SC_EX19_9a_RileyKneisl_Report_1

Shelly Cashman Excel 2019 | Module 9: SAM Project 1a Riley Kneisl GE ver. 17.1.0-rc0000 1. 2/2 Use Trace Precedents and Trace Dependents to correct formula errors. 2. 2/2 Evaluate and correct a formula. 3. 2/2 Evaluate and correct a formula. Copy a formula into a range. 4. 2/2 Add a data validation rule to a range. Add an input message to a data validation rule. Add an error alert to a data validation rule. 5. 2/2 Update a value in a cell. Verify values in multiple cells. 6. 2/2 Create a scenario. Set the input cell values for a scenario. Enter Cell values. 7. 3/3 Create a scenario. Set the input cell values for a scenario. Enter Cell values. 8. 3/3 View the worksheet using specific scenario values. 9. 3/3 Create a scenario report. 10. 2/3 Create a Scenario PivotTable Report. Modify the number format of a PivotTable field. 11. 3/3 Define the objective cell in a Solver model. Define the changing cells in a Solver model. Define the constraints in a Solver model. Define the solving method in a Solver model. Save a Solver model to a worksheet. 12. 3/3 Save a Solver report to a worksheet. SUBMISSION #1 | SCORE IS: 29 OUT OF 30 DeShawn Washington runs the Florida office of Maxwell 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. DeShawn 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 F7. b. Use the Trace Dependents arrows to determine whether the formula in cell F7 causes other errors in the worksheet. c. Correct the formula in cell F7, which should multiply the Mandatory training fee per person (cell F3 ) by the minimum number of trainees (cell F5 ), and then add the online access fee (cell F6 ) to that result. d. Remove the trace arrows. Correct the Name error in cell B21 as follows: a. Use any error-checking method to determine the source of the error in cell B21, which should calculate the average income per session. b. Correct the error by editing the formula in cell B21. Correct the divide-by-zero errors as follows: a. Evaluate the formula in cell B17 to determine which cell is causing the divide-by-zero error. b. Correct the formula in cell B17, which should divide the income per program (cell B15 ) by the minimum number of trainees (cell B5 ). c. Fill the range C17:F17 with the formula in cell B17. ( 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 cell C5. 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 B5:F5 as follows: a. Set a data validation rule for the range B5:F5 that allows only whole number values greater than 0 . b. 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. c. 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. Identify the invalid data in the worksheet and correct the entry as follows: a. Circle the invalid data in the worksheet. b. Type 15 as the minimum number of trainees for the Tech Skills program (cell C5). c. Verify 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: a. Use Max Attendance as the scenario name. b. Use the enrolled trainees per day data (range B8:F8 ) as the changing cells. c. Enter cell values for the Max Attendance scenario as shown in bold in Table 1, which are the same values as in the range B7:F7. DeShawn also wants to calculate profit based on the minimum number of trainees. Add another new scenario to compare the profit with low program enrollment as follows: a. Add a scenario to the worksheet using Low Attendance as the scenario name. b. Use the enrolled trainees per day data (range B8:F8 ) as the changing cells. c. Enter cell values for the Low Attendance scenario as shown in bold in Table 2. Show the Low Attendance scenario values in the Tutorial Fees worksheet. 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 fee increase or a $5 fee decrease. Compare the average profit per program based on the scenarios as follows: a. Create a Scenario Summary report using the average profit per program (range B10:F10 ) as the result cells to show how the average profit changes depending on the fee changes. b. Use New Fees Scenario Report as the name of the worksheet containing the report. DeShawn also wants to focus on one or two types of small-group training sessions at a time when comparing the average profit per program. Return to the New Fees worksheet and create another type of report as follows: a. Create a Scenario PivotTable report using the average profit per program (range B10:F10 ) as the result cells to compare the average profit depending on the fee changes in a PivotTable. b. Use New Fees PivotTable as the name of the worksheet containing the PivotTable. c. Format cells B4:F6 in the New Fees PivotTable worksheet using the Accounting number format with 0 decimal places and $ as the symbol. In the New Fees PivotTable worksheet, the numeric values in the PivotTable should be formatted using the Accounting number format with 0 decimal places and the $ symbol. 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 use the same resources. Use Solver to find this information as follows: a. Use the total weekly profit (cell G16 , 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 Tuesday and Thursday sessions for the five programs (range B4:F5 ) 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 A26 . f. Solve the model, keeping the Solver solution. 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: a. Solve the model again, this time choosing to produce an Answer report. b. Use Webinar Answer Report as the name of the worksheet containing the Answer report.

Author: Note: Do

AM

Maxwell Training Orientation Tech Skills Soft Skills Product Mandatory Training fee per person $ 150 $ 495 $ 329 $ 229 $ 495 Sessions per program 2 12 10 6 4 Minimum trainees 10 15 15 20 10 Online access fee $ 500 $ 1,200 $ 750 $ 500 $ 750 Gross Income $ 2,000 $ 8,625 $ 5,685 $ 5,080 5,700 Number of trainers 2 5 3 3 3 Trainer cost $ 525 $ 1,100 $ 1,000 $ 785 $ 1,100 Materials cost $ 470 $ 1,400 $ 650 $ 750 $ 1,200 Expenses $ 1,520 $ 6,900 $ 3,650 $ 3,105 $ 4,500 Income stats Per program $ 480 $ 1,725 $ 2,035 $ 1,975 $ 1,200 Per session $ 240 $ 144 $ 204 $ 329 $ 300 Per trainee $ 48 $ 115 $ 136 $ 99 $ 120 Average income Per program $ 1,483.00 Per session $ 243.28 Per trainee $ 103.48

Scenario Summary Current Values: $10 Increase $15 Increase Changing Cells: Orientation_New_Fee $ 75 $ 85 $ 90 Tech_Skills_New_Fee $ 125 $ 135 $ 140 Soft_Skills_New_Fee $ 100 $ 110 $ 125 Product_New_Fee $ 80 $ 90 $ 105 Mandatory_New_Fee $ 95 $ 105 $ 120 Result Cells: $B$10 $ 700 $ 850 $ 925 $C$10 $ 235 $ 295 $ 325 $D$10 $ 1,865 $ 2,105 $ 2,465 $E$10 $ 785 $ 945 $ 1,185 $F$10 $ 640 $ 760 $ 940 Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created. Changing cells for each scenario are highlighted in gray.

$5 Decrease $ 70 $ 120 $ 95 $ 75 $ 90 $ 625 $ 205 $ 1,745 $ 705 $ 580

Maxwell Training Orientation Tech Skills Soft Skills Product Mandatory Trainer cost per day $ 360 $ 480 $ 480 $ 375 $ 400 Materials cost $ 65 $ 35 $ 55 $ 120 $ 100 Total cost $ 425 $ 515 $ 535 $ 495 $ 500 Maximum trainees per day 24 8 32 24 32 Enrolled trainees per day 15 6 24 16 12 Training fee per person $ 85 $ 135 $ 110 $ 90 $ 105 Average profit per program $ 850 $ 295 $ 2,105 $ 945 $ 760 Average profit per trainee $ 56.67 $ 49.17 $ 87.71 $ 59.06 $ 63.33

Microsoft Excel 16.0 Answer Report Worksheet: [SC_EX19_9a_RileyKneisl_1.xlsx]Webinars Report Created: 11/22/2023 4:28:04 PM Result: Solver found a solution. All Constraints and optimality conditions are sa Solver Engine Solver Options Objective Cell (Max) Cell Name Original Value Final Value $G$16 Total_Weekly_Profit $ 1,105.00 $ 1,105.00 Variable Cells Cell Name Original Value Final Value $B$4:$F$5 Constraints Cell Name Cell Value Formula $B$6:$F$6 <= 3 $B$6:$F$6 >= 1 $D$21 Thursday_Consultations 2 $D$21<=2 $D$22 Total_Consultations 4 $D$22<=5 $G$5 Total_Thursday_Webinars 8 $G$5<=15 $G$4 Total_Tuesday_Webinars 5 $G$4<=5 $G$6 Total_Weekly_Webinars 13 $G$6=13 $D$20 Tuesday_Consultations 2 $D$20<=2 $B$4:$F$5 >= 1 $B$4:$F$5=Integer

Maxwell Training Webinar Schedule Orientation Tech Skills Soft Skills Product Tuesday webinars 1 1 1 1 Thursday webinars 1 2 1 2 Webinars per week 2 3 2 3 Webinars - Profit Analysis Orientation Tech Skills Soft Skills Product Profit per Tuesday webinar $ 30.00 $ 55.00 $ 40.00 $ 30.00 Subtotal profit $ 60.00 $ 165.00 $ 80.00 $ 90.00 Profit per Thursday webinar $ 35.00 $ 55.00 $ 50.00 $ 25.00 Subtotal profit $ 70.00 $ 165.00 $ 100.00 $ 75.00 Total weekly profit $ 130.00 $ 330.00 $ 180.00 $ 165.00 Consultations Tech Management Total Tuesday consultations 1 1 2 Thursday consultations 1 1 2 Consultations per week 2 2 4 Maximum weekly profit model $ 1,105.00 10 1 1 1 1 1 1 1 1 1 1 32767 0

Mandatory Total 1 5 2 8 3 13 Mandatory Total $ 45.00 200.00 $ 135.00 530.00 $ 55.00 220.00 $ 165.00 575.00 $ 300.00 $ 1,105.00