1 answer

Please answer questions using Microsoft excel, and please show formulas. Thank you. School of Business BUS...

Question:

Please answer questions using Microsoft excel, and please show formulas. Thank you.
School of Business BUS 476-Operations Management Dr. Enrique G. Zapatero, Professor Homework #8-Aggregate Planning Instructions: Use MS Excel to produce the computations to support your answers to ProblemsI, and I. Problem I: A production manager needs to develop a production schedule to meet the following demand Period Demand 600 600 800 1000 1200 600 2 Productions costs are as follows: (a) regular time: $3 per unit. (b) over-time: $5 per unit. (c) subcontract: $7 per unit. (d) Inventory carrying cost $2 per unit per period (e) Back-order costs = $5 per unit per period. You have to meet all the demand. You start with no inventory at the beginning of the first period, and end with no inventory at the end of the sixth period Schedule A: Suppose the manager has adopted a policy of level production (i.e., constant production rate for all periods). Assume no over-time or subcontracting is allowed Schedule B: Suppose some workers have retired, and the regular production rate can be only 700 units per period. Further, no subcontracting is allowed However, it is possible to schedule production on an over-time basis. (Hint: You normally schedule over-time production during peak demand.) Schedule C: Suppose the regular rate of production per period is 700. However you are allowed to buy 200 units from a subcontractor during each of the periods 3, 4, and 5. Develop a production schedule to meet the demand Schedule D: Suppose the regular rate of production is 700 units per period Further, you are allowed to produce 100 units during each of the periods 3,4 and 5 on an overtime basis, but the subcontractor has gone out of business. You can hire two temporary workers during the periods 3, 4, and 5. Each worker can produce 50 units per period at the regular cost. But it costs $200 to train a worker and check the quality of the product during each of the three periods
media%2F2bb%2F2bb96eb9-9dee-4873-94df-4a
media%2Feaa%2Feaae00f6-5230-467c-ad25-c8
media%2F54d%2F54d8b6d2-9a65-4ee0-af61-8e
media%2Fa0d%2Fa0daca9e-73cb-4adf-abd7-35
School of Business BUS 476-Operations Management Dr. Enrique G. Zapatero, Professor Homework #8-Aggregate Planning Instructions: Use MS Excel to produce the computations to support your answers to ProblemsI, and I. Problem I: A production manager needs to develop a production schedule to meet the following demand Period Demand 600 600 800 1000 1200 600 2 Productions costs are as follows: (a) regular time: $3 per unit. (b) over-time: $5 per unit. (c) subcontract: $7 per unit. (d) Inventory carrying cost $2 per unit per period (e) Back-order costs = $5 per unit per period. You have to meet all the demand. You start with no inventory at the beginning of the first period, and end with no inventory at the end of the sixth period Schedule A: Suppose the manager has adopted a policy of level production (i.e., constant production rate for all periods). Assume no over-time or subcontracting is allowed Schedule B: Suppose some workers have retired, and the regular production rate can be only 700 units per period. Further, no subcontracting is allowed However, it is possible to schedule production on an over-time basis. (Hint: You normally schedule over-time production during peak demand.) Schedule C: Suppose the regular rate of production per period is 700. However you are allowed to buy 200 units from a subcontractor during each of the periods 3, 4, and 5. Develop a production schedule to meet the demand Schedule D: Suppose the regular rate of production is 700 units per period Further, you are allowed to produce 100 units during each of the periods 3,4 and 5 on an overtime basis, but the subcontractor has gone out of business. You can hire two temporary workers during the periods 3, 4, and 5. Each worker can produce 50 units per period at the regular cost. But it costs $200 to train a worker and check the quality of the product during each of the three periods

Answers

Schedule A can be solved as shown below (Excel Formula is shown in each cell. Kindly note this to understand):

This is for Level planning; means constant production over period to meet demand.

Costs Per Unit Units Cost Regular Time Labor Co Overtime SUM(B28.G28 G4 H4 6 Beginning Inventor 8 Labor Standard (units/workeIts solution will be as shown below:

Costs Reqular Time Labor Cost Overtime Subcontractin Inventory Holding Cost Backorders Hirin Per Unit Units Cost $3.001 48001

Hence total cost of Production for this Schedule A is $ 17,800.00

Schedule B:

Since in this schedule, the only change is that, the # of workers have reduced and hence the production capacity. Since overtime is allowed but no subcontracting, hence its solution will be as shown below:

The Formula will remain same as shown in Schedule A. The only changes I made in Shell B7; Begining workforce is 700:

Costs Reqular Time Labor Cost Overtime Subcontractin Inventory Holding Cost Backorders Per Unit Units Cost $3.004200$12,600 6Hence the overall cost for this schedule is $ 19,000.00

Schedule C:

In this case, the Formula will be changed in Row No 28 & 29. Row # 28 will be zero.

But Subcontracting is allowed hence the value in Row # 29 will be as shown below:

Costs Per Unit Units Cost Regular Time Labor Co Overtime Subcontra Inventory Holding Cost -SUM(B27 G27)G3 H3 SUM(B28 : G28) GAnd its solution is shown below:

Costs Regular Time Labor Cost Overtime Subcontractin Inventory Holding Cost Backorders Per Unit Units Cost $3.004200$12,600 $Hence the cost in this case is $ 20,200.00

.

Similar Solved Questions

1 answer
If the reaction Xproducts is second-order, then which of the following graphs is linear with slope...
If the reaction Xproducts is second-order, then which of the following graphs is linear with slope +k? versus Rate versus t 1 / [X] versus t [X]2 versus t In [X] versus t...
1 answer
Francine currently has $25,000 in her 401k account at work, and plans to contribute $300 at...
Francine currently has $25,000 in her 401k account at work, and plans to contribute $300 at the end of each month for the next 10 years. How much will she have in the account in 10 years, if the account averages a 4% annual return? Assume monthly compounding. Asnwer = $ (Round to the nearest cent/pe...
1 answer
11 Required information The following information applies to the questions displayed below Legacy issues $610,000 of...
11 Required information The following information applies to the questions displayed below Legacy issues $610,000 of 6.5 %, four-year bonds dated January 1, 2017, that pay interest semiannually on June 30 and December 31. They are issued at $579.203 and their market rate is 8 % at the issue date. Pa...
1 answer
As pdlt ol an exercise ruutirie, dn athlete does a set 이 dim ups shown IS...
As pdlt ol an exercise ruutirie, dn athlete does a set 이 dim ups shown IS d ur dph ut the d thlete s vertical speed ds she perilms a chin-up. If her mass is 75.3 kg calculate he torce exerted by the chin-up bar her hands for the given times. (nter the magnitudes in N.) 30 0 0.5 2.0 time (s) (...
1 answer
Imagine that the trade war between the US and China escalates, and spreads to other nations, including Australia. Discuss the potential costs and benefits for the Australian economy (200 word limit).
Imagine that the trade war between the US and China escalates, and spreads to other nations, including Australia. Discuss the potential costs and benefits for the Australian economy (200 word limit)....
1 answer
Chromatids, and one crossover involves_ chromatids. Crossing-over takes place in bivalents (tetrads) consisting of_ A. 2,2...
chromatids, and one crossover involves_ chromatids. Crossing-over takes place in bivalents (tetrads) consisting of_ A. 2,2 B. 4,2 C.4,4 D.not possible to know E. 2,4...
1 answer
Sorting: (40 points) a. We studied several sorting algorithms. Every sorting algorithm has their own special...
Sorting: (40 points) a. We studied several sorting algorithms. Every sorting algorithm has their own special reason where it can only use. Can you explain carefully in which situation the following algorithms would be best sorting algorithm to use in an application. (10 points) i. Insertion sort ii....
1 answer
1. Mark each of the following true or false with a brief explanation for your answer...
1. Mark each of the following true or false with a brief explanation for your answer for each statement (iv) On Q, define by a b . Then is a binary operation on Q....
1 answer
Income Tax Employment Income - Computation Edriana is employed as a managing director at a multinational...
Income Tax Employment Income - Computation Edriana is employed as a managing director at a multinational company in Malaysia and her salary is RM180,000 per annum. She is provided with unfurnished living accommodation for which the company pays rent amounting to RM60,000 per annum. Edriana is not...
1 answer
The sample size is 150 values. 4. (2 points) Consider the restrictions for using the t...
The sample size is 150 values. 4. (2 points) Consider the restrictions for using the t test procedures (robustness guidelines), for this sample size. a) What do we need our data to look like in order for the t procedures to be appropriate? b) Is it appropriate to use the t procedures for this data? ...
1 answer
The advertising account of Skype Stores included an amount of R1 200 that was paid for...
The advertising account of Skype Stores included an amount of R1 200 that was paid for advertising for the period 01 November 2015 to 31 October 2016. Which of the following alternatives represent the correct double entry to record the required adjustment on 29 February 2016, the end of the financia...
1 answer
2019 Tax Bill Please compute the total tax due (April 30th, 2020) for the following single...
2019 Tax Bill Please compute the total tax due (April 30th, 2020) for the following single (non-senior, nonmarried) resident of Ontario. During the 2019 tax year, they earned $160,000 in (gross) T4 employment income. From this sum, $40,000 was deducted by their employer (for provincial and federal t...
1 answer
Steps please. Assuming ΔΗο and AS"are independent of temperature, calculate the temperature where K- 1.00 for...
Steps please. Assuming ΔΗο and AS"are independent of temperature, calculate the temperature where K- 1.00 for the following reaction: 5. Fe O3 (s) 3 H2(g) 2 Fe (s) + 3 H2O (g)...
1 answer
Why is it important to consider the components of the social ecology framework in community and public health
Why is it important to consider the components of the social ecology framework in community and public health?...
1 answer
The amounts of time per workout an athlete uses a stairclimber are normally distributed, with a...
The amounts of time per workout an athlete uses a stairclimber are normally distributed, with a mean of 21 minutes and a standard deviation of 7 minutes. Find the probability that a randomly selected athlete uses a stairclimber for (a) less than 16 minutes, (b) between 21 and 28 minutes, and (c) mor...