# Sc_ex19_9b_firstlastname_1.xlsx | Computer Science homework help

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