A B C D E F G H I J K L MIDDLE Productivity Analysis Assignment TENNESSEE Total = 55 points: Productivity Calculation Tab (30 points) + What-If Analysis Tab (25 points) STATE UNIVERSITY Current Values Use column F for JONES COLLEGE OF BUSINESS e Metric ~ [Parent(Domestic) [subsidiary(LDG) | Sales (units) 1,850,000 750,000 1,850,000 Units Produced 1,425,000 479,586 1,425,000 Labor (hours) 45,000 25,000 45,000 Labor Cost ($) per hour $14.25 $5.75 $14.25 Materials Cost ($) $400,259 $205,568 $400,259 Change any of these values necessary for problem #8 Equipment (Hours) 120,598 30,578 120,598 Equipment Cost ($) $1,359,600 $225,000 $1,359,600 Fixed Costs ($) $2,000,000 $725,000 $2,000,000 Selling Price per Unit $19.99 $19.99 $19.99 What-If Productivity Values Based on New Data Entered in Column F Measure Parent Organization Labor Productivity (units/labor hour) 31.67 Labor Productivity (units/labor $) 2.22 Materials Productivity (units/$) 3.56 Equipment Producti (units/hour) 11.82 Equipment Productivity (units/$) 1.05 Total-Factor Productivity (units/$) 0.32 Labor H#NAME? 7. Provide one possible explanation how the Parent can be more productive when considering labor productivity in units per hour, but the subsidiary is more productive in units per dollar.) One possible reason why parents may be more productive in units per hour is that they produce more output with the given number of workers than the subsidiaries, while the overall labor cost for the subsidiary is less than that of the parent. Parent Equipment Purchase (Part #1) 8. Assume purchase of new equipment at an incremental, one-time fixed cost of $1,500,000 resulting in a reduction in labor hours to 30,000, a reduction in Equipment Hours to 100,000, and a reduction of Equipment Costs to $1,248,000 with all other costs remaining the same. Change the appropriate values in column F above. Answer the questions below by comparing the new productivity value in cells D18 through D23 to the values you calculated on the "Productivity Calculations" worksheet. For example, if the value in D18 on this worksheet (Labor Productivity in units/labor hour) is less than the value for Labor Productivity in units/labor hour for the Parent operations on the "Productivity Calculations" worksheet, then choose "decreased\" Productivity Calculations What-If Analysis + Ready %(Accessibity: Investigate =l E M - + 13% Parent Equipment Purchase (Part #1) 8. Assume purchase of new equipment at an incremental, one-time fixed cost of $1,500,000 resulting in a reduction in labor hours to 30,000, a reduction in Equipment Hours to 100,000, and a reduction of Equipment Costs to $1,248,000 with all other costs remaining the same. Change the appropriate values in column F above. Answer the questions below by comparing the new productivity value in cells D18 through D23 to the values you calculated on the "Producti Calculations" worksheet. For example, if the value in D18 on this worksheet (Labor Productivity in units/labor hour) is less than the value for Labor Productivity in units/labor hour for the Parent operations on the "Productivity Calculations" worksheet, then choose "decreased" Choose the correct answer for each productivity calcul: Labor Productivity in terms of units/labor hour| " Labor Productivity in terms of Productivity Measure units/labor $ Materials Productivity in terms of increased units/Materials Cost ($) Equipment Producti in terms of units/hour| Equipment Productivity in terms of units/$ Total Factor Productivity in terms of units/$ Parent Equipment Purchase (Part #2) 9. Calculate Total Revenue prior to any equipment changes using data in cells C6 through C14 above. The formula for Profit is Total Revenue - Total Costs The formula for Total Revenue is Total Units Sold * Selling Price per Unit Pre Equipment Purchase Total Profit Post Equipment Purchase Total Profit 10. Summarize your observations concerning how the potential new equipment purchase will impact each of the productivity values and total revenue. Provide a recommendation whether to purchase the equipment and also provide supporting evidence for your recommendation