please help with this HW question provide the formula used put in excel format The Austin, Texas plant of Computer Products produces disk units for personal and small business computers Gerald Knox, the plant's production planning director, is looking over next year's sales forecasts for these products and will be developing an aggregate capacity plan for the plant The quarterly sales forecasts for the disk units are as follows 1 st Quarter 2 nd Quarter 3 rd Quarter 4 th Quarter 2610 2700 2700 2430 Ample machine capacity exists to produce the forecast Each disk unit takes an average of 20 labor hours In addition, you have collected the following information Inventory carrying cost is $100 per disk unit per quarter The cost is applied to all units in inventory at the end of a quarter The plant works the same number of days in each quarter, 12 five day weeks, 6 hours per day In a backlog situation, the customer will wait for his order to be filled but will expect a price reduction each quarter he waits The backlog costs are $300 per disk for the first quarter the customer waits, $700 for the second quarter the customer waits, and $900 for the third quarter the customer waits In filling orders, backlogged items will always be filled before current quarter items, The cost of hiring a worker is $800 while the cost of laying off a worker is $950 Workers hired during a quarter are able to produce at full utilization for the quarter The straight time labor rate is $20 per hour for the first quarter and increases to $23 per hour starting in the third quarter Overtime work is paid at time and a half (150 ) of the straight time work Outsourcing (contract work) is paid at the rate of $475 per disk unit for the labor and you provide the material Demand during the fourth quarter of the prior year was 2,430 and this was met by a workforce working full utilization with no overtime The demand for the first quarter of the next year (year following the year you are analyzing) is projected to be at the 2,610 unit level Compare the following two sales and operations plans i) Computer Products is planning on using a work force capable of producing 2520 units in any quarter If all of the production is not required in one of these quarters, all of the workers will continue to be employed and there will be underutilization The workers will be paid but only the units needed in the quarter will be produced If the demand is not filled using the full workforce working straight time (12 five day weeks, 6 hours per day), it will meet any demand not filled with this workforce working straight time by outsourcing the unmet demand Ignoring the material cost, what is the total cost to produce the disks for the year ii) A form of level production where the total number of disk units needed during the year are produced during the year Production is to be at a uniform (constant) rate throughout the entire year All workers will be fully utilized each quarter, in other words, there is no underutilization In any quarter, when there are backlog items from the prior quarter, these will be given priority in being filled over current quarter items What is the total cost of this option, excluding the material cost

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 27, 2024

please help with this HW question. provide the formula used. put in excel format The Austin, Texas plant of Computer Products produces disk units for

please help with this HW question. provide the formula used. put in excel format

The Austin, Texas plant of Computer Products produces disk units for personal and small business computers. Gerald Knox, the plant's production planning director, is looking over next year's sales forecasts for these products and will be developing an aggregate capacity plan for the plant. The quarterly sales forecasts for the disk units are as follows:

1^st Quarter	2^nd Quarter	3^rd Quarter	4^th Quarter
2610	2700	2700	2430

Ample machine capacity exists to produce the forecast. Each disk unit takes an average of 20 labor-hours. In addition, you have collected the following information:

Inventory carrying cost is $100 per disk unit per quarter. The cost is applied to all units in inventory at the end of a quarter.
The plant works the same number of days in each quarter, 12 five-day weeks, 6 hours per day.
In a backlog situation, the customer will wait for his order to be filled but will expect a price reduction each quarter he waits.The backlog costs are $300 per disk for the first quarter the customer waits, $700 for the second quarter the customer waits, and $900 for the third quarter the customer waits. In filling orders, backlogged items will always be filled before current quarter items,
The cost of hiring a worker is $800 while the cost of laying off a worker is $950. Workers hired during a quarter are able to produce at full utilization for the quarter.
The straight time labor rate is $20 per hour for the first quarter and increases to $23 per hour starting in the third quarter.
Overtime work is paid at time and a half (150%) of the straight time work.
Outsourcing (contract work) is paid at the rate of $475 per disk unit for the labor and you provide the material
Demand during the fourth quarter of the prior year was 2,430 and this was met by a workforce working full utilization with no overtime.
The demand for the first quarter of the next year (year following the year you are analyzing) is projected to be at the 2,610 unit level.

Compare the following two sales and operations plans.

i) Computer Products is planning on using a work force capable of producing 2520 units in any quarter. If all of the production is not required in one of these quarters, all of the workers will continue to be employed and there will be underutilization. The workers will be paid but only the units needed in the quarter will be produced. If the demand is not filled using the full workforce working straight time (12 five-day weeks, 6 hours per day), it will meet any demand not filled with this workforce working straight time by outsourcing the unmet demand. Ignoring the material cost, what is the total cost to produce the disks for the year?

ii) A form of level production where the total number of disk units needed during the year are produced during the year. Production is to be at a uniform (constant) rate throughout the entire year. All workers will be fully utilized each quarter, in other words, there is no underutilization. In any quarter, when there are backlog items from the prior quarter, these will be given priority in being filled over current quarter items. What is the total cost of this option, excluding the material cost?