What-if Scenario Analysis Hiring Plan Assignment Objective : Use Excel’s Scenari
ID: 2401612 • Letter: W
Question
What-if Scenario Analysis
Hiring Plan Assignment
Objective: Use Excel’s Scenario Tool to perform a what-if analysis for a hiring plan. The business problem is this: a manager is faced with a decision that requires the evaluation of several alternatives, or scenarios. In this example, the manager has been allocated a budget of $125,000 for the current fiscal year (same as calendar year) for new hires. This manager has received approval to hire five (5) new personnel positions: Administrative Assistant (1), Assistant Manager (1), Sales Representative (2), and Marketing Representative (1). The starting salary for these positions is indicated below:
Personnel Position
Initial Annual Salary
Administrative Assistant
$28,000
Assistant Manager
$38,000
Sales Representative
$35,000
Sales Representative
$35,000
Marketing Representative
$37,000
Notice that if you total these salaries, the amount is greater than $125,000. So hiring must be staggered throughout the year. This is the purpose of this assignment: to examine various scenarios that describe when the positions can be filled in order to meet the budget constraint of $125,000 for this year.
.
2. Solve this problem by addressing these constraints and using Excel’s Scenario Tool.
Additional Constraints:
An Administrative Assistant is needed immediately. The Manager is answering phones and typing memos while her management of the division is suffering from lack of total attention.
A Sales Representative is required in the first quarter
A Marketing Representative is required in the first or second quarter.
Every year for the past seven years, a hiring freeze has occurred on October 1st. Therefore if a position is not filled by October 1st, it risks elimination from the budget and is very difficult to have reinstated so everyone needs to be hired and on board before October 1st.
Devise at least four Excel Scenarios that produce a hiring plan that meets these constraints and consumes at least $120,000 of your allocated budget. Remember, you cannot go over your $125,000 budget.
What-if Scenario Analysis
Hiring Plan Assignment
HIRING PLAN 3 Personnel Position Annual Salary Monthly Salary Hiring Month Budget Impact 4 Administrative Assistant 5 Assistant Manager 6 Sales Representative 7Sales Representative 8 Marketing Representative 628,000 $38,000 $35,000 35,000 537 $2333.33 $3,166.67 62,916.67 $2,916.67 $3,083.33 28,000.00 38,000.00 $35,000.00 35,000.00 37,000.00 10 TOTAL $173,000 $173,000Explanation / Answer
Scenario Summary Current Values: Hiring Plan 1 Hiring Plan 2 Hiring Plan 3 Hiring Plan 4 Changing Cells: $D$3 Hiring month Hiring month Hiring month Hiring month Hiring month $D$4 1 1 1 1 1 $D$5 1 9 8 7 8 $D$6 1 3 3 3 2 $D$7 1 4 5 5 5 $D$8 1 5 5 5 5 Result Cells: $E$10 $173,000 $120,750 $121,000 $124,167 $123,917 Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created. Changing cells for each scenario are highlighted in gray.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.