A- A Boo y Cashmon Bicel 2019 Module : SAM Project 1b SC Green Lake Sports Camp FORMULA AUDITING. DATA VAUDATION AND COMPLEX PROBLEM SOLVING . GETTING STARTED Open the fileSC_EX19_b FirstLastName_1.xlsx, available for download from the SAM website Save the file as SC EX19_9b_FirstLastName_2.xlsx by changing the l' to a "2" If you do not see the risk 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.95 FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell 6 of the Documentation sheet 1 cellB6 does not display your name, delete the file and download a new copy from the SAM witbit 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, dick the File tab, click Options, and then dick the Aas-ins category. In the Manage box select Excel Add-ons and then click Go. In the Add-ins box check the Solver Add-in check box, and then click OK to instalar Solver Add-in solisted in the Add-ins aviable box, click Browse to locate it PROJECT STUPS 1 Takaryashion the board of the Green Lake Sports Camp, a recreational summer came in Syracuse, New Yor. She is to work to analyze the camps Gracias and is for your incorrectang reading problems with the data Go to the Teamworksheet Takarask you to correct the error in the worksheet Correct the terror as to Uthe Trace Precedents arrows to find the source of the VALUE error call Use the Trace Dependent on to determine whether the one in causes or errors in the worst Correct the form which end add nebestrationer procedemente col c. and the multiply the result by the man who camers (oc) R. Crethe22 method to the water in which were we Coretthetic CENGAGE BO 3 909 ODD F4 --- Abbcd & AaBb AoBbCcDc Aabbcode AobCcDc AaBbccde Project Goal SAMBodyc... SAMFigurel Main Project Normal Project Bod Cosman Excel 2019 Module 9: SAM Project lb 4. 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. Takara suspects that the remaining divide by zero errors and the two negative values in the range 16: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 CS: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. identify the invalid data in the worksheet and correct the entry as follows: Circle the Invalid data in the worksheet. b Type 10 as the minimum number of campers for the lacrosse sessions (cellB6). Verify that this change corrected the remaining divide by zero errors and resulted In positive values in the range 16:16 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 amed 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: Use Max Campers as the scenario name Use the enrolled campers per day data (range C969) as the changing cells. Enter cell values for the Max Campers scenario as shown in bold in Table 1, which are the same values as in the range CG Tobiet: Col Valves for the Max Campers Scenario Vare 10 Games Faste campers Le camere Soccer Dames Coll2 10 12 15 Mailings Review ferences View =,=, lul - A- AS. Bod A == ally Cashman Excel 2019 Module 9: SAM Project 1b a. 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 follow Add a scenario to the worksheet using Min Campers as the scenario name. b. Use the enrolled campers per day data (range 09:69) as the changing cells. C. Enter cell values for the Min Campers scenario as shown in bold in Table 2. All Table 2: Cell Values for the Min Campers Scenario Cell Baseball Campers cell 09) Basketball Campers (cell D9) Lacrosse Campers (cell E9) Soccer Campers Volleyball Campers GO Value 8 8 7 8 B 9 Show the Min Campers scenario values in the Private Lessons worksheet. Go to the Revised Fees worksheet. Takra 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 SS or S10 Increase or a $5 decrease to the coaching fees. Compare the average profit per session based on the scenarios as follows: Create a Scenario Summary report using the average profit per session (range C111611) as the result celis 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 Pees worksheet and Create another type of report as follows: Create a Scenario PivoTable report using the average profit per session (range C111011) as the result cells tompare the average profit depending on the fee changes in a Pivottable Uw Revised Fees PivotTables the name of the worlesheet containing the Pivottable Format cell 046 in the Revised Fees Plotte worksheet using the Accounting number format with decimal places nd as the symbol 11. Go to the Games we have wants to determine the number of games the camp can hold on Fridays and Saturdays to meet without interfering rices, which are the forces and Saturdays and use the same + e scenario as shown in bold in Table 1, are the same values as in the range CS:G8. Table 1: Cell Valves for the Max Compers Scenario Cell Baseball Campers (cell 09 Basketball Campers (cell D9) Lacrosse Campers (cell E9) Soccer_Campers cell 09 Volleyball Campers (eel 69) Value 10 12 10 12 15 CENGAGE Shelly Cashman Excel 2019 Module S. SAM Project 1b resources Use Solver to find this information as follows: Use the total weekly profit (ell 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 CS:G6) as the changing variable cells Determine and enter the constraints based on the formacion 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 827 Solve the model, keeping the Solver solution Table 3: Solver Constraints C constraint Each game is scheduled at least once on Friday and CSIGS Lonce on Saturday Each Friday and turdey game an integer CS:GO Each sport is scheduled for a game fome per ekor more Each sport is scheduled for game mes per week C7167 or less The tomber day The number of auctor Turday Game ( The number of CICE PRIREZI The total number of de practice Saturday Practice (2) The follow PERCEISER Q-5 EX19 9 Autoceste AaBb ActCD Atcete ABDCDC Astocco SABdy Alicet SAM SAUR CENGAGE Sholy Cosmon Excel 2010 Module 9 SAM Project resources Use Solver to Find this information as follows: # Use the total weekly profit (ell H17, named Total Weekly Profit) as the objective cell in the Solver model, with the goal of determining the maximum value for that Use the number of Friday and Saturday games for the five sports (range CS:C6) as the changing variable cells. Determine and enter the constraints based on the information provided in Table d. Use Simplex LP as the solving method to find a global optimal solution. e Save the Solver model in cell 827 1. Solve the model, keeping the Solver solution Table 3: Solver Constraints + Constraint Ce or Range Each game is scheduled at least once on Friday and CS:G6 once on Saturday Each day and Saturday game value is an integer CS:G6 Each sport is scheduled for a game 1 time per week or 07:07 more Each sport is scheduled for a game 3 times per week C7:67 or less The total number of Friday games or less Total Friday_Games (HS) The total number of Saturday games is 15 or less Total Saturday Games (H) The total number of games per week 13 Total Weekly_Games (7) The total number of pride practices is 2 or less Friday Practices (121) The total number of Saturday practices is 2 or less Saturday Practices (22) The total number of practices per week 5.5 or less Total Practices (2) 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: Solve the model again, this time choosing to produce an Answer report. D. 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 ext Excel. Follow the directions on the SAM website to submit your completed project A- A Boo y Cashmon Bicel 2019 Module : SAM Project 1b SC Green Lake Sports Camp FORMULA AUDITING. DATA VAUDATION AND COMPLEX PROBLEM SOLVING . GETTING STARTED Open the fileSC_EX19_b FirstLastName_1.xlsx, available for download from the SAM website Save the file as SC EX19_9b_FirstLastName_2.xlsx by changing the l' to a "2" If you do not see the risk 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.95 FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell 6 of the Documentation sheet 1 cellB6 does not display your name, delete the file and download a new copy from the SAM witbit 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, dick the File tab, click Options, and then dick the Aas-ins category. In the Manage box select Excel Add-ons and then click Go. In the Add-ins box check the Solver Add-in check box, and then click OK to instalar Solver Add-in solisted in the Add-ins aviable box, click Browse to locate it PROJECT STUPS 1 Takaryashion the board of the Green Lake Sports Camp, a recreational summer came in Syracuse, New Yor. She is to work to analyze the camps Gracias and is for your incorrectang reading problems with the data Go to the Teamworksheet Takarask you to correct the error in the worksheet Correct the terror as to Uthe Trace Precedents arrows to find the source of the VALUE error call Use the Trace Dependent on to determine whether the one in causes or errors in the worst Correct the form which end add nebestrationer procedemente col c. and the multiply the result by the man who camers (oc) R. Crethe22 method to the water in which were we Coretthetic CENGAGE BO 3 909 ODD F4 --- Abbcd & AaBb AoBbCcDc Aabbcode AobCcDc AaBbccde Project Goal SAMBodyc... SAMFigurel Main Project Normal Project Bod Cosman Excel 2019 Module 9: SAM Project lb 4. 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. Takara suspects that the remaining divide by zero errors and the two negative values in the range 16: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 CS: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. identify the invalid data in the worksheet and correct the entry as follows: Circle the Invalid data in the worksheet. b Type 10 as the minimum number of campers for the lacrosse sessions (cellB6). Verify that this change corrected the remaining divide by zero errors and resulted In positive values in the range 16:16 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 amed 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: Use Max Campers as the scenario name Use the enrolled campers per day data (range C969) as the changing cells. Enter cell values for the Max Campers scenario as shown in bold in Table 1, which are the same values as in the range CG Tobiet: Col Valves for the Max Campers Scenario Vare 10 Games Faste campers Le camere Soccer Dames Coll2 10 12 15 Mailings Review ferences View =,=, lul - A- AS. Bod A == ally Cashman Excel 2019 Module 9: SAM Project 1b a. 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 follow Add a scenario to the worksheet using Min Campers as the scenario name. b. Use the enrolled campers per day data (range 09:69) as the changing cells. C. Enter cell values for the Min Campers scenario as shown in bold in Table 2. All Table 2: Cell Values for the Min Campers Scenario Cell Baseball Campers cell 09) Basketball Campers (cell D9) Lacrosse Campers (cell E9) Soccer Campers Volleyball Campers GO Value 8 8 7 8 B 9 Show the Min Campers scenario values in the Private Lessons worksheet. Go to the Revised Fees worksheet. Takra 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 SS or S10 Increase or a $5 decrease to the coaching fees. Compare the average profit per session based on the scenarios as follows: Create a Scenario Summary report using the average profit per session (range C111611) as the result celis 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 Pees worksheet and Create another type of report as follows: Create a Scenario PivoTable report using the average profit per session (range C111011) as the result cells tompare the average profit depending on the fee changes in a Pivottable Uw Revised Fees PivotTables the name of the worlesheet containing the Pivottable Format cell 046 in the Revised Fees Plotte worksheet using the Accounting number format with decimal places nd as the symbol 11. Go to the Games we have wants to determine the number of games the camp can hold on Fridays and Saturdays to meet without interfering rices, which are the forces and Saturdays and use the same + e scenario as shown in bold in Table 1, are the same values as in the range CS:G8. Table 1: Cell Valves for the Max Compers Scenario Cell Baseball Campers (cell 09 Basketball Campers (cell D9) Lacrosse Campers (cell E9) Soccer_Campers cell 09 Volleyball Campers (eel 69) Value 10 12 10 12 15 CENGAGE Shelly Cashman Excel 2019 Module S. SAM Project 1b resources Use Solver to find this information as follows: Use the total weekly profit (ell 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 CS:G6) as the changing variable cells Determine and enter the constraints based on the formacion 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 827 Solve the model, keeping the Solver solution Table 3: Solver Constraints C constraint Each game is scheduled at least once on Friday and CSIGS Lonce on Saturday Each Friday and turdey game an integer CS:GO Each sport is scheduled for a game fome per ekor more Each sport is scheduled for game mes per week C7167 or less The tomber day The number of auctor Turday Game ( The number of CICE PRIREZI The total number of de practice Saturday Practice (2) The follow PERCEISER Q-5 EX19 9 Autoceste AaBb ActCD Atcete ABDCDC Astocco SABdy Alicet SAM SAUR CENGAGE Sholy Cosmon Excel 2010 Module 9 SAM Project resources Use Solver to Find this information as follows: # Use the total weekly profit (ell H17, named Total Weekly Profit) as the objective cell in the Solver model, with the goal of determining the maximum value for that Use the number of Friday and Saturday games for the five sports (range CS:C6) as the changing variable cells. Determine and enter the constraints based on the information provided in Table d. Use Simplex LP as the solving method to find a global optimal solution. e Save the Solver model in cell 827 1. Solve the model, keeping the Solver solution Table 3: Solver Constraints + Constraint Ce or Range Each game is scheduled at least once on Friday and CS:G6 once on Saturday Each day and Saturday game value is an integer CS:G6 Each sport is scheduled for a game 1 time per week or 07:07 more Each sport is scheduled for a game 3 times per week C7:67 or less The total number of Friday games or less Total Friday_Games (HS) The total number of Saturday games is 15 or less Total Saturday Games (H) The total number of games per week 13 Total Weekly_Games (7) The total number of pride practices is 2 or less Friday Practices (121) The total number of Saturday practices is 2 or less Saturday Practices (22) The total number of practices per week 5.5 or less Total Practices (2) 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: Solve the model again, this time choosing to produce an Answer report. D. 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 ext Excel. Follow the directions on the SAM website to submit your completed project