Maxwell Training
\nFORMULA AUDITING, DATA VALIDATION, AND COMPLEX PROBLEM SOLVING<\/p>\n
GETTING STARTED
\n\u2022\tOpen the file SC_EX19_9a_FirstLastName_1.xlsx, available for download from the SAM website.
\n\u2022\tSave the file as SC_EX19_9a_FirstLastName_2.xlsx by changing the \u201c1\u201d to a \u201c2\u201d.
\no\tIf 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.
\n\u2022\tWith the file SC_EX19_9a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
\no\tIf cell B6 does not display your name, delete the file and download a new copy from the SAM website.
\n\u2022\tTo complete this project ( help with nursing paper writing from experts with MSN & DNP degrees), 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.
\nPROJECT STEPS
\n1.\tDeShawn 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.<\/p>\n
Go to the Blended Training worksheet. DeShawn asks you to correct the errors in the worksheet. Correct the first error as follows:
\na.\tUse the Trace Precedents arrows to find the source of the #VALUE! error in cell F7.
\nb.\tUse the Trace Dependents arrows to determine whether the formula in cell F7 causes other errors in the worksheet.
\nc.\tCorrect 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.
\nd.\tRemove the trace arrows.
\n2.\tCorrect the Name error in cell B21 as follows:
\na.\tUse any error-checking method to determine the source of the error in cell B21, which should calculate the average income per session.
\nb.\tCorrect the error by editing the formula in cell B21.
\n3.\tCorrect the divide-by-zero errors as follows:
\na.\tEvaluate the formula in cell B17 to determine which cell is causing the divide-by-zero error.
\nb.\tCorrect the formula in cell B17, which should divide the income per program (cell B15) by the minimum number of trainees (cell B5).
\nc.\tFill the range C17:F17 with the formula in cell B17. (Hint: You will correct the new error in cell C17 in the following steps.)
\n4.\tDeShawn 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.<\/p>\n
Add data validation to the range B5:F5 as follows:
\na.\tSet a data validation rule for the range B5:F5 that allows only whole number values greater than 0.
\nb.\tAdd an Input Message using Number of Trainees as the Input Message title and the following text as the Input message:
\nEnter the minimum number of trainees for this program.
\nc.\tAdd an Error Alert using the Stop style, Trainees Error as the Error Alert title, and the following text as the Error message:
\nThe minimum number of trainees must be greater than 0.
\n5.\tIdentify the invalid data in the worksheet and correct the entry as follows:
\na.\tCircle the invalid data in the worksheet.
\nb.\tType 15 as the minimum number of trainees for the Tech Skills program (cell C5).
\nc.\tVerify that this change cleared the remaining errors in the worksheet.
\n6.\tGo 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.<\/p>\n
Add a new scenario to compare the profit with maximum enrollments as follows:
\na.\tUse Max Attendance as the scenario name.
\nb.\tUse the enrolled trainees per day data (range B8:F8) as the changing cells.
\nc.\tEnter 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.
\nTable 1: Cell Values for the Max Attendance Scenario<\/p>\n
Cell\tValue
\nOrientation_Trainees (cell B8)\t24
\nTech_Skills_Trainees (cell C8)\t8
\nSoft_Skills_Trainees (cell D8)\t32
\nProduct_Trainees (cell E8)\t24
\nMandatory_Trainees (cell F8)\t32<\/p>\n
7.\tDeShawn also wants to calculate profit based on the minimum number of trainees.<\/p>\n
Add another new scenario to compare the profit with low program enrollment as follows:
\na.\tAdd a scenario to the worksheet using Low Attendance as the scenario name.
\nb.\tUse the enrolled trainees per day data (range B8:F8) as the changing cells.
\nc.\tEnter cell values for the Low Attendance scenario as shown in bold in Table 2.
\nTable 2: Cell Values for the Low Attendance Scenario<\/p>\n
Cell\tValue
\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
8.\tShow the Low Attendance scenario values in the Tutorial Fees worksheet.
\n9.\tGo 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.<\/p>\n
Compare the average profit per program based on the scenarios as follows:
\na.\tCreate 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.
\nb.\tUse New Fees Scenario Report as the name of the worksheet containing the report.
\n10.\tDeShawn 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:
\na.\tCreate 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.
\nb.\tUse New Fees PivotTable as the name of the worksheet containing the PivotTable.
\nc.\tFormat cells B4:F6 in the New Fees PivotTable worksheet using the Accounting number format with 0 decimal places and $ as the symbol.
\n11.\tGo 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.<\/p>\n
Use Solver to find this information as follows:
\na.\tUse 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.
\nb.\tUse the number of Tuesday and Thursday sessions for the five programs (range B4:F5) as the changing variable cells.
\nc.\tDetermine and enter the constraints based on the information provided in Table 3.
\nd.\tUse Simplex LP as the solving method to find a global optimal solution.
\ne.\tSave the Solver model in cell A26.
\nf.\tSolve the model, keeping the Solver solution.
\nTable 3: Solver Constraints<\/p>\n
Constraint\tCell or Range
\nEach webinar is scheduled at least once on Tuesday and once on Thursday\tB4:F5
\nEach Tuesday and Thursday webinar value is an integer\tB4:F5
\nEach type of webinar is scheduled 1 time per week or more\tB6:F6
\nEach type of webinar is scheduled 3 times per week or less\tB6:F6
\nThe total number of Tuesday webinars is 5 or less\tTotal_Tuesday_Webinars
\nThe total number of Thursday webinars is 15 or less\tTotal_Thursday_Webinars
\nThe total number of webinars per week is 13\tTotal_Weekly_Webinars
\nThe total number of Tuesday consultations is 2 or less \tTuesday_Consultations
\nThe total number of Thursday consultations is 2 or less\tThursday_Consultations
\nThe total number of consultations per week is 5 or less\tTotal_Consultations<\/p>\n
12.\tDeShawn 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:
\na.\tSolve the model again, this time choosing to produce an Answer report.
\nb.\tUse Webinar Answer Report as the name of the worksheet containing the Answer report.
\nYour 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 ( help with nursing paper writing from experts with MSN & DNP degrees).<\/p>\n
Final Figure 1: Blended Training Worksheet<\/p>\n
Final Figure 2: Tutorial Fees Worksheet<\/p>\n
Final Figure 3: New Fees Scenario Report Worksheet<\/p>\n
Final Figure 4: New Fees PivotTable Worksheet<\/p>\n
Final Figure 5: New Fees Worksheet<\/p>\n
Final Figure 6: Webinar Answer Report Worksheet<\/p>\n
Final Figure 7: Webinar Worksheet<\/p>\n
——–<\/p>\n
Maxwell Training FORMULA AUDITING, DATA VALIDATION, AND COMPLEX PROBLEM SOLVING STEP ONE: GETTING STARTED
\n\u2022 Open the file SC_EX19_9a_FirstLastName_1.xlsx, available for download from the SAM website.
\n\u2022 Save the file as SC_EX19_9a_FirstLastName_2.xlsx by changing the \u201c1\u201d to a \u201c2\u201d.
\no 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.
\n\u2022 With the file SC_EX19_9a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
\no If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
\n\u2022
\nTo complete this project ( help with nursing paper writing from experts with MSN & DNP degrees), you need to add the Solver Add-in. If Solver is not listed under<\/p>\n","protected":false},"excerpt":{"rendered":"
Maxwell Training FORMULA AUDITING, DATA VALIDATION, AND COMPLEX PROBLEM SOLVING GETTING STARTED \u2022 Open the file SC_EX19_9a_FirstLastName_1.xlsx, available for download from the SAM website. \u2022\u2026<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9033,7586,1386,7589,9034,9031,7587,7588,7584,7585,9032],"tags":[8809,7838,8811,8808,8813,8807,8806,8814,8810,8812],"class_list":["post-107226","post","type-post","status-publish","format-standard","hentry","category-assignment-help-uae","category-best-dissertation-writing-service-in-australia","category-best-research-paper-writing-services-in-usa","category-capstone-project-writing-service","category-cheap-essay-writing-services-china","category-essay-writing-service-usa","category-legitimate-phd-dissertation-writing-services-usa","category-term-paper-writing-service","category-thesis-writing-service-in-canada","category-uae-best-dissertation-writing-help-service","category-write-my-essay-australia","tag-best-master-thesis-writing-service","tag-best-thesis-writing-services","tag-cheap-thesis-writing-services","tag-master-thesis-help","tag-master-thesis-writing-service","tag-thesis-help-online-free","tag-thesis-writing-help-online","tag-thesis-writing-services-cost","tag-thesis-writing-services-near-me","tag-thesis-writing-services-reviews"],"_links":{"self":[{"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/posts\/107226","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\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/comments?post=107226"}],"version-history":[{"count":0,"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/posts\/107226\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/media?parent=107226"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/categories?post=107226"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/tags?post=107226"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}