Barry Cheney, the Golf Course
Manager at the Red Bluff Golf Course & Pro Shop, has been considering
expanding the clubhouse to accommodate a steady increase in business. This
expansion could include more space for the pro shop and more guest
accommodations. Barry will need to provide a detailed analysis of past sales
along with sales forecasts to assure William Mattingly, the resort’s CEO, that
the money spent on the improvements and expansion will have positive financial
benefits for Red Bluff. To increase management’s understanding of the current
capacities, Barry has collected data about traffic, sales, and product mix. He
has asked you to analyze this data, using Excel’s What-If Analysis tools.
Step |
Instructions |
Points Possible |
1 |
Start Excel. Open the downloaded file named Excel_Ch10_Prepare_ExpansionAnalysis_1of2.xlsx.
Grader has automatically added your last name to the beginning of the
filename. |
0 |
2 |
The Red Bluff Golf Course &
Prop Shop offers golf lessons to its clients for a fee of $140.00 per lesson.
The costs for each golf lesson comprises both fixed and variable costs.
Conducting a break-even analysis will determine the minimum number of clients
Red Bluffs needs to have signed up for golf lessons to offset the costs. |
3 |
3 |
In cell D13, calculate the total fixed costs associated
with providing golf lessons. |
3 |
4 |
In cell D15, calculate the total
commission the golf instructors will earn by multiplying the gross revenue by
the instructor commission. |
3 |
5 |
In cell D16, calculate the total cost of supplies by
multiplying the amount of supplies per client by the total clients. |
3 |
6 |
In cell D17, calculate the total
variable costs by adding the total instructor commission and the total cost
of supplies. |
3 |
7 |
In cell D18, calculate the total expenses by adding up the
total fixed costs and the total variable costs. |
3 |
8 |
In cell D19, calculate the net
income by subtracting the total expenses from the gross revenue. |
3 |
9 |
In cell D4, conduct a simple what-if analysis by changing
the value to 50,
then 60,
and finally 65,
noticing the changes to the net income in cell D19. |
2 |
10 |
Incorporating a scroll bar form
control into the break-even analysis will make it easier to determine the
number of clients necessary for Red Bluff to break even. |
5 |
11 |
Conditional formatting can be used to highlight the Net
Income value, in cell D19, in two different ways. One set of formatting can
be applied if the net income results in a loss; another can be applied if the
net income results in a profit. |
6 |
12 |
A one-variable data table can be
used to determine how much the monthly payment on a loan would be based on
varying interest rates. Conditional formatting can then be applied to add
emphasis and increase the readability of the monthly payment. |
4 |
13 |
In cell E2, reference the cell that calculates the monthly
payment amount for the loan. Apply a custom format to cell E2 so that the
cell appears to be Monthly Payment. |
3 |
14 |
Finish the one-variable data
table to see the monthly payment amount for each interest rate. |
5 |
15 |
Apply conditional formatting to the range E3:E9 using the
gradient fill, green data bar option. |
4 |
16 |
A one-variable data table can be
used to determine how the changes in client demand for golf lessons will
affect the expenses, revenue, and net income. You will also apply some
conditional formatting to add emphasis and increase the readability of the
Net Income. |
6 |
17 |
Apply custom formatting to cells H4:J4 so that the results
of the referenced cells are hidden. |
5 |
18 |
Finish the one-variable data
table to see how the expenses, revenue, and net income change when the total
number of clients vary from 10 to 100. |
5 |
Get Free Quote!
425 Experts Online