Shelly Cashman Excel 2019 | Module 9: SAM Project 1b Green Lake Sports Camp FORMULA AUDITING, DATA VALIDATION, AND COMPLEX PROBLEM SOLVING GETTING STARTED ? Open the file SC_EX19_9b_ FirstLastName _1

Subject:MS ExcelPrice:20.01 Bought5

Share With

Shelly Cashman Excel 2019 | Module 9: SAM Project 1b

Green Lake Sports Camp

FORMULA AUDITING, DATA VALIDATION, AND COMPLEX PROBLEM SOLVING

GETTING STARTED

?

Open the file

SC_EX19_9b_

FirstLastName

_1.xlsx

, available for download from the

SAM website.

?

Save the file as

SC_EX19_9b_

FirstLastName

_2.xlsx

by changing the “1” to a “2”.

?

If you do not see the

.xlsx

file extension in the Save As dialog box, do not type it. The

program will add the file extension for you automatically.

?

With the file

SC_EX19_9b_

FirstLastName

_2.xlsx

still open, ensure that your first and

last name is displayed in cell B6 of the Documentation sheet.

?

If cell B6 does not display your name, delete the file and download a new copy from the

SAM website.

?

To complete this project, you need to add the Solver Add-in. If Solver is not listed under

the Analysis section of the Data ribbon, click the File tab, click Options, and then click the

Add-Ins category. In the Manage box, select Excel Add-ins and then click Go. In the Add-

Ins box, check the

Solver Add-in

check box, and then click OK to install. If Solver Add-in

is not listed in the Add-Ins available box, click Browse to locate it.

PROJECT STEPS

1.

Takara Hiyashi is on the board of the Green Lake Sports Camp, a recreational summer

camp in Syracuse, New York. She is using an Excel workbook to analyze the camp's

financials and asks for your help in correcting errors and solving problems with the data.

Go to the

Teams

worksheet. Takara asks you to correct the errors in the worksheet.

Correct the first error as follows:

a.

Use the

Trace Precedents

arrows to find the source of the #VALUE! error in cell

C8.

b.

Use the

Trace Dependents

arrows to determine whether the formula in cell C8

causes other errors in the worksheet.

c.

Correct the formula in cell C8, which should add the baseball registration fee per

person (cell

C4

) and the equipment fee (cell

C7

), and then multiply the result by

the minimum number of campers (cell

C6

).

d.

Remove the trace arrows.

2.

Correct the Name error in cell C22 as follows:

a.

Use any error-checking method to determine the source of the error in cell C22,

which should calculate the average revenue per week.

b.

Correct the error by editing the formula in cell C22.

3.

Correct the divide by zero errors as follows:

a.

Evaluate the formula in cell C18 to determine which cell is causing the divide by

zero error.

b.

Correct the formula in cell C18, which should divide the revenue per session (cell

C16

) by the minimum number of campers (cell

C6

).

c.

Fill the range

D18:G18

with the formula in cell C18.

4.

Takara suspects that the remaining divide by zero errors and the two negative values in

the range E16:E18 are related to the zero value in cell E6. She wants to make sure that

anyone entering the minimum number of campers enters a number greater than zero.

Add data validation to the range C6:G6 as follows:

a.

Set a data validation rule for the range

C6:G6

that allows only

whole number

values

greater than 0

.

b.

Add an Input Message using

Number of Campers

as the Input Message Title and

the following text as the Input message:

Enter the minimum number of campers for this session.

c.

Add an Error Alert using the

Stop

style,

Campers Error

as the Error Alert Title,

and the following text as the Error message:

The minimum number of campers must be greater than 0.

5.

Identify the invalid data in the worksheet and correct the entry as follows:

a.

Circle the invalid data in the worksheet.

b.

Type

10

as the minimum number of campers for the lacrosse sessions (cell E6).

c.

Verify that this change corrected the remaining divide by zero errors and resulted

in positive values in the range E16:E18.

6.

Go to the

Private Lessons

worksheet. This worksheet analyzes financial data for private

and semi-private lessons, which the camp runs throughout the day. Takara has already

created a scenario named Current Campers that calculates profit based on the current

number of campers enrolled for each session. She also wants to calculate profit based on

the maximum number of campers.

Add a new scenario to compare the profit with maximum enrollments as follows:

a.

Use

Max Campers

as the scenario name.

b.

Use the enrolled campers per day data (range

C9:G9

) as the changing cells.

c.

Enter cell values for the Max Campers scenario as shown in bold in Table 1, which

are the same values as in the range C8:G8.

Table 1: Cell Values for the Max Campers Scenario

Cell

Value

Baseball_Campers (cell C9)

10

Basketball_Campers (cell D9)

12

Lacrosse_Campers (cell E9)

10

Soccer_Campers (cell F9)

12

Volleyball_Campers (cell G9)

15

7.

Takara also wants to calculate profit based on the minimum number of campers.

Add another new scenario to compare the profit with low session enrollment as follows:

a.

Add a scenario to the worksheet using

Min Campers

as the scenario name.

b.

Use the enrolled campers per day data (range

C9:G9

) as the changing cells.

c.

Enter cell values for the Min Campers scenario as shown in bold in Table 2.

Table 2: Cell Values for the Min Campers Scenario

Cell

Value

Baseball_Campers (cell C9)

8

Basketball_Campers (cell D9)

8

Lacrosse_Campers (cell E9)

7

Soccer_Campers (cell F9)

8

Volleyball_Campers (cell G9)

7

8.

Show the

Min Campers

scenario values in the

Private Lessons

worksheet.

9.

Go to the

Revised Fees

worksheet. Takara is considering whether to change the coaching

fees for the private lessons. She has created three scenarios on the

Revised Fees

worksheet showing the profit with a $5 or $10 increase or a $5 decrease to the coaching

fees.

Compare the average profit per session based on the scenarios as follows:

a.

Create a Scenario Summary report using the average profit per session (range

C11:G11

) as the result cells to show how the average profit changes depending on

the coaching fee changes.

b.

Use

Revised Fees Scenario Report

as the name of the worksheet containing the

report.

10.

Takara also wants to focus on one or two types of private lessons at a time when

comparing the average profit per session. Return to the

Revised Fees

worksheet and

create another type of report as follows:

a.

Create a Scenario PivotTable report using the average profit per session (range

C11:G11

) as the result cells to compare the average profit depending on the fee

changes in a PivotTable.

b.

Use

Revised Fees PivotTable

as the name of the worksheet containing the

PivotTable.

c.

Format cells B4:F6 in the

Revised Fees PivotTable

worksheet using the

Accounting

number format with

0

decimal places and

$

as the symbol.

11.

Go to the

Games

worksheet. Takara wants to determine the number of games the camp

can hold on Fridays and Saturdays to make the highest weekly profit without interfering

with practices, which are also scheduled for Fridays and Saturdays and use the same

resources.

Use Solver to find this information as follows:

a.

Use the total weekly profit (cell

H17

, named Total_Weekly_Profit) as the objective

cell in the Solver model, with the goal of determining the maximum value for that

cell.

b.

Use the number of Friday and Saturday games for the five sports (range

C5:G6

) as

the changing variable cells.

c.

Determine and enter the constraints based on the information provided in Table 3.

d.

Use

Simplex LP

as the solving method to find a global optimal solution.

e.

Save the Solver model in cell

B27

.

f.

Solve the model, keeping the Solver solution.

Table 3: Solver Constraints

Constraint

Cell or Range

Each game is scheduled at least once on Friday and

once on Saturday

C5:G6

Each Friday and Saturday game value is an integer

C5:G6

Each sport is scheduled for a game 1 time per week or

more

C7:G7

Each sport is scheduled for a game 3 times per week

or less

C7:G7

The total number of Friday games is 10 or less

Total_Friday_Games (H5)

The total number of Saturday games is 15 or less

Total_Saturday_Games (H6)

The total number of games per week is 13

Total_Weekly_Games (H7)

The total number of Friday practices is 2 or less

Friday_Practices (E21)

The total number of Saturday practices is 2 or less

Saturday_Practices (E22)

The total number of practices per week is 5 or less

Total_Practices (E23)

12.

Takara wants to document the answer Solver found, including the constraints and a list of

the values Solver changed to solve the problem. Produce an Answer report for the Solver

model as follows:

a.

Solve the model again, this time choosing to produce an

Answer

report.

b.

Use

Games Answer Report

as the name of the worksheet containing the Answer

report.

Your workbook should look like the Final Figures on the following pages. Save your changes, close

the workbook, and then exit Excel. Follow the directions on the SAM website to submit your

completed project.

Final Figure 1: Teams Worksheet

Final Figure 2: Private Lessons Worksheet

Final Figure 3: Revised Fees Scenario Report Worksheet

Final Figure 4: Revised Fees PivotTable Worksheet

Final Figure 5: Revised Fees Worksheet

Final Figure 6: Games Answer Report Worksheet

Final Figure 7: Games Worksheet