Productivity Analysis Assignment Total = 55 points: Productivity Calculation Tab (30 points) + What-lf 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 la - 60 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). Cell B23 must be a formula - units/labor hour 1c. Interpret your findings by indicating which of the two entities more productive in terms of single-factor labor productivity in units per hour Labor Productivity (units/S) 2a. Compute the single-factor productivity for labor for the Parent operations in units per dollar (enter your formula in cell B31). Cell B31 must be a formula - units/labor $ 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 more productive in terms of single-factor labor productivity in units per dollar. Materials Productivity (units/S) 3a. Compute the single-factor productivity for materials for the Parent operations in units per dollar (enter your formula in cell 042). Cell B42 must be a formula - units/s 3b. Compute the single-factor productivity for materials for the Subsidiary operations in units per dollar (enter your formula in cell B45). units/s 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). Cell B53 must be a formula - units/hour 46. Compute the single-factor productivity for equipment for the Subsidiary operations in units per hour (enter your formula in cell 056). 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). Cell B64 must be a formula - units/ 5b. Compute the single-factor productivity for equipment for the Subsidiary operations in units per dollar (enter your formula in cell B67). Cell B67 must be a formula - units/$ 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). Cell B75 must be a formula - units/ 6b. Compute the total-factor productivity (all factors) for the Subsidiary operations in units per dollar (enter your formula in cell B78). Cell B78 must be a formula - units/S 6c. Interpret your findings by indicating which of the two entities is more productive in terms of total-factor productivity in units per dollar