Module 8 Project 1B

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