Maxwell Coaching
FORMULA AUDITING, DATA VALIDATION, AND COMPLEX PROBLEM SOLVING
GETTING STARTED
• Open the file SC_EX19_9a_FirstLastName_1.xlsx, out there for obtain from the SAM web site.
• Save the file as SC_EX19_9a_FirstLastName_2.xlsx by altering the “1” to a “2”.
o If you don’t see the .xlsx file extension within the Save As dialog field, don’t sort it. This system will add the file extension for you routinely.
• With the file SC_EX19_9a_FirstLastName_2.xlsx nonetheless open, make sure that your first and final title is displayed in cell B6 of the Documentation sheet.
o If cell B6 doesn’t show your title, delete the file and obtain a brand new copy from the SAM web site.
• To finish this venture, you have to add the Solver Add-in. If Solver isn’t listed beneath the Assessment part of the Knowledge ribbon, click on the File tab, click on Choices, after which click on the Add-Ins class. Within the Handle field, choose Excel Add-ins after which click on Go. Within the Add-Ins field, test the Solver Add-in test field, after which click on OK to put in. If Solver Add-in isn’t listed within the Add-Ins out there field, click on Browse to find it.
PROJECT STEPS
1. DeShawn Washington runs the Florida workplace of Maxwell Coaching, a company coaching agency in Tampa. He’s utilizing an Excel workbook to investigate the corporate’s financials and asks on your Help in correcting errors and fixing issues with the information.
Go to the Blended Coaching worksheet. DeShawn asks you to appropriate the errors within the worksheet. Appropriate the primary error as follows:
a. Use the Hint Precedents arrows to seek out the supply of the #VALUE! error in cell F7.
b. Use the Hint Dependents arrows to find out whether or not the formulation in cell F7 causes different errors within the worksheet.
c. Appropriate the formulation in cell F7, which ought to multiply the Necessary coaching payment per particular person (cell F3) by the minimal variety of trainees (cell F5), after which add the net entry payment (cell F6) to that consequence.
d. Take away the hint arrows.
2. Appropriate the Identify error in cell B21 as follows:
a. Use any error-checking methodology to find out the supply of the error in cell B21, which ought to calculate the common earnings per session.
b. Appropriate the error by modifying the formulation in cell B21.
three. Appropriate the divide-by-zero errors as follows:
a. Consider the formulation in cell B17 to find out which cell is inflicting the divide-by-zero error.
b. Appropriate the formulation in cell B17, which ought to divide the earnings per program (cell B15) by the minimal variety of trainees (cell B5).
c. Fill the vary C17:F17 with the formulation in cell B17. (Trace: You’ll appropriate the brand new error in cell C17 within the following steps.)
four. DeShawn suspects that the 2 remaining errors are associated to the zero worth in cell C5. He desires to ensure that anybody coming into the minimal variety of trainees enters a quantity larger than zero.
Add information validation to the vary B5:F5 as follows:
a. Set an information validation rule for the vary B5:F5 that enables solely entire quantity values larger than zero.
b. Add an Enter Message utilizing Variety of Trainees because the Enter Message title and the next textual content because the Enter message:
Enter the minimal variety of trainees for this program.
c. Add an Error Alert utilizing the Cease fashion, Trainees Error because the Error Alert title, and the next textual content because the Error message:
The minimal variety of trainees have to be larger than zero.
5. Establish the invalid information within the worksheet and proper the entry as follows:
a. Circle the invalid information within the worksheet.
b. Kind 15 because the minimal variety of trainees for the Tech Expertise program (cell C5).
c. Confirm that this variation cleared the remaining errors within the worksheet.
6. Go to the Tutorial Charges worksheet. This worksheet analyzes monetary information for small-group coaching periods, which Maxwell Coaching runs all through the day. DeShawn has already created a situation named Present Enrollment that calculates revenue primarily based on the present variety of trainees enrolled for every program. He additionally desires to calculate revenue primarily based on the utmost variety of trainees.
Add a brand new situation to check the revenue with most enrollments as follows:
a. Use Max Attendance because the situation title.
b. Use the enrolled trainees per day information (vary B8:F8) because the altering cells.
c. Enter cell values for the Max Attendance situation as proven in daring in Desk 1, that are the identical values as within the vary B7:F7.
Desk 1: Cell Values for the Max Attendance Situation
Cell Worth
Orientation_Trainees (cell B8) 24
Tech_Skills_Trainees (cell C8) eight
Soft_Skills_Trainees (cell D8) 32
Product_Trainees (cell E8) 24
Mandatory_Trainees (cell F8) 32
7. DeShawn additionally desires to calculate revenue primarily based on the minimal variety of trainees.
Add one other new situation to check the revenue with low program enrollment as follows:
a. Add a situation to the worksheet utilizing Low Attendance because the situation title.
b. Use the enrolled trainees per day information (vary B8:F8) because the altering cells.
c. Enter cell values for the Low Attendance situation as proven in daring in Desk 2.
Desk 2: Cell Values for the Low Attendance Situation
Cell Worth
Orientation_Trainees (cell B8) 10
Tech_Skills_Trainees (cell C8) 2
Soft_Skills_Trainees (cell D8) 16
Product_Trainees (cell E8) 10
Mandatory_Trainees (cell F8) 15
eight. Present the Low Attendance situation values within the Tutorial Charges worksheet.
9. Go to the New Charges worksheet. DeShawn is contemplating whether or not to vary the charges for the small-group coaching periods. He has created three eventualities on the New Charges worksheet displaying the revenue with a $10 or $15 payment improve or a $5 payment lower.
Examine the common revenue per program primarily based on the eventualities as follows:
a. Create a Situation Abstract report utilizing the common revenue per program (vary B10:F10) because the consequence cells to point out how the common revenue adjustments relying on the payment adjustments.
b. Use New Charges Situation Report because the title of the worksheet containing the report.
10. DeShawn additionally desires to give attention to one or two kinds of small-group coaching periods at a time when evaluating the common revenue per program. Return to the New Charges worksheet and create one other sort of report as follows:
a. Create a Situation PivotTable report utilizing the common revenue per program (vary B10:F10) because the consequence cells to check the common revenue relying on the payment adjustments in a PivotTable.
b. Use New Charges PivotTable because the title of the worksheet containing the PivotTable.
c. Format cells B4:F6 within the New Charges PivotTable worksheet utilizing the Accounting quantity format with zero decimal locations and $ because the image.
11. Go to the Webinars worksheet. DeShawn desires to find out the variety of webinars the corporate can maintain on Tuesdays and Thursdays to make the best weekly revenue with out interfering with consultations, that are additionally scheduled for Tuesdays and Thursdays and use the identical sources.
Use Solver to seek out this info as follows:
a. Use the entire weekly revenue (cell G16, named Total_Weekly_Profit) as the target cell within the Solver mannequin, with the objective of figuring out the utmost worth for that cell.
b. Use the variety of Tuesday and Thursday periods for the 5 applications (vary B4:F5) because the altering variable cells.
c. Decide and enter the constraints primarily based on the knowledge supplied in Desk three.
d. Use Simplex LP because the fixing methodology to discover a world optimum answer.
e. Save the Solver mannequin in cell A26.
f. Resolve the mannequin, holding the Solver answer.
Desk three: Solver Constraints
Constraint Cell or Vary
Every webinar is scheduled not less than as soon as on Tuesday and as soon as on Thursday B4:F5
Every Tuesday and Thursday webinar worth is an integer B4:F5
Every sort of webinar is scheduled 1 time per week or extra B6:F6
Every sort of webinar is scheduled three instances per week or much less B6:F6
The full variety of Tuesday webinars is 5 or much less Total_Tuesday_Webinars
The full variety of Thursday webinars is 15 or much less Total_Thursday_Webinars
The full variety of webinars per week is 13 Total_Weekly_Webinars
The full variety of Tuesday consultations is 2 or much less Tuesday_Consultations
The full variety of Thursday consultations is 2 or much less Thursday_Consultations
The full variety of consultations per week is 5 or much less Total_Consultations
12. DeShawn desires to doc the reply Solver discovered, together with the constraints and an inventory of the values Solver modified to resolve the issue. Produce an Reply report for the Solver mannequin as follows:
a. Resolve the mannequin once more, this time selecting to supply an Reply report.
b. Use Webinar Reply Report because the title of the worksheet containing the Reply report.
Your workbook ought to appear like the Ultimate Figures on the next pages. Save your adjustments, shut the workbook, after which exit Excel. Observe the instructions on the SAM web site to submit your accomplished venture.
Ultimate Determine 1: Blended Coaching Worksheet
Ultimate Determine 2: Tutorial Charges Worksheet
Ultimate Determine three: New Charges Situation Report Worksheet
Ultimate Determine four: New Charges PivotTable Worksheet
Ultimate Determine 5: New Charges Worksheet
Ultimate Determine 6: Webinar Reply Report Worksheet
Ultimate Determine 7: Webinar Worksheet
——–
Maxwell Coaching FORMULA AUDITING, DATA VALIDATION, AND COMPLEX PROBLEM SOLVING STEP ONE: GETTING STARTED
• Open the file SC_EX19_9a_FirstLastName_1.xlsx, out there for obtain from the SAM web site.
• Save the file as SC_EX19_9a_FirstLastName_2.xlsx by altering the “1” to a “2”.
o If you don’t see the .xlsx file extension within the Save As dialog field, don’t sort it. This system will add the file extension for you routinely.
• With the file SC_EX19_9a_FirstLastName_2.xlsx nonetheless open, make sure that your first and final title is displayed in cell B6 of the Documentation sheet.
o If cell B6 doesn’t show your title, delete the file and obtain a brand new copy from the SAM web site.
•
To finish this venture, you have to add the Solver Add-in. If Solver isn’t listed beneath
