Learn how to leverage Excel’s Scenario Manager to effortlessly evaluate the financial impact of hiring decisions. See how different employee scenarios can significantly influence company profitability through clear, summarized reports.
Key Insights
- The Scenario Manager in Excel, located within the "What-If Analysis" under the Data tab, helps users analyze and summarize the financial impacts of specific variables, such as new hires, by creating multiple scenarios simultaneously.
- In the provided example, each new employee generates $20,000 in sales over three months, with associated costs including $15,000 in salary, $2,000 in benefits, $600 for materials, $300 for administrative fees, and additional hiring and training expenses totaling $4,800.
- The article demonstrates the efficiency of defining cell ranges with named references (such as "new hires") to clearly display meaningful labels in Scenario Manager reports, streamlining the comparison of scenarios ranging from one to five new hires and showing the incremental benefit to the company—from a loss of $2,700 for one employee to a profit of $5,700 for five employees.
Note: These materials offer prospective students a preview of how our classes are structured. Students enrolled in this course will receive access to the full set of materials, including video lectures, project-based assignments, and instructor feedback.
Scenario Manager Part 2. We're going to take a look at another example of using the Scenario Manager. We're going to use this Scenario Manager to build out and summarize different scenarios simultaneously. The Scenario Manager can be found on the data tab and if you move over to the right you want to look into the forecast group and then you'll click the drop down for what if analysis and that's where you'll see the Scenario Manager.
So here's our current scenario, a company is trying to determine how many new hires it should post for. Now each employee is expected to earn $20,000 in sales during the first three months on average. The materials needed for that employee to do that work will cost the company $600 in regards to a workstation and necessary supplies.
When it comes to salaries, it's going to cost the company $15,000 in regards to three months of salary. The benefits, the cost to the company is $2,000. Administrative costs, that will cost the company $300 in regards to filing paperwork and such.
The hiring process, also an additional cost, $800 for all job advertising costs and $4,000 for the time that the HR team devotes to training the new hires. So I want to be able to see the company benefit overall. The current company benefit without hiring any employees is right now negative $4,800.
So how much is that loss reduced by if I hire one employee? Well now it's down to negative $2,700. How about two employees? Now it's down to negative $600. We're getting to the point where we might actually come out ahead.
Three employees? Well now we're making a $1,500 company benefit with the third employee. And if we hire even more, the benefit gets even higher. For four employees it's $3,600 and for five employees it's going to be $5,700.
These are all different types of scenarios, but unfortunately in order for me to see that I have to click the drop down and select, click the drop down and select, click the drop. I want an easy way to switch between all the scenarios. So a scenario manager is perfect for this.
So I'm going to create a scenario manager by going to the data tab. I'll head over to what if analysis and I'll click scenario manager. Now I need to add in all the scenarios starting from one employee all the way to five.
So I'll click add and I'll call this scenario one. I'll just type the name one. The cell that needs to change in order for the benefit to occur to the company is C22.
It's already selected. What is the value for one? Well it's literally one. Now notice that the name of the cell is referenced here as C22.
What if I wanted that cell to refer to the name that describes the value like new hires and I don't want to see C22? Well I can create a named range. In fact I'll create a named range for all the values here. It's going to be very easy to do if you go to the formulas tab and from the defined names group choose create from selection.
So I'm going to select this cell along with its name. I'm going to hold on to control and select these values. Hold on to control and select the last set of values.
I am now going to click create from selection and I want to name the cells over on the left well on the right the values on the right. I want to name the values on the right based on the names in the left column. So I'll click okay.
Let me make sure I select this correctly. I think I may have double selected a range. So I'm going to press control and select this range and then press and then still holding on to control I'm going to select this range.
Okay that looks a little bit better. I'll go to create from selection. I'm going to name the values in the right column using the names in the left row.
I'll click okay and now they've all been named. When I create my summary report I'll actually see those names in my summary report. All right so now I'll go back to my scenario manager data what if analysis scenario manager.
I'm going to create my first scenario which is one. The cell that's being referenced is C22. Now when I click okay notice the difference.
Before I saw a dollar sign C dollar sign 22 but because I've given the name to this cell when I click okay it's now going to be referred to as new hires. And so that's an easy way to distinguish what that cell represents rather than just seeing the cell reference. Now what is the value in that cell if the scenario was one I'm going to type one.
I'm not using the actual value that's in the cell. I'm going to click add and add another scenario. I'm going to call this two.
I'll click okay the value in that cell is going to be two. I'll click add then I'll type three. I'll click okay change that value to three.
Then I'll click add. I'm going to type four. I'll click okay the value in that cell is not five it's going to be four.
Then I'll click add and finally this is going to refer to five. Now this is one of those situations where when I click okay the value is actually the value that I want. So it's five and I will select that and just click okay.
Since it's the last value I should now go back and see all five values. Now I want to be able to run each scenario. So I've selected one.
I'll click show. This is where the company benefit is at negative 2700. I'll try two.
I'll click show. Now it's at negative 600. Three I think is where I make a profit $1,500 company benefit.
Four $3,600 benefit and five that's going to be the most 5,700. Now that I have all these scenarios I'd like to see a summary. So I'm going to click summary and I'm going to select the values that I want to see results for.
So I'm going to choose new hires. So equal new hires. I'm going to hold on to the control key and select all the other numerical values that represent results.
I'll click okay and now the scenario manager is going to work creating a report that uses all the values I just selected and will reference the new hire for those values. It takes a little while to run so just be patient. This is one of the situations where the report doesn't get created automatically and it may take some time.
If you see a circle spinning or an arrow glass or some other visual indication, there we go. And now we see all the values that represent the result cells and the changing cells. I even included the changing cells as part of my result cells.
Now column D is not necessary. It just repeats the value for five. It's just whatever was currently selected at the time.
So I can choose hide and this is my actual report. So this is a second example of using scenario manager to create a report that shows me different scenarios based on, in this case, the number of hires a new company makes. Thank you for watching.