Homework answers / question archive /  Shelly Cashman Excel 2019 | Module 9: SAM Critical Thinking Project 1c Maxwell Training FORMULA AUDITING, DATA VALIDATION, AND COMPLEX PROBLEM SOLVING   GETTING STARTED Open the file SC_EX19_CT9c_FirstLastName_1

 Shelly Cashman Excel 2019 | Module 9: SAM Critical Thinking Project 1c Maxwell Training FORMULA AUDITING, DATA VALIDATION, AND COMPLEX PROBLEM SOLVING   GETTING STARTED Open the file SC_EX19_CT9c_FirstLastName_1

MS Excel

Share With

---

 Shelly Cashman Excel 2019 | Module 9: SAM Critical Thinking Project 1c

Maxwell Training

FORMULA AUDITING, DATA VALIDATION, AND COMPLEX PROBLEM SOLVING

 

• GETTING STARTED

• Open the file SC_EX19_CT9c_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as SC_EX19_CT9c_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_CT9c_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. 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:
   1. Use the Trace Precedents arrows to find the source of the error in the Mandatory Gross Income cell, and then use the Trace Dependents arrows to determine whether the Mandatory Gross Income formula causes other errors in the worksheet.
   2. Correct the Mandatory Gross Income formula, which should multiply the mandatory training fee per person by the minimum number of trainees, and then add the online access fee to that result.
   3. Remove the trace arrows.
2. Use any error-checking method to determine the source of the Name error in the Average income section, which should calculate the average income per session. Edit the formula to correct the error.
3. Correct the divide-by-zero errors as follows:
   1. For the orientation income per trainee, evaluate the formula to determine which cell is causing the divide-by-zero error.
   2. Correct the formula, which should divide the income per program by the minimum number of trainees.
   3. Fill the rest of the row with the new formula. (Hint: You will correct the new error in cell C17 in the following steps.)
4. DeShawn suspects that the two remaining errors are related to the zero value for minimum trainees. He wants to make sure that anyone entering the minimum number of trainees enters a number greater than zero.
   
   Add data validation to the minimum trainees values as follows:
   1. Set a data validation rule that allows only whole numbers greater than 0.
   2. Add an Input Message using Number of Trainees as the message title and the following text as the message: Enter the minimum number of trainees for this program.
   3. Add a Stop Error Alert using Trainees Error as the alert title and the following text as the error message: The minimum number of trainees must be greater than 0.
5. Circle the invalid data in the worksheet. Change the minimum for the Tech Skills program to 15 trainees. Verify that this change cleared the remaining errors in the worksheet.
6. 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 named Max Attendance using the Enrolled trainees per day data as the changing cells. Enter cell values as shown in Table 1, which are the same values as those in the Maximum trainees per day row.

 

Cell	Value
Orientation_Trainees	24
Tech_Skills_Trainees	8
Soft_Skills_Trainees	32
Product_Trainees	24
Mandatory_Trainees	32

 

7. DeShawn also wants to calculate profit based on the minimum number of trainees. Add a new scenario named Low Attendance using the Enrolled trainees per day data as the changing cells. Enter cell values as shown in Table 2.

 

Cell	Value
Orientation_Trainees	10
Tech_Skills_Trainees	2
Soft_Skills_Trainees	16
Product_Trainees	10
Mandatory_Trainees	15

 

8. Show the Low Attendance scenario values in the Tutorial Fees 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 fee increase or a $5 fee decrease.
   
   Create a Scenario Summary report using the Average profit per program row as the result cells to show how the average profit changes depending on how the fee changes. Use 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 a time when comparing the average profit per program. Return to the New Fees worksheet and create another type of report as follows:
    1. Create a Scenario PivotTable report using the Average profit per program row as the result cells to compare the average profit depending on the fee changes in a PivotTable.
    2. Use New Fees PivotTable as the name of the worksheet containing the PivotTable.
    3. Display the values in the New Fees PivotTable worksheet using the Accounting number format with zero decimal places and 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 use the same resources.
    
    Use Solver to find this information as follows:
    1. Use Total weekly profit as the objective cell in the Solver model, with the goal of determining the maximum value for that cell.
    2. Use the number of Tuesday and Thursday sessions for the five programs as the changing variable cells.
    3. Determine and enter the constraints based on the information provided in Table 3.
    4. Use Simplex LP as the solving method to find a global optimal solution.
    5. Save the Solver model below the Maximum weekly profit model label.
    6. Solve the model, keeping the Solver solution.

 

Constraint	Cell or Range
Each webinar is scheduled at least once on Tuesday and once on Thursday	B4:F5
Each Tuesday and Thursday webinar value is an integer	B4:F5
Each type of webinar is scheduled 1 time per week or more	B6:F6
Each type of webinar is scheduled 3 times per week or less	B6:F6
The total number of Tuesday webinars is 5 or less	Total_Tuesday_Webinars
The total number of Thursday webinars is 15 or less	Total_Thursday_Webinars
The total number of webinars per week is 13	Total_Weekly_Webinars
The total number of Tuesday consultations is 2 or less	Tuesday_Consultations
The total number of Thursday consultations is 2 or less	Thursday_Consultations
The total number of consultations per week is 5 or less	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.
    
    Solve the model again and produce an Answer report, using Webinar Answer Report as the name of the worksheet containing the 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.