Productivity Analysis Assignment Total = 55 points: Productivity Calculation Tab (30 points) + What-If Analysis Tab (25 points) Assignment Learning Objectives Data: 1. Calculate single factor and total factor productivity measures Metric Hint: Use Excel formulas to calculate your answer. For Example: To calculate the Parent's total labor dollars, you multiply labor hours * labor cost In Excel, you enter: =C7*C8 The "=" sign tells Excel that you are entering a formula and then you multiply the two cells that contain your data 2. Interpret productivity measures and draw appropriate conclusions 3. Use formulas in Excel 4. Use Excel to perform "What-if" Analysis Use data in columns C & D for the Calculation Exercises 1a - 6c Subsidiary (Less Parent (Domestic) Developed Country) 1,750,000 650,000 1,400,000 480,000 40,000 20,000 $14.57 $5.52 $400,000 $200,000 120,000 30,000 $1,359,600 $225,000 $2,000,000 $725,000 $19.99 $19.99 Sales (units) Units Produced Labor (hours) Labor Cost ($) per hour Materials Cost ($) Equipment (Hours) Equipment Cost ($) Fixed Costs ($) Selling Price per Unit Total points this worksheet = 30 points Calculation Exercises: Labor Productivity (units/hour) 1a. Compute the single-factor productivity for labor for the Parent operations in units per hour (enter your formula in cell B20). units/labor hour Cell B20 must be a formula 1b. Compute the single-factor productivity for labor for the Subsidiary operations in units per hour (enter your formula in cell B23). units/labor hour Cell B23 must be a formula 1c. Interpret your findings by indicating which of the two entities is more productive in terms of single-factor labor productivity in units per hour Labor Productivity (units/$) 2a. Compute the single-factor productivity for labor for the Parent operations in units per dollar (enter your formula in cell B31). units/labor $ Cell B31 must be a formula Labor Productivity (units/hour) 1a. Compute the single-factor productivity for labor for the Parent operations in units per hour (enter your formula in cell B20). units/labor hour Cell B20 must be a formula 1b. Compute the single-factor productivity for labor for the Subsidiary operations in units per hour (enter your formula in cell B23). units/labor hour Cell B23 must be a formula 1c. Interpret your findings by indicating which of the two entities is more productive in terms of single-factor labor productivity in units per hour Labor Productivity (units/$) 2a. Compute the single-factor productivity for labor for the Parent operations in units per dollar (enter your formula in cell B31). units/labor $ Cell B31 must be a formula 2b. Compute the single-factor productivity for labor for the Subsidiary operations in units per dollar (enter your formula in cell B34). units/labor $ Cell B34 must be a formula 2c. Interpret your findings by indicating which of the two entities is more productive in terms of single-factor labor productivity in units per dollar. Materials Productivity (units/$) 3a. Compute the single-factor productivity for materials for the Parent operations in units per dollar (enter your formula in cell B42). units/$ Cell B42 must be a formula 3b. Compute the single-factor productivity for materials for the Subsidiary operations in units per dollar (enter your formula in cell B45). units/$ Cell B45 must be a formula 3c. Interpret your findings by indicating which of the two entities is more productive in terms of single-factor materials productivity in units per dollar. Equipment Productivity (units/hour) 4a. Compute the single-factor productivity for equipment for the Parent operations in units per hour (enter your formula in cell B53). units/hour Cell B53 must be a formula 4b. Compute the single-factor productivity for equipment for the Subsidiary operations in units per hour (enter your formula in cell B56). units/hour Cell B56 must be a formula 4c. Interpret your findings by indicating which of the two entities is more productive in terms of single-factor equipment productivity in units per hour. Equipment Productivity (units/$) 5a. Compute the single-factor productivity for equipment for the Parent operations in units per dollar (enter your formula in cell B64). units/$ Cell B64 must be a formula 5b. Compute the single-factor productivity for equipment for the Subsidiary operations in units per dollar (enter your formula in cell B67). units/$ Cell B67 must be a formula 5c. Interpret your findings by indicating which of the two entities is more productive in terms of single-factor equipment productivity in units per dollar. Total-Factor Productivity: 6a. Compute the total-factor productivity (all factors) for the Parent operations in units per dollar (enter your formula in cell B75). units/$ Cell B75 must be a formula 6b. Compute the total-factor productivity (all factors) for the Subsidiary operations in units per dollar (enter your formula in cell B78). units/$ Cell B78 must be a formula 6c. Interpret your findings by indicating which of the two entities is more productive in terms of total-factor productivity in units per dollar. Productivity Analysis Assignment Total = 55 points: Productivity Calculation Tab (30 points) + What-If Analysis Tab (25 points) Current Values Data: Metric Sales (units) Units Produced Labor (hours) Labor Cost ($) per hour Materials Cost ($) Equipment (Hours) Equipment Cost ($) Fixed Costs ($) Selling Price per Unit Parent (Domestic) 1,400,000 1,400,000 40,000 $14.57 $400,000 120,000 $1,359,600 $2,000,000 $9.99 Subsidiary (LDC) 450,000 480,000 20,000 $5.52 $200,000 30,000 $225,000 $725,000 $7.99 Use column F for question 8 Parent (Domestic) 1,400,000 1,400,000 40,000 $14.57 $400,000 120,000 $1,359,600 $2,000,000 $9.99 What-If Productivity Values Based on New Data Entered in Column F Measure Parent Organization Labor Productivity (units/labor hour) 35.00 Labor Productivity (units/labor $) 2.40 Materials Productivity (units/$) 3.50 Equipment Productivity (units/hour) 11.67 Equipment Productivity (units/$) 1.03 Total-Factor Productivity (units/$) 0.32 Labor 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.) 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 Fabove. 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 Measure Choose the correct answer for each productivity calculation Labor Productivity in terms of units/labor hour Labor Productivity in terms of units/labor $ Materials Productivity in terms of units/Materials Cost ($) Equipment Productivity in terms of units/hour Equipment Productivity in terms of units/$ Total Factor Productivity in terms of units/labor $ Materials Productivity in terms of units/Materials Cost ($) Equipment Productivity 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