B E F 1 2 C D Red Bluff Golf Course & Pro Shop Profit Scenarios 3 Revenue 4 Retail Sales $115,000.00 5 Golf Lessons and Fees $340,000.00 6 Gross Revenue $455,000.00 7 Expenses 8 Fixed Costs 9 Manager Salaries $5,135.90 10 Utilities $469.70 11 Equipment Depreciation $1,234.20 12 Insurance $360.80 13 Total Fixed Costs $7,200.60 14 Variable costs 15 Employee Commissions 17% $77,350.00 16 Total Expenses $84,550.60 17 Net Income $370,449.40 18 B C D E F G 1 Red Bluff Golf Course & Pro Shop 2 Solver for Golf Lessons 3 $21,920,696.65 2 FALSE TRUE 4 TRUE TRUE TRUE 32767 5 o Instructors on Duty 4 6 Revenue 7 Lesson Fee $190.00 8 Gross Revenue 9 Expenses 10 Fixed Costs 11 Utilities $469.70 12 Equipment Depreciation $1,234.20 13 Insurance $360.80 14 Total Fixed Costs $2,064.70 15 Variable Costs 16 Instructor Commission 10% $2,490,292.00 17 Supplies per Client $14.95 $489,866.65 18 Total Variable costs $2,980,158.65 19 Total Expenses $2,982,223.35 20 Net Income 21 22 Saved Solver Models 23 $21,920,696.65 24 2 25 FALSE TRUE 27 TRUE 28 TRUE 29 TRUE 30 32767 31 0 32 26 D E Net Income $D$17 B 1 Retail Sales and Golf Lessons and Fees (All) 2 Scenario Pivot Table Report (All) 3 Scenarios Gross Revenue Commision 4 Row Labels $D$6 $C$15 5 Best Case Scenario 6 Worst Case Scenario $125,000 0.095 7 Most Likely Scenario 8 Worst Case Scenario 187500 0.1 9 10 11 12 13 $105,924 $161,549 15 16 17 1 Red Bluff Golf Course & Pro Shop Forecast of Product Sales 2 3 Qty. Price $37.00 Inventory Items Stock Golf Balls Golf Shorts Large Golf Umbrella High Performance Golf Polo Extended Price $0 $0 $0 $0 300 125 Goal $27,500 $14,000 $7,500 $20,100 $75.00 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 1 Red Bluff Golf Course & Pro Shop Profit Scenarios 3 Revenue Retail Sales Golf Lessons and Fees Gross Revenue $65,000.00 $220,000.00 5 5 - Expenses 3 Fixed Costs Manager Salaries Utilities Equipment Depreciation Insurance $5,135.90 $469.70 $1,234.20 $360.80 Employee Commissions 17% 0 1 2 3 Total Fixed Costs 4 Variable costs 5 5 6 Total Expenses 7 Net Income 8 9 0 0 1 2. 3 4 1 1 $190.00 $190.00 Red Bluff Golf Course & Pro Shop Solver for Golf Lessons Input Variables Total Clients Instructors on Duty Revenue Lesson Fee Gross Revenue Expenses Fixed Costs Utilities Equipment Depreciation Insurance Total Fixed Costs Variable Costs Instructor Commission 10% Supplies per Client $14.95 Total Variable Costs Total Expenses Net Income $469.70 $1,234.20 $360.80 $2,064.70 $19.00 $14.95 $33.95 $2,098.65 $1,908.65 Saved Solver Models 13 0.5 Create a Best Case Scenario where the total revenue in retail sales is $115,000 and total revenue in golf lessons and fees is $340,000. 14 0.5 Create a Worst Case Scenario where the total revenue in retail sales is $35,000 and total revenue in golf lessons and fees is $90,000. 15 0.4 Scenarios can be viewed individually, but it is often more helpful to view them side-by-side. Generate a Scenario Summary report to show the results of the current scenario, Best Case Scenario, and Worst Case Scenario. Make sure that the result cell is set to D17, Net Income. 16 0.4 17 0.5 Format the Scenario Summary report so that it is easier to read by completing the following. Merge & Center cells B6:C6 and replace the text $D$4 with Retail Sales. Merge & Center cells B7:07 and replace the text $D$5 with Lessons and Fees. Merge & Center cells B9:C9 and replace the text $D$17 with Net Income. Edit the Best Case Scenario so that the changing cells are 04:05 and C15. Keep the current scenario value for cell C15 as 0.17. Edit the Worst Case Scenario so that the changing cells are 04:05 and C15. Change the scenario value for C15 to 0.095. Create a new Most Likely Scenario using 04:05 and C15 as the changing cells. Set the scenario value for D4 to 25000, D5 to 162500 and C15 to 0.10. Create a Scenario PivotTable report to show the gross revenue, commission, and net income of each scenario. Use cells D6, C15, and D17 as the result cells. 18 0.4 19 0.6 Format the Scenario PivotTable report so that it is easier to read by completing the following In cell A1, replace existing text with Retail Sales and Golf Lessons and Fees. Adjust the width of Column A to 34. In cell A2, type Scenario PivotTable Report. Merge & Center cells A2:D2, apply a Bold style, and adjust the font size to 16. In cell A3, type Scenarios. In cell B3, type Gross Revenue, in cell C3, type Commission, and in cell D3, type Net Income. Center the data in cells B3:03 and adjust the width of columns B:C to 14. 20 0.4 21 0.3 22 0.4 Format cells B4:B6 and D4:06 as Currency with 0 decimal places. Format cells C4:C6 as Percentage with 1 decimal place. The golf course manager wants you to find the total number of clients, the number of hours of lessons per day, and the number of instructors on duty needed to maximize net income. The worksheet you were given was previously set up with functions to calculate the net income. The Solver tool in Excel can optimize the net income while meeting several constraints within the worksheet. If necessary, load the Solver Add-in. On the Golflessons worksheet, begin the process of creating a solver model by setting the objective cell to D20. Be sure the objective is set to Max and use cells 04:05 as the changing cells. 23 0.4 Continue creating the solver model by defining the following constraints. There can only be 4 or fewer instructors scheduled at any given time. Instructors can give anywhere from 7 to 14 lessons per day. The total clients and instructors on duty must be an integer. 24 0.4 Generate a Solver Answer report by modifying the settings as follows. Use the GRG Nonlinear solving method. Under Options, click the All Methods tab and verify the Use Automatic Scaling check box is unchecked. On the GRG Nonlinear tab, click the check to Use Multistart and then click to uncheck the Require Bounds on Variables check box. In the Solver Results dialog box, verify that the Keep Solver Solution option is selected. Create an Answer report. On the Golflessons worksheet, restore the original values in the worksheet by typing 1 in cells D4 and D5. Save the Solver model, starting in cell A23. 25 0.3 26 0.4 Edit the constraint in the Solver model that restricts the number of clients to 14 or less. Change it so that the number of clients can be 25 or less. Also, edit the constraint that restricts the number of instructors to 4 or less. Change it so that the number of instructors can be 4 or more. Add a constraint so that the number of instructors on duty can be no more than 7. Save the revised Solver model, starting in cell B23. Generate another Solver Answer report with the revised constraints. Be sure to check the Restore Original Values option. 27 0.2 28 0.2 B E F 1 2 C D Red Bluff Golf Course & Pro Shop Profit Scenarios 3 Revenue 4 Retail Sales $115,000.00 5 Golf Lessons and Fees $340,000.00 6 Gross Revenue $455,000.00 7 Expenses 8 Fixed Costs 9 Manager Salaries $5,135.90 10 Utilities $469.70 11 Equipment Depreciation $1,234.20 12 Insurance $360.80 13 Total Fixed Costs $7,200.60 14 Variable costs 15 Employee Commissions 17% $77,350.00 16 Total Expenses $84,550.60 17 Net Income $370,449.40 18 B C D E F G 1 Red Bluff Golf Course & Pro Shop 2 Solver for Golf Lessons 3 $21,920,696.65 2 FALSE TRUE 4 TRUE TRUE TRUE 32767 5 o Instructors on Duty 4 6 Revenue 7 Lesson Fee $190.00 8 Gross Revenue 9 Expenses 10 Fixed Costs 11 Utilities $469.70 12 Equipment Depreciation $1,234.20 13 Insurance $360.80 14 Total Fixed Costs $2,064.70 15 Variable Costs 16 Instructor Commission 10% $2,490,292.00 17 Supplies per Client $14.95 $489,866.65 18 Total Variable costs $2,980,158.65 19 Total Expenses $2,982,223.35 20 Net Income 21 22 Saved Solver Models 23 $21,920,696.65 24 2 25 FALSE TRUE 27 TRUE 28 TRUE 29 TRUE 30 32767 31 0 32 26 D E Net Income $D$17 B 1 Retail Sales and Golf Lessons and Fees (All) 2 Scenario Pivot Table Report (All) 3 Scenarios Gross Revenue Commision 4 Row Labels $D$6 $C$15 5 Best Case Scenario 6 Worst Case Scenario $125,000 0.095 7 Most Likely Scenario 8 Worst Case Scenario 187500 0.1 9 10 11 12 13 $105,924 $161,549 15 16 17 1 Red Bluff Golf Course & Pro Shop Forecast of Product Sales 2 3 Qty. Price $37.00 Inventory Items Stock Golf Balls Golf Shorts Large Golf Umbrella High Performance Golf Polo Extended Price $0 $0 $0 $0 300 125 Goal $27,500 $14,000 $7,500 $20,100 $75.00 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 1 Red Bluff Golf Course & Pro Shop Profit Scenarios 3 Revenue Retail Sales Golf Lessons and Fees Gross Revenue $65,000.00 $220,000.00 5 5 - Expenses 3 Fixed Costs Manager Salaries Utilities Equipment Depreciation Insurance $5,135.90 $469.70 $1,234.20 $360.80 Employee Commissions 17% 0 1 2 3 Total Fixed Costs 4 Variable costs 5 5 6 Total Expenses 7 Net Income 8 9 0 0 1 2. 3 4 1 1 $190.00 $190.00 Red Bluff Golf Course & Pro Shop Solver for Golf Lessons Input Variables Total Clients Instructors on Duty Revenue Lesson Fee Gross Revenue Expenses Fixed Costs Utilities Equipment Depreciation Insurance Total Fixed Costs Variable Costs Instructor Commission 10% Supplies per Client $14.95 Total Variable Costs Total Expenses Net Income $469.70 $1,234.20 $360.80 $2,064.70 $19.00 $14.95 $33.95 $2,098.65 $1,908.65 Saved Solver Models 13 0.5 Create a Best Case Scenario where the total revenue in retail sales is $115,000 and total revenue in golf lessons and fees is $340,000. 14 0.5 Create a Worst Case Scenario where the total revenue in retail sales is $35,000 and total revenue in golf lessons and fees is $90,000. 15 0.4 Scenarios can be viewed individually, but it is often more helpful to view them side-by-side. Generate a Scenario Summary report to show the results of the current scenario, Best Case Scenario, and Worst Case Scenario. Make sure that the result cell is set to D17, Net Income. 16 0.4 17 0.5 Format the Scenario Summary report so that it is easier to read by completing the following. Merge & Center cells B6:C6 and replace the text $D$4 with Retail Sales. Merge & Center cells B7:07 and replace the text $D$5 with Lessons and Fees. Merge & Center cells B9:C9 and replace the text $D$17 with Net Income. Edit the Best Case Scenario so that the changing cells are 04:05 and C15. Keep the current scenario value for cell C15 as 0.17. Edit the Worst Case Scenario so that the changing cells are 04:05 and C15. Change the scenario value for C15 to 0.095. Create a new Most Likely Scenario using 04:05 and C15 as the changing cells. Set the scenario value for D4 to 25000, D5 to 162500 and C15 to 0.10. Create a Scenario PivotTable report to show the gross revenue, commission, and net income of each scenario. Use cells D6, C15, and D17 as the result cells. 18 0.4 19 0.6 Format the Scenario PivotTable report so that it is easier to read by completing the following In cell A1, replace existing text with Retail Sales and Golf Lessons and Fees. Adjust the width of Column A to 34. In cell A2, type Scenario PivotTable Report. Merge & Center cells A2:D2, apply a Bold style, and adjust the font size to 16. In cell A3, type Scenarios. In cell B3, type Gross Revenue, in cell C3, type Commission, and in cell D3, type Net Income. Center the data in cells B3:03 and adjust the width of columns B:C to 14. 20 0.4 21 0.3 22 0.4 Format cells B4:B6 and D4:06 as Currency with 0 decimal places. Format cells C4:C6 as Percentage with 1 decimal place. The golf course manager wants you to find the total number of clients, the number of hours of lessons per day, and the number of instructors on duty needed to maximize net income. The worksheet you were given was previously set up with functions to calculate the net income. The Solver tool in Excel can optimize the net income while meeting several constraints within the worksheet. If necessary, load the Solver Add-in. On the Golflessons worksheet, begin the process of creating a solver model by setting the objective cell to D20. Be sure the objective is set to Max and use cells 04:05 as the changing cells. 23 0.4 Continue creating the solver model by defining the following constraints. There can only be 4 or fewer instructors scheduled at any given time. Instructors can give anywhere from 7 to 14 lessons per day. The total clients and instructors on duty must be an integer. 24 0.4 Generate a Solver Answer report by modifying the settings as follows. Use the GRG Nonlinear solving method. Under Options, click the All Methods tab and verify the Use Automatic Scaling check box is unchecked. On the GRG Nonlinear tab, click the check to Use Multistart and then click to uncheck the Require Bounds on Variables check box. In the Solver Results dialog box, verify that the Keep Solver Solution option is selected. Create an Answer report. On the Golflessons worksheet, restore the original values in the worksheet by typing 1 in cells D4 and D5. Save the Solver model, starting in cell A23. 25 0.3 26 0.4 Edit the constraint in the Solver model that restricts the number of clients to 14 or less. Change it so that the number of clients can be 25 or less. Also, edit the constraint that restricts the number of instructors to 4 or less. Change it so that the number of instructors can be 4 or more. Add a constraint so that the number of instructors on duty can be no more than 7. Save the revised Solver model, starting in cell B23. Generate another Solver Answer report with the revised constraints. Be sure to check the Restore Original Values option. 27 0.2 28 0.2