- [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!