1. DeShawn Washington runs the Florida office of Training, a corporate training firm in Tampa. He is using an Excel workbook to analyze the company's financials and asks for your help in correcting errors and solving problems with the data Go to the Blended Training worksheet. Deshawnakes you to correct the errors in the worksheet. Correct the first error as follows: Use the Trace Precedents arrows to find the source of the #VALUE error in cell F7. bUse the Trace Dependents arons to determine whether the formula in cell 57 causes other errors in the worksheet C Correct the formula in cell F7, which should multiply the Mandatory training fee per person (celi F3) by the minimum number of trainees (cellF5), and then add the online access fee (cell F6) to that result d. 2. 3. C 4. Remove the brace arrows Correct the Name error in cell 21 as follows: a. Use any error-checking method to determine the source of the error in cell 821, which should calculate the average income per session Correct the error by editing the formula in cell 021. Correct the divide-by-zero errors as follows: Evaluate the formula in cell B17 to determine which cell is causing the divide-by- zero error Correct the formula in cell 517, which should divide the income per program (cell 315) by the minimum number of trainees (cellBS) Fill the range C17-F17 with the formula in cel 827. (Hint: You will correct the new error in cell C17 in the following steps.) DeShawn suspects that the two remaining errors are related to the zero value in cells. He wants to make sure that anyone entering the minimum number of trainees enters a number greater than zero. Add data validation to the range 85 F5 as follows: Set a data validation rule for the range 85.75 that allows only whole number values greater than 0. . Add an Input message using Number of Trainees as the Input Message title and the following text as the input message: Enter the minimum number of trainees for this program. Add an Error Alert using the Stop style. Trainees Error as the Error Alert title, and the following text as the Error message: The minimum number of trainees must be greater than 0. Identity the Invalid data in the worksheet and correct the entry as follows: Circle the Invalid data in the worksheet Type 15 as the minimum number of trainees for the Tech Scis program (cell Cs). Verity that this change cleared the remaining errors in the worksheet Go to the Tutorial Fees worksheet. This worksheet analyzes financial data for small group training sessions, which Maxwell Training runs throughout the day, DeShawn has already created a scenario named Current Enrollment that calculates profit based on the Current number of trainees enrolled for each program. He also wants to calculate profit based on the maximum number of trainees Add a new scenario to compare the profit with maximum enrollments as follows: Use Max Attendance as the scenario name Use the enrolled trainees per day data (range 18:28) as the changing cells Enter cell values for the Max Attendance scenario as shown in bold in Table 1 which are the same values as in the range 87:77 5. C 6. Table 1: Coll Values for the Mox Attendance Scenario Cell Orientation Traines Tech Skills Trainees cell 08 Soft Skills Traines Product Trains Mandatory Trains 24 8 32 24 32 7. Deshawn also wants to calculate profit based on the minimum number of trainees. Add another new scenario to compare the profit with low program enramentas follows: Add a scenario to the worksheet using Low Attendance as the scenario name b. Use the enrolled trainees per day data (range 38:Fs) as the changing cells Enter cel values for the Low Attendance scenario as shown in bold in Table 2. Table 2 Col Values for the tow Attendance Scenario GAN Orientation Trainees 20 10 Tech Skis Ties.cl CS 2 Sort Shirnine 28 16 Product Trainees (cel ES 10 15 Show the Low Attendance scenario values in the Tutorial Pees worksheet 9. Go to the New Fees worksheet. DeShawn is considering whether to change the fees for the small group training sessions. He has created three scenarios on the New Fees worksheet showing the profit with a $10 or $15 fe increase or a $5 fee decrease Compare the average profit per program based on the scenarios as follows Create a Scenarie Summary report using the average profit per program (range 310F10) as the result cells to show how the average profit changes depending on the fee changes bi Ose New Fees Scenario Report as the name of the worksheet containing the report 10. Deshawn also wants to focus on one or two types of small group training sessions at time when comparing the average profit per program. Return to the New Fees worksheet and create another type of report as follows: Create a Scenario Pivot Table report using the average profit per program (range 810-F10) as the result cells to compare the average profit depending on the fee changes in a Pivottable Die New Fees Pivot Table as the name of the worksheet containing the Format els 4F in the New Fees Pivot Table worksheet using the Accounting number format with a decimal places and $ as the symbol. 11 Go to the Webinars worksheet. DeShawn wants to determine the number of webinars the company can hold on Tuesdays and Thursdays to make the highest weekly profit without interfering with consultations, which are also scheduled for Tuesdays and Thursdays and the same resources Use Solver to find this information as follows: Use the total weekly profit (G16, named Total Weekly Profit) as the objective cell in the Solver model with the goal of determining the maximum value for that cell Use the number of Tuesday and Thursday Sessions for the five programs (range 34F5) as the changing variable cells c. Determine and enter the constraints based on the information provided in Table . Use Simplex LP as the saving method to find a global optimal solution & Save the Solver model in cel A26. Solve the model, keeping the Solver solution Table 3: Solver Constraints Caller Range 14:15 Constanta Each webinar is scheduled at least once on Tuesday dance on Thursday Each Tuesday and Thursday webinar value is an 841F5 Fach 36:16 86176 type of webinar is scheduled 7 time per week or more Each type of webinar is scheduled 3 times per week Lores The total number of Tuesday webinars is. 5 or less The total number of the websis 15 or less The focal number of webinars per week is 13 The total number of Tuesday Consultations is 2 or 55 The total number of Thursday consultations is 2 or less The total number of consultations per week is sor Total Tuesday Webinars Total Thursday Webinars Total Weekly Webinars Tuesday. Consultations Thursday. Consultations Total_Consultations The total number of consultations per week is 5 or Total_Consultations 12. Deshawn 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 Use Webinar 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: Blended Training Worksheet Maxwell Training M Geus loreme 10T Il Manam 11001 11 13 16 15 14 tome stats 15 16 Perm 11 Post 16 10 Average Income 30 1 LO 10 Anal Figure 2: Tutorial Foes Workshoat Maxwell Training HO Culowe TIME 201310 TY Meer M. . 10 1 10 AW II Apa III Find Figure 3: New Fees Scenario Report Worksheet enerima HEBAT 1 135 150 10 11 11 11 14 Challe Ostali New Fee Tech Stille News 135 13 New Fee 100 110 Pret New Fee Mandatory New Ye 10 Colle STO 30 3310 3 350 D510 1.101 3310 17310 2001 Notes Cure Values technines tighligital . 33 635 305 20 IND Final Figure 4: New Fees PivotTable Worksheet A B D E F 1 $B$9:$F39 by (All) 2 3 Row Labels - $B$10 $C$10 $D$10 SE$10 SF$10 4 $10 Increase 850 $ 295 2.105 $ 3 760 5 $15 Increase $ 925 $ 325 2.465 $ 1.1955 940 6 $6 Decrease $ 625 $ 2058 1,745 $ 705 % 580 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 New Fees Scenario Report New Fees Pivot Table New Fees WE Final Figure 5: New Fees Worksheet Maxwell Training Orientation Tech Skills Soft Skills Produet Mandatory 3 Tricot per 3603 03 480 9 375 400 4 Materialet 653 35 55 1203 100 5 Total cost $ 5155 500 5 7 Maximum Trines day 24 22 24 32 8 Enrolled traines per day 15 34 16 12 Maxwell Training Orientation Tecla Skill Soft Skills Pro Manila 3 Tier cost per day 360 SOS 1903 375 00 4 Materiale 35 553 1203 300 Totalcoat $ 3155495 S so 7 Maximtraines per day 8 23 34 32 8 Enrolled traines per day 15 6 36 9 Training 153 1553 1103 995 105 10 Average profite program 303 395 2.105 $ 95 960 11 Average profite trainees 56.67 $12.17 81.71 $59.06 $ 12 13 14 16 16 Final Figure 6: Webinar Answer Report Worksheet Microsoft Excel 16.0 Answer le pert Worksheet: 8C EXIS J. Firstam Webinars Report Created: 1921/2015 7:06:09 PM RealSolver found a solution. All Central and optimaliserested Solver Engine 6. Biel LP 1 Some 038 3 x Selver Oy 10 Med Use Mes Sulgelesulted Meslutego Stalintulutage Name Original Veil Value 110111 14 Cell Me Cell 1610114 17 19 Variable Celle 30 Cell 21 22 1014 1034 34 1654 25 1714 Original Value Pal Value Integer I Nam Option TechWeb So The W. TW. Mad Wear On The Ware TechThWetan ShadesWe Frode The Wars Mandy Theday Wears 11 1 1 1 I 33 28 1035 SESS 30 1993 31 32 33 Centre 34 Cell 3 101 CM 37 30 SEN Cell Valve Formula Stark 01 Nam Wasky Own Wor Webby Tee W. Werk Wear Wer Predare WebMaster Web We One W. Wewe War WW Workert, We Werky MW. Th. Tural Coas Total Thundes Was Total War SON SCH HD SEN 3750 3931 30132 3095 9094 SON 3 > 910 10 din Swing Anda Swat Net 104 410 st 3055 44 15 1. DeShawn Washington runs the Florida office of Training, a corporate training firm in Tampa. He is using an Excel workbook to analyze the company's financials and asks for your help in correcting errors and solving problems with the data Go to the Blended Training worksheet. Deshawnakes you to correct the errors in the worksheet. Correct the first error as follows: Use the Trace Precedents arrows to find the source of the #VALUE error in cell F7. bUse the Trace Dependents arons to determine whether the formula in cell 57 causes other errors in the worksheet C Correct the formula in cell F7, which should multiply the Mandatory training fee per person (celi F3) by the minimum number of trainees (cellF5), and then add the online access fee (cell F6) to that result d. 2. 3. C 4. Remove the brace arrows Correct the Name error in cell 21 as follows: a. Use any error-checking method to determine the source of the error in cell 821, which should calculate the average income per session Correct the error by editing the formula in cell 021. Correct the divide-by-zero errors as follows: Evaluate the formula in cell B17 to determine which cell is causing the divide-by- zero error Correct the formula in cell 517, which should divide the income per program (cell 315) by the minimum number of trainees (cellBS) Fill the range C17-F17 with the formula in cel 827. (Hint: You will correct the new error in cell C17 in the following steps.) DeShawn suspects that the two remaining errors are related to the zero value in cells. He wants to make sure that anyone entering the minimum number of trainees enters a number greater than zero. Add data validation to the range 85 F5 as follows: Set a data validation rule for the range 85.75 that allows only whole number values greater than 0. . Add an Input message using Number of Trainees as the Input Message title and the following text as the input message: Enter the minimum number of trainees for this program. Add an Error Alert using the Stop style. Trainees Error as the Error Alert title, and the following text as the Error message: The minimum number of trainees must be greater than 0. Identity the Invalid data in the worksheet and correct the entry as follows: Circle the Invalid data in the worksheet Type 15 as the minimum number of trainees for the Tech Scis program (cell Cs). Verity that this change cleared the remaining errors in the worksheet Go to the Tutorial Fees worksheet. This worksheet analyzes financial data for small group training sessions, which Maxwell Training runs throughout the day, DeShawn has already created a scenario named Current Enrollment that calculates profit based on the Current number of trainees enrolled for each program. He also wants to calculate profit based on the maximum number of trainees Add a new scenario to compare the profit with maximum enrollments as follows: Use Max Attendance as the scenario name Use the enrolled trainees per day data (range 18:28) as the changing cells Enter cell values for the Max Attendance scenario as shown in bold in Table 1 which are the same values as in the range 87:77 5. C 6. Table 1: Coll Values for the Mox Attendance Scenario Cell Orientation Traines Tech Skills Trainees cell 08 Soft Skills Traines Product Trains Mandatory Trains 24 8 32 24 32 7. Deshawn also wants to calculate profit based on the minimum number of trainees. Add another new scenario to compare the profit with low program enramentas follows: Add a scenario to the worksheet using Low Attendance as the scenario name b. Use the enrolled trainees per day data (range 38:Fs) as the changing cells Enter cel values for the Low Attendance scenario as shown in bold in Table 2. Table 2 Col Values for the tow Attendance Scenario GAN Orientation Trainees 20 10 Tech Skis Ties.cl CS 2 Sort Shirnine 28 16 Product Trainees (cel ES 10 15 Show the Low Attendance scenario values in the Tutorial Pees worksheet 9. Go to the New Fees worksheet. DeShawn is considering whether to change the fees for the small group training sessions. He has created three scenarios on the New Fees worksheet showing the profit with a $10 or $15 fe increase or a $5 fee decrease Compare the average profit per program based on the scenarios as follows Create a Scenarie Summary report using the average profit per program (range 310F10) as the result cells to show how the average profit changes depending on the fee changes bi Ose New Fees Scenario Report as the name of the worksheet containing the report 10. Deshawn also wants to focus on one or two types of small group training sessions at time when comparing the average profit per program. Return to the New Fees worksheet and create another type of report as follows: Create a Scenario Pivot Table report using the average profit per program (range 810-F10) as the result cells to compare the average profit depending on the fee changes in a Pivottable Die New Fees Pivot Table as the name of the worksheet containing the Format els 4F in the New Fees Pivot Table worksheet using the Accounting number format with a decimal places and $ as the symbol. 11 Go to the Webinars worksheet. DeShawn wants to determine the number of webinars the company can hold on Tuesdays and Thursdays to make the highest weekly profit without interfering with consultations, which are also scheduled for Tuesdays and Thursdays and the same resources Use Solver to find this information as follows: Use the total weekly profit (G16, named Total Weekly Profit) as the objective cell in the Solver model with the goal of determining the maximum value for that cell Use the number of Tuesday and Thursday Sessions for the five programs (range 34F5) as the changing variable cells c. Determine and enter the constraints based on the information provided in Table . Use Simplex LP as the saving method to find a global optimal solution & Save the Solver model in cel A26. Solve the model, keeping the Solver solution Table 3: Solver Constraints Caller Range 14:15 Constanta Each webinar is scheduled at least once on Tuesday dance on Thursday Each Tuesday and Thursday webinar value is an 841F5 Fach 36:16 86176 type of webinar is scheduled 7 time per week or more Each type of webinar is scheduled 3 times per week Lores The total number of Tuesday webinars is. 5 or less The total number of the websis 15 or less The focal number of webinars per week is 13 The total number of Tuesday Consultations is 2 or 55 The total number of Thursday consultations is 2 or less The total number of consultations per week is sor Total Tuesday Webinars Total Thursday Webinars Total Weekly Webinars Tuesday. Consultations Thursday. Consultations Total_Consultations The total number of consultations per week is 5 or Total_Consultations 12. Deshawn 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 Use Webinar 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: Blended Training Worksheet Maxwell Training M Geus loreme 10T Il Manam 11001 11 13 16 15 14 tome stats 15 16 Perm 11 Post 16 10 Average Income 30 1 LO 10 Anal Figure 2: Tutorial Foes Workshoat Maxwell Training HO Culowe TIME 201310 TY Meer M. . 10 1 10 AW II Apa III Find Figure 3: New Fees Scenario Report Worksheet enerima HEBAT 1 135 150 10 11 11 11 14 Challe Ostali New Fee Tech Stille News 135 13 New Fee 100 110 Pret New Fee Mandatory New Ye 10 Colle STO 30 3310 3 350 D510 1.101 3310 17310 2001 Notes Cure Values technines tighligital . 33 635 305 20 IND Final Figure 4: New Fees PivotTable Worksheet A B D E F 1 $B$9:$F39 by (All) 2 3 Row Labels - $B$10 $C$10 $D$10 SE$10 SF$10 4 $10 Increase 850 $ 295 2.105 $ 3 760 5 $15 Increase $ 925 $ 325 2.465 $ 1.1955 940 6 $6 Decrease $ 625 $ 2058 1,745 $ 705 % 580 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 New Fees Scenario Report New Fees Pivot Table New Fees WE Final Figure 5: New Fees Worksheet Maxwell Training Orientation Tech Skills Soft Skills Produet Mandatory 3 Tricot per 3603 03 480 9 375 400 4 Materialet 653 35 55 1203 100 5 Total cost $ 5155 500 5 7 Maximum Trines day 24 22 24 32 8 Enrolled traines per day 15 34 16 12 Maxwell Training Orientation Tecla Skill Soft Skills Pro Manila 3 Tier cost per day 360 SOS 1903 375 00 4 Materiale 35 553 1203 300 Totalcoat $ 3155495 S so 7 Maximtraines per day 8 23 34 32 8 Enrolled traines per day 15 6 36 9 Training 153 1553 1103 995 105 10 Average profite program 303 395 2.105 $ 95 960 11 Average profite trainees 56.67 $12.17 81.71 $59.06 $ 12 13 14 16 16 Final Figure 6: Webinar Answer Report Worksheet Microsoft Excel 16.0 Answer le pert Worksheet: 8C EXIS J. Firstam Webinars Report Created: 1921/2015 7:06:09 PM RealSolver found a solution. All Central and optimaliserested Solver Engine 6. Biel LP 1 Some 038 3 x Selver Oy 10 Med Use Mes Sulgelesulted Meslutego Stalintulutage Name Original Veil Value 110111 14 Cell Me Cell 1610114 17 19 Variable Celle 30 Cell 21 22 1014 1034 34 1654 25 1714 Original Value Pal Value Integer I Nam Option TechWeb So The W. TW. Mad Wear On The Ware TechThWetan ShadesWe Frode The Wars Mandy Theday Wears 11 1 1 1 I 33 28 1035 SESS 30 1993 31 32 33 Centre 34 Cell 3 101 CM 37 30 SEN Cell Valve Formula Stark 01 Nam Wasky Own Wor Webby Tee W. Werk Wear Wer Predare WebMaster Web We One W. Wewe War WW Workert, We Werky MW. Th. Tural Coas Total Thundes Was Total War SON SCH HD SEN 3750 3931 30132 3095 9094 SON 3 > 910 10 din Swing Anda Swat Net 104 410 st 3055 44 15