Maxwell Coaching
\nFORMULA AUDITING, DATA VALIDATION, AND COMPLEX PROBLEM SOLVING<\/p>\n
GETTING STARTED
\n\u2022\tOpen the file SC_EX19_9a_FirstLastName_1.xlsx, out there for obtain from the SAM web site.
\n\u2022\tSave the file as SC_EX19_9a_FirstLastName_2.xlsx by altering the \u201c1\u201d to a \u201c2\u201d.
\no\tIf 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.
\n\u2022\tWith 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.
\no\tIf cell B6 doesn’t show your title, delete the file and obtain a brand new copy from the SAM web site.
\n\u2022\tTo 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.
\nPROJECT STEPS
\n1.\tDeShawn 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.<\/p>\n
Go to the Blended Coaching worksheet. DeShawn asks you to appropriate the errors within the worksheet. Appropriate the primary error as follows:
\na.\tUse the Hint Precedents arrows to seek out the supply of the #VALUE! error in cell F7.
\nb.\tUse the Hint Dependents arrows to find out whether or not the formulation in cell F7 causes different errors within the worksheet.
\nc.\tAppropriate 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.
\nd.\tTake away the hint arrows.
\n2.\tAppropriate the Identify error in cell B21 as follows:
\na.\tUse any error-checking methodology to find out the supply of the error in cell B21, which ought to calculate the common earnings per session.
\nb.\tAppropriate the error by modifying the formulation in cell B21.
\nthree.\tAppropriate the divide-by-zero errors as follows:
\na.\tConsider the formulation in cell B17 to find out which cell is inflicting the divide-by-zero error.
\nb.\tAppropriate the formulation in cell B17, which ought to divide the earnings per program (cell B15) by the minimal variety of trainees (cell B5).
\nc.\tFill 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.)
\nfour.\tDeShawn 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.<\/p>\n
Add information validation to the vary B5:F5 as follows:
\na.\tSet an information validation rule for the vary B5:F5 that enables solely entire quantity values larger than zero.
\nb.\tAdd an Enter Message utilizing Variety of Trainees because the Enter Message title and the next textual content because the Enter message:
\nEnter the minimal variety of trainees for this program.
\nc.\tAdd an Error Alert utilizing the Cease fashion, Trainees Error because the Error Alert title, and the next textual content because the Error message:
\nThe minimal variety of trainees have to be larger than zero.
\n5.\tEstablish the invalid information within the worksheet and proper the entry as follows:
\na.\tCircle the invalid information within the worksheet.
\nb.\tKind 15 because the minimal variety of trainees for the Tech Expertise program (cell C5).
\nc.\tConfirm that this variation cleared the remaining errors within the worksheet.
\n6.\tGo 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.<\/p>\n
Add a brand new situation to check the revenue with most enrollments as follows:
\na.\tUse Max Attendance because the situation title.
\nb.\tUse the enrolled trainees per day information (vary B8:F8) because the altering cells.
\nc.\tEnter 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.
\nDesk 1: Cell Values for the Max Attendance Situation<\/p>\n
Cell\tWorth
\nOrientation_Trainees (cell B8)\t24
\nTech_Skills_Trainees (cell C8)\teight
\nSoft_Skills_Trainees (cell D8)\t32
\nProduct_Trainees (cell E8)\t24
\nMandatory_Trainees (cell F8)\t32<\/p>\n
7.\tDeShawn additionally desires to calculate revenue primarily based on the minimal variety of trainees.<\/p>\n
Add one other new situation to check the revenue with low program enrollment as follows:
\na.\tAdd a situation to the worksheet utilizing Low Attendance because the situation title.
\nb.\tUse the enrolled trainees per day information (vary B8:F8) because the altering cells.
\nc.\tEnter cell values for the Low Attendance situation as proven in daring in Desk 2.
\nDesk 2: Cell Values for the Low Attendance Situation<\/p>\n
Cell\tWorth
\nOrientation_Trainees (cell B8)\t10
\nTech_Skills_Trainees (cell C8)\t2
\nSoft_Skills_Trainees (cell D8)\t16
\nProduct_Trainees (cell E8)\t10
\nMandatory_Trainees (cell F8)\t15<\/p>\n
eight.\tPresent the Low Attendance situation values within the Tutorial Charges worksheet.
\n9.\tGo 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.<\/p>\n
Examine the common revenue per program primarily based on the eventualities as follows:
\na.\tCreate 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.
\nb.\tUse New Charges Situation Report because the title of the worksheet containing the report.
\n10.\tDeShawn 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:
\na.\tCreate 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.
\nb.\tUse New Charges PivotTable because the title of the worksheet containing the PivotTable.
\nc.\tFormat cells B4:F6 within the New Charges PivotTable worksheet utilizing the Accounting quantity format with zero decimal locations and $ because the image.
\n11.\tGo 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.<\/p>\n
Use Solver to seek out this info as follows:
\na.\tUse 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.
\nb.\tUse the variety of Tuesday and Thursday periods for the 5 applications (vary B4:F5) because the altering variable cells.
\nc.\tDecide and enter the constraints primarily based on the knowledge supplied in Desk three.
\nd.\tUse Simplex LP because the fixing methodology to discover a world optimum answer.
\ne.\tSave the Solver mannequin in cell A26.
\nf.\tResolve the mannequin, holding the Solver answer.
\nDesk three: Solver Constraints<\/p>\n
Constraint\tCell or Vary
\nEvery webinar is scheduled not less than as soon as on Tuesday and as soon as on Thursday\tB4:F5
\nEvery Tuesday and Thursday webinar worth is an integer\tB4:F5
\nEvery sort of webinar is scheduled 1 time per week or extra\tB6:F6
\nEvery sort of webinar is scheduled three instances per week or much less\tB6:F6
\nThe full variety of Tuesday webinars is 5 or much less\tTotal_Tuesday_Webinars
\nThe full variety of Thursday webinars is 15 or much less\tTotal_Thursday_Webinars
\nThe full variety of webinars per week is 13\tTotal_Weekly_Webinars
\nThe full variety of Tuesday consultations is 2 or much less \tTuesday_Consultations
\nThe full variety of Thursday consultations is 2 or much less\tThursday_Consultations
\nThe full variety of consultations per week is 5 or much less\tTotal_Consultations<\/p>\n
12.\tDeShawn 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:
\na.\tResolve the mannequin once more, this time selecting to supply an Reply report.
\nb.\tUse Webinar Reply Report because the title of the worksheet containing the Reply report.
\nYour 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.<\/p>\n
Ultimate Determine 1: Blended Coaching Worksheet<\/p>\n
Ultimate Determine 2: Tutorial Charges Worksheet<\/p>\n
Ultimate Determine three: New Charges Situation Report Worksheet<\/p>\n
Ultimate Determine four: New Charges PivotTable Worksheet<\/p>\n
Ultimate Determine 5: New Charges Worksheet<\/p>\n
Ultimate Determine 6: Webinar Reply Report Worksheet<\/p>\n
Ultimate Determine 7: Webinar Worksheet<\/p>\n
——–<\/p>\n
Maxwell Coaching FORMULA AUDITING, DATA VALIDATION, AND COMPLEX PROBLEM SOLVING STEP ONE: GETTING STARTED
\n\u2022 Open the file SC_EX19_9a_FirstLastName_1.xlsx, out there for obtain from the SAM web site.
\n\u2022 Save the file as SC_EX19_9a_FirstLastName_2.xlsx by altering the \u201c1\u201d to a \u201c2\u201d.
\no 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.
\n\u2022 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.
\no If cell B6 doesn’t show your title, delete the file and obtain a brand new copy from the SAM web site.
\n\u2022
\nTo finish this venture, you have to add the Solver Add-in. If Solver isn’t listed beneath<\/p>\n","protected":false},"excerpt":{"rendered":"
Maxwell Coaching FORMULA AUDITING, DATA VALIDATION, AND COMPLEX PROBLEM SOLVING GETTING STARTED \u2022 Open the file SC_EX19_9a_FirstLastName_1.xlsx, out there for obtain from the SAM web\u2026<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[666,667,665,663,664],"tags":[668,672,670,676,675,669,673,674,671],"class_list":["post-22323","post","type-post","status-publish","format-standard","hentry","category-nurse-study","category-nursing-essay","category-nursing-study","category-online-nursing-study","category-online-nursing-writers","tag-free-printable-nursing-study-guides","tag-nursing-study-guides-free","tag-nursing-study-guides-worksheets","tag-nursing-study-hub","tag-nursing-study-material","tag-nursing-study-websites","tag-study-nursing-in-australia","tag-study-nursing-in-usa","tag-study-nursing-online-uk"],"_links":{"self":[{"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/posts\/22323","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/comments?post=22323"}],"version-history":[{"count":0,"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/posts\/22323\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/media?parent=22323"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/categories?post=22323"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/tags?post=22323"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}