Calculate Investing in Productivity Improvements
meghancb89
Activity3ROIAnalysis2019.xlsxROI Scenario
| Northcentral University | |||||||
| MBA-6010 Strategic Planning | |||||||
| Assignment 3.2 Calculate Investing in Productivity Improvements (HR) | |||||||
| INPUTS | |||||||
| Total labor expenditure (direct labor) from Annual Report Income Statement | $ 14,612,000 | ||||||
| # of employees - Round 2 | 692 | ||||||
| Turnover Rate | 6.6% | ||||||
| RECAP | |||||||
| SAVINGS (Rows 21-26) | $ 7,765,123 | ||||||
| COSTS | |||||||
| RECRUITING COST (Rows 32-37) | $ 965,000 | ||||||
| TRAINING COSTS (Rows 42-47) | $ 308,800 | ||||||
| WORKFORCE COMPLEMENT TO COVER TRAINING COSTS (Rows 52-57) | $ 3,356,089 | ||||||
| TOTAL COSTS | $ 4,629,889 | ||||||
| NET SAVINGS | $ 3,135,235 | ||||||
| SAVINGS | |||||||
| A | B | C = B / A | D = B - C | ||||
| Productivity Payoffs | Total labor expenditure (direct labor) from Annual Report Income Statement | Total Labor Complement | Savings | ||||
| Round 2 | 1.02 | $ 14,612,000 | $ 14,325,490 | $ 286,510 | |||
| Round 3 | 1.05 | $ 14,612,000 | $ 13,916,190 | $ 695,810 | |||
| Round 4 | 1.08 | $ 14,612,000 | $ 13,529,630 | $ 1,082,370 | |||
| Round 5 | 1.12 | $ 14,612,000 | $ 13,046,429 | $ 1,565,571 | |||
| Round 6 | 1.15 | $ 14,612,000 | $ 12,706,087 | $ 1,905,913 | |||
| Round 7 | 1.18 | $ 14,612,000 | $ 12,383,051 | $ 2,228,949 | |||
| $ 87,672,000 | $ 79,906,877 | $ 7,765,123 | |||||
| COSTS | |||||||
| RECRUITING COST | |||||||
| E = # employees/A | F = Difference in # employees from previous round | G = F/E | H | I = (H-G)*E | J | K = I x J | |
| # of employees given increase in productivity | Difference in # employees | Turnover | # Employees need to hire | Recruiting cost per employee | Total Recruiting cost | ||
| Round 2 | 692 | - 0 | $ - 0 | ||||
| Round 3 | 678 | 14 | 2% | 6.6% | 45 | $ 5,000 | $ 225,000 |
| Round 4 | 646 | 32 | 5% | 6.6% | 43 | $ 5,000 | $ 215,000 |
| Round 5 | 598 | 48 | 8% | 6.6% | 39 | $ 5,000 | $ 195,000 |
| Round 6 | 534 | 64 | 12% | 6.6% | 35 | $ 5,000 | $ 175,000 |
| Round 7 | 464 | 70 | 15% | 6.6% | 31 | $ 5,000 | $ 155,000 |
| $ 965,000 | |||||||
| TRAINING COSTS | |||||||
| I | L | M | N = I x L x M | ||||
| # Employees need to hire | # Hours | Cost per hour | Total Training Costs | ||||
| Round 2 | - 0 | 80 | $ 20.00 | $ - 0 | |||
| Round 3 | 45 | 80 | $ 20.00 | $ 72,000 | |||
| Round 4 | 43 | 80 | $ 20.00 | $ 68,800 | |||
| Round 5 | 39 | 80 | $ 20.00 | $ 62,400 | |||
| Round 6 | 35 | 80 | $ 20.00 | $ 56,000 | |||
| Round 7 | 31 | 80 | $ 20.00 | $ 49,600 | |||
| $ 308,800 | |||||||
| WORKFORCE COMPLEMENT TO COVER TRAINING COSTS | |||||||
| C | O | P = C x O | |||||
| Total Labor Complement | Increase to cover employees training | ||||||
| Round 2 | $ 14,325,490 | 0.042 | $ 601,671 | ||||
| Round 3 | $ 13,916,190 | 0.042 | $ 584,480 | ||||
| Round 4 | $ 13,529,630 | 0.042 | $ 568,244 | ||||
| Round 5 | $ 13,046,429 | 0.042 | $ 547,950 | ||||
| Round 6 | $ 12,706,087 | 0.042 | $ 533,656 | ||||
| Round 7 | $ 12,383,051 | 0.042 | $ 520,088 | ||||
| $ 3,356,089 | |||||||
Insert these three numbers from CAPSIM * Total Labor for Round 2 Income Statement in the Annual Report (not the Courier) * # of Employees from Complement # on HR Summary, p.12 of the Courier * Turnover Rate from Round 2, p.12 of the Courier
