- [Instructor] This is Excel Module 8: SAM Project 1b.
We will be working for Ferguson Fitness,
and there's one thing we need to do
before beginning the project.
We need to have a Solver Add-In,
and so we'll go ahead and activate that in Excel.
It's something that's not automatically within Excel,
so let's go ahead and get that ready to go.
Click on File and then Options.
And in this first column here, select Add-ins.
And in the second column at the bottom,
next to Excel Add-ins click on Go.
And you can see some different tools available to add in.
Just activate the check box by Solver Add-in and click OK.
Then if you check on your Data tab to the far right,
you should have a Solver tool ready to go.
All right, let's do a quick save to get the appropriate
file name going, File, Save As.
The only thing you should have to change on this file
is change the ending 1 to a 2 on the file name.
And I always make sure it's on my desktop as well.
And then I'll save.
All right, so let's go ahead and see what they would like us
to do in this project.
Step task one says that, "Bruce and Doug Ferguson
"started Ferguson Fitness providing comprehensive, private,
"and corporate fitness programs
"in Philadelphia, Pennsylvania.
"Bruce made a workbook detailing the services they offer.
"He asks you to help analyze the data
"to determine how they can increase profits."
So we will switch to the Personal Training worksheet
and create a one-variable data table
to calculate the sales, expenses, and profit
based on the hours of personal training provided as follows.
So in cell E5, we will enter a formula
without using a function that references cell C4
which is the number of hours of personal training provided.
So on the Personal Training worksheet,
if I click in cell E5, I will reference cell C4
by typing in an equals and clicking on cell C4,
which once again is the
personal training hours provided.
Rather than pressing Enter, if I press Tab
it should put me in place for the next reference.
We will type, in cell F5 we will reference cell C19,
so type in an equals under the Total Sales heading.
C19 is going to reference the Total Sales number
in cell C19, and press the Tab.
We want to do the same for Total Expenses in cell C21.
So we'll type an equals, or rather C20.
So make sure you get C20 in that reference.
Press the Tab and then reference the Gross Profit.
In cell H5, type in an equals
and reference the Gross Profit in cell C21.
I can go ahead and press Enter then,
and we're ready to create our one variable data table.
So what I'll need to do is select the information
that the instructions are asking me to select.
They want like E5 to H10 gives us the proper selection
for what we'll need to do to make this data table work,
and then we'll be referencing cell C4
as the Column input cell.
So with that range selected,
I can go ahead and click on Data,
and in the Forecast group, What-If Analysis Data Table.
Now because we only have a one-variable data table going
and it's about Hours Contracted,
we will click in the Column input cell
and reference cell C4 for Hours Sold,
and click OK, and this will appropriately
fill the data table.
All right, let's do a save.
I'm doing Control + S.
Let's go on to the next step.
In number two, we want to create a two-variable data table
to calculate the gross profit based on the hours
of fitness training provided and the hourly rate charged.
So they're giving us a range to select
for this two-variable data table.
We'll be using the hourly rate for the Row input cell,
and the hours of fitness training provided
for the Column input cell.
And then we'll do some formatting on that data table.
So if we select E4, I'm gonna scroll
over a little bit for a minute.
Need to be able to select clear over to column L.
So if I select, sorry, E14
over to L and down to Row 19,
I've got the correct selection made.
I will go to the Data tab Forecast group,
What-If Analysis Data Table,
and our instructions did mention that it wanted
to have the Row input cell be C5.
So I want you to see what that's going to be,
C5 is Price Per Hour.
Right, there's your prices per hour
from left to right in Row 14.
And then the Column input cell is going to be cell C4,
which is representing the hours that were sold
in this Column here.
Once you click OK, your data table will fill in
with the appropriate values.
Now one thing I wanted to point out here
is what's going on, particularly in cell E14.
This is what's making your data table work.
We have E14 right here is referencing cell C21,
which is all about the gross profit.
And you'll notice that the title in this data table
is all about gross profit analysis.
And it is what is driving this data that filled in
when we needed it to do so.
So this particular number isn't that helpful
to what we're trying to do.
So we're going to apply a custom format
to cell E14 to display the text Hours/Rate
in place of that cell value.
So in order to do this, make sure E14 is active,
right-click, choose to format that cell,
and in the options it's already considered
a Custom category.
But if you'll go over to the Type area
and scroll to the top, you'll find a General category.
And then once you have that General type selected,
you can select the word General just above that
and in quotes, type Hours/Rate,
close quote, and then click OK.
It gives us a more meaningful Column heading,
but it also leaves that reference up in the formula bar
to the Gross Profit, found in cell C21.
All right, so now on step three,
we're going to switch to the Consulting worksheet
and create a Scatter with Straight Lines chart
based on the range E4 to G14 in the data table
titled Consulting Break-Even Analysis.
So for Consulting, we will select E4 to G14,
which does not include the Gross Profit Column.
Kind of notice that that's what they want there,
and then we'll just go to Insert.
And in the Charts group, locate the Scatter Charts
and once again you'll looking
for Scatter with Straight Lines.
All right, so it's that last option in the Scatter Category.
All right, and once you have that chart ready to go,
we will modify the chart as follows.
We first want to resize and reposition the chart
so that it covers E15
to H30.
If I'm not sure I've reached 30,
I usually select that row just to make sure
that I include row 30 in my selection.
And then once you've got your chart resized
and repositioned, we want to remove the chart title.
So I'm gonna select the chart title and press Delete.
There are different ways to do that.
You can use your chart element tool over here
if you want to, or just select it,
select its placeholder and press Delete.
Would also like us to add Sales and Expenses
as the vertical axis title,
and Hours as the horizontal axis title.
So I'll use the Add Chart element,
and I will activate the axis titles for the vertical one.
I can triple click within its placeholder
and add Sales and Expenses.
And double-click or triple-click the axis title
to select what's in there by default and type Hours,
and click away.
All right, step five wants us to change
the Bounds Axis Options as follows.
We'll be changing the Minimum Bounds of the vertical axis
to -30,000 and let the Maximum Bounds automatically
change to 130,000.
We will also change the number format to,
on the vertical axis, to Currency with 0 decimal places
and a $ as the symbol.
And we will change the Minimum Bounds of the horizontal axis
to 700, and the Maximum Bounds to 1500.
So what we're doing here is we're looking at our lines
and we're noticing that they're not taking up a lot
of the grid area on the chart,
so we're making adjustments so that it looks better
and our lines take more of the space available.
So to change the Minimum Bounds of the vertical axis,
let's go ahead and select the vertical axis
and right-click it.
Choose to format that axis, and then over in our Format Axis
task pane, we can go to the Bounds section
and under Minimum type in -30,000.
And if I tab away from it, the Maximum Bounds
will automatically adjust.
Then let's go ahead and format the vertical axis
to Currency, so I'm gonna scroll down
in the Format Axis task pane and expand Number
where I can set the Category to Currency
and I can also make sure it's at zero decimal places.
And you can see that that's what will happen
over here on the vertical axis.
Now let's adjust our Minimum Bounds of the horizontal axis,
which is kind of moved up into the chart itself
because of the negative values.
Let's go ahead and right-click
the horizontal axis value, format the axis.
And for the Minimum Bounds it's 700.
And for the Maximum Bounds we're going to set it at 1500.
And as I press Enter there, you can see that the lines
now take up much more of the space available.
Now we will be creating two scenarios as follows
to compare the costs of hiring fitness instructors
with those for hiring certified personal trainers
while increasing the number of hours provided.
So in the Scenario Manager, we will add those two
new scenarios using the data shown in bold
in the Table below, in Table 1 below.
The changing cells for both scenarios
are the nonadjacent cells of C4, C11, and C14.
And then we'll close the Scenario Manager
without showing any of the scenarios.
So on the Consulting sheet, I will deselect this chart
and then I will go to the Data Tab,
the Forecast group, What-If Analysis,
and call up the Scenario Manager.
And it said it wanted us to create two scenarios,
so let's click Add.
The first scenario name from our Table 1
is called Instructors.
And it's supposed to be by changing cells
the specific cells are C4.
So I select whatever is in my changing cell box
and I can select C4, which is Hours Sold.
I'll hold my Control key down and also select C11
which is Variable Cost Per Hour,
and C14, which is Total Fixed Cost.
Right then I'm ready to click OK.
And the Hours Sold for Consulting for instructors was 1500.
The Variable Cost Per Hour for Consulting
for instructors was 53.
I'm tabbing to get to the next box
or you can go ahead and click on the box.
And then the Total Fixed Cost for Consulting
for instructors was 34,500.
I just put in the raw data
and I don't worry about the commas.
Click Add for the second scenario we want to set up,
that scenario name is Trainers.
The changing cells should not change,
otherwise it's not a comparable scenario.
So we'll leave the changing cells as-is, then click OK.
Then the Hours Sold Consulting is 1800.
The Variable Cost Per Hour for Consulting
is 55 for trainers, and the Total Fixed Cost for Consulting
for trainers is 35,500.
All right, then I can go ahead and click OK.
I can check my Instructors Scenario name
and my Trainers Scenario name
and if there are any problems there,
I can also go in and edit the information there
if I need to do that.
And then I can close the Scenario Manager.
Let's move on to task seven, which tells us to switch
to the Corporate Programs worksheet,
create a Scatter with Straight Lines chart
based on range E6 to J14 in the data table
titled Corporate Programs Net Income Analysis.
So let's go ahead and do that,
and then we will modify the chart.
So we're selecting in the Corporate Programs sheet
cells E6 to J14.
I'm gonna have to scroll over so we can see that.
And then I will choose to Insert,
and in the Charts group I'll go the Scatter category
and look for Scatter with Straight Lines.
All right, so we have that chart.
We do need to resize it and reposition it
so that it resides in E15 to J30.
We will remove the title from the chart.
We will reposition the chart legend
to the right of the chart.
We'll add Net Income as a vertical axis title
and Hours as the horizontal axis title.
And then we'll change the colors of the chart
to Monochromatic Palette 2,
and it tells us specifically where that is.
All right, so let's first move and reposition the chart
so that it begins in cell E15
and extends to J30.
Then let's remove the chart title.
I'm clicking on the placeholder and pressing delete.
I can also now reposition the chart legend,
which is along the bottom at this point.
So I'm clicking the chart legend.
I can right-click and choose to format the legend
and get the Format Legend task pane
and choose to put the chart legend on the right.
I can add a vertical axis title
by formatting the vertical axis.
Oh, I just want a title so I make sure the chart
is selected, let me size my task pane out a little bit
so I can use my quick tools over here in Chart Elements.
Keep in mind that you can come up to the ribbon too
and Add Chart Elements whenever you would like
as long as you're on the Chart Design tab.
But I'll use my Add Chart Elements tool,
I'll choose Axis Titles, and for the vertical axis
it should be Net Income.
And the horizontal should be Hours.
And then with the chart still selected
on the Chart Design tab, in the Chart Styles group
I can change colors
to Monochromatic Palette 2.
All right, let's take a look at task nine
that would like us to change the Bounds Axis Options
for this new chart.
The Minimum Balance will, the vertical axis will be -20,000
and we will leave the Maximum Bounds at 40,000.
We'll set the horizontal axis to cross
at the axis value -20,000.
We'll format the vertical axis for Currency
with 0 decimal places and a $ as the symbol,
and we will change the Minimum Bounds
of the horizontal axis to 900
and the Maximum Bounds to 1700.
All right, so the first thing we wanna do
is change the Minimum Bounds of the vertical axis.
So I'll select the vertical axis.
I will choose to format it.
And the Minimum Bounds are supposed to be set at -20,000.
We will leave the Maximum Bounds at 40,000.
And we will set the horizontal axis
to cross at the axis value of -20,000.
All right, let's see, um,
all right so if I choose under the horizontal axis crosses
the axis value, I'm gonna set that at 20,000, -20,000.
I want to change the number format
of the vertical axis to currency.
So I'll expand Number, set it to Currency,
and we should have zero decimal places.
And then we want to change the Minimum Bounds
of the horizontal axis, so I've selected the horizontal axis
and I'll right-click and Format.
The Minimum Bounds should be 900,
and the Maximum Bounds should be 1700.
Task number 10 would like us to edit
the chart series names as follows.
For Series 1, we're going to change it
to the series name in F5.
For Series 2, we'll change the series name
to the content in cell G5. (electronic chimes)
- [Electronic Automated Voice] I'm on it, hello?
(electronic chimes) Hmm.
- [Instructor] And then for, we'll follow the same situation
with Series 3, 4, and 5.
So when we go back here, we can select the legend.
It's basically what it is,
and if you right-click the, (chuckles)
the selection of the legend you can select the data.
And that puts you into an easy place to make changes
to the series names here.
So I'm gonna select Series 1, choose to edit it,
and I can simply go up and select F5.
I'll click, um, I think that's all I need to do there.
If I click OK you can see that that first
Series 1 is now $80.
So I'll select Series 2, I will edit that to be,
to include G5,
Edit Series 3 to be H5,
Edit Series 4 to be I5,
and Edit Series 5 to be J5,
and click OK. (dialog box chimes)
Okay, let me see,
I might have an error. (dialog box chimes)
Let's see which one it has not agreed with me on,
looks like the very last one
which should be a J5, and OK,
and OK and you can see now that the series names
have changed in this new chart.
Task 11 tells us
that Bruce wants to determine whether partnering
with another fitness company would reduce the costs
of the fitness training services.
So we will be switching to the Training Providers worksheet
and then run the Solver to solve this problem as follows.
What we're going to do is set the objective
as minimizing the value of F10 which is the Total Costs.
We will use the range C4 to E4
as the changing variable cells.
Adjust the hours provided by each company
using the following constraints.
So our constraints are listed here
and I will work through those
and kind of explain those as we go along.
So let's go back to our spreadsheet,
specifically we want to go to
the Training Providers worksheet,
and we're going to use the Solver.
So let's look here, we've got Training Providers here.
We've got Fixed and Variable Costs,
and then Total Costs here listed.
So, and different types of products,
the Express, the FitPros, the Universal, and the Total
for those different products or providers.
So let's go ahead and call up the Solver.
Once again, that's on our Data tab.
We added it at the beginning of the project
if you didn't have it already.
So on the Data tab, call up the Solver.
Our instructions tell us that we want to set the objective
as minimizing the value, so I'm gonna choose Minimizing
the value of what's in cell F10.
So if I can pull over and show you that F10
is actually the Total Costs.
We want to minimize the Total Costs
for Ferguson Fitness by changing the following cells.
So click in By Changing Variable Cells.
The cells that are variable are in C4 to E4
which are the Hours Provided, all right.
And then just so the Solver can actually find solutions
to what we need it to do,
we need it to add some constraints.
So the first thing we'll do is click on Add.
The first constraint in our instructions say
that F4 has to equal 600.
So F4 is the total hours,
you know, that this is what we have to work with
so we want to make sure that that's one of the constraints
that we have 600 hours to work with, click Add.
It also mentions that F10 is less than or equal to 130,000
and that's the maximum fees paid to another fitness company.
So once again, we select F10 and we say that it has
to be less than or equal to 130,000.
Once again that's the maximum fees paid
to another fitness company.
We'll add that constraint.
The next constraint says that C4 to E4,
or our Hours Provided, has to be less than or equal to 220,
220 which is the maximum
hours provided by a single fitness company.
So click Add, we have another constraint,
and we have one more.
We also have to make sure that C4 to E4
is set as an integer, meaning we can't just have
a partial hours recorded.
We want to have whole numbers as the hours
that would be showing in any one
of those cells in that range.
All right, so we can click OK now
and we will see a list of our constraints
just to make sure that we have set those up correctly.
Let's see what the next thing is to do in the step.
It wants us to run to the Solver
and keep the solution that the solver gives us,
and then return to the Solver Parameters Dialog box.
We will save that model in this particular range,
and then we'll go ahead and close that dialog box, okay?
So let's do that then.
So we want to run the Solver, we're going to click on Solve.
(dialog box chimes)
In the dialog box that is provided for us,
we want to keep the Solver solution
and we want to be able to return
to the Solver Parameters Dialog box so that we can then
load and save its results.
So our instructions specifically say to save the model
in range B14 to B21.
So I'm going to choose Load/Save.
I'm going to select B14 to be 21.
You can actually just start in B14 and not select the range,
but I like to see where the range will reside.
And then be sure, be sure to click Save.
And the results will be given to you
in the spreadsheet itself.
Your eText explains in detail what this model means
and what each entry means,
and I'll leave you time to go ahead
and check your eText for an explanation.
So let's go ahead and close the Solver.
So back to the instructions.
Task 12 would like us to switch
to the All Services worksheet.
We will use the Scenario Manager
to create a Scenario Summary report
that summarizes the effect of Current,
Increase Hourly, and Contractors scenarios.
Use the range C17 to E17 as the result cells.
So on the All Services worksheet,
let's go ahead and do that.
If you recall from before, the Scenario Manager
is in Data, What-If Analysis,
Scenario Manager, okay.
So remember we were able to in a previous task
create some scenarios.
This time we're gonna go directly to Summary
so that we can provide a Scenario Summary
that will use the range C17 to E17 as the result cell.
So let's see what's in those cells.
C17 to E17 is Profit Per Hour, all right?
So we go ahead and click OK.
A new sheet is created entitled Scenario Summary,
and this information is now provided in columns
next to one another.
Just quickly, the Scenario Summary shows the current values,
it shows the scenario if we stay with our current plan,
it also shows the Scenario Summary
if we increase our hourly,
and also if there is a contractor change.
All right, once again your eText can give you more details
on the Scenario Summary, but this is what it looks like.
So let's go ahead and switch back now
to the All Services worksheet
where we'll use the Scenario Manager
to compare the profit per hour in each scenario.
We will be doing a Scenario PivotTable report
for the result cells in C17 through E17.
We will remove the Filter from the PivotTable.
There's a Filter field on that PivotTable,
so we will remove that.
And then we will change the number format
of several of the PivotTable fields.
And we will use Personal Training as the row label
Consulting, in B3, and then Consulting is the value in C3,
and Corporate Programs as the value in D3.
We'll also in cell A1 use Profit Per Hour
as a report title.
We will format that report title as the Title cell style
and then we will resize columns A through D to 17.
So we're basically making this look like
a much nicer Scenario PivotTable report.
So back to the All Services worksheet.
Let's go to Data, Forecast group,
What-If Analysis, Scenario Manager.
And if you click on Summary, you can find an option
to activate the Scenario PivotTable report.
Checking to make sure the result cells
are still C17 to E17 which is our Profit Per Hour.
Go ahead and click OK.
And you have a new Scenario PivotTable sheet
with a PivotTable available.
The first thing it mentions that it wants us to do
is while the PivotTable is activated,
we want to remove this filter.
Now I'm not getting my PivotTable task pane.
So if this happens to you, you want to go up
to the PivotTable Analyze tab
and go ahead and activate the Field List.
Sometimes it disappears when we are using another task pane
or when we're in a more of a minimized view.
So the Field List activates it
on the PivotTable Analyze, all right?
So notice that there is a filter applied,
and you can see it also in Row 1
and it just wants us to remove that filter.
You can either use the dropdown arrow
and choose to remove the field,
or you can drag and drop it into the document window
and the filter will be removed.
All right, down here in the PivotTable task pane
it wants us to format the values in the PivotTable
to be currency with two decimal points
and a dollar sign symbol.
So I'm just gonna, with practice, use my dropdown arrows
on these fields, choose the Value Field setting,
and go into the Number Formatting
and set it up for Currency, two decimals,
the dollar sign by default is there.
Do the same thing, so click OK, and do the same thing
for the other values in the PivotTable.
Once again, Number Format, Currency,
and then make sure you get the last one as well.
Go to cell B3.
You can do this in the PivotTable task pane
or you can simply click on cell B3
and change that little bit of an awkward
column heading there to profit, person,
oh let me double-check the name of that, Personal Training.
I'll tab to the next field in cell C3
where they'd like it to say Consulting,
and cell D3 should say Corporate Programs.
Those are more appropriate column headings there
for the PivotTable.
In cell A1, let's go ahead and type in Profit Per Hour
as the report title,
and then search for the appropriate cell style.
We would like it to have the title cell style.
So I'm on the ribbon in the Styles group,
Home tab, Styles group, dropdown list Title.
We'd like to resize columns A through D to 17 characters,
so I'm gonna select all four columns.
I'm gonna right-click, choose Column Width, type in 17.00.
That's exactly what the instructions ask for,
so that's what I'm going to do.
All right, we haven't saved for awhile
so let's go ahead and deselect the PivotTable
and do a quick Control + S to save our changes.
You might want to save more often than I have
just to make sure you make it through a task
and you're ready to go on without any worries
of your work being saved.
One last step is to add a PivotChart
to the Scenario PivotTable worksheet as follows.
It will be a Clustered Column PivotChart
based on that PivotTable.
We'll resize and reposition the chart
so that it covers the range A8 to D21
and then we will hide the Field Buttons.
So back to the PivotTable.
Make sure it is active,
and then we'll go ahead and create a PivotChart.
On the PivotTable Analyze tab, if your PivotTable is active
you've got a PivotChart button
that you can go ahead and use.
Clustered Column is actually the default, so click OK.
All right, let's move that chart so it's in cell A8 to D21.
And hide the Fields Button in the chart, okay?
So we've got these, these can be distracting on a PivotChart
so if you feel like they are annoying or distracting
go up to your ribbon and kinda look around
and see if you can find a tool to hide those.
It's gotta be on one of your contextual tabs.
It's on PivotChart Analyze,
if I choose the Field Buttons arrow I can Hide All.
Be sure to check your final figures on the following pages
of the instructions and then do one final save
on your changes, close the workbook,
and upload your project.
Thanks, everyone!