Product Mix Optimization - e05 grader H2 Project

Anonymous
timer Asked: Apr 15th, 2019
account_balance_wallet $15

Question Description

Project Description:

3-D CustomAble Designs is a medical device company that utilizes 3-D printing technology to manufacture wheelchairs. They offer a standard model for the majority of their customer base but have recently starting implementing a design-your-own-chair service in which the chairs can be customized with some unique features and designs. You have been asked to use your knowledge of Excel to help them clean up some of their customer names that were corrupted when exported from their CRM system. You have also been asked to help with a Solver model that will help to determine the optimal mix of standard and custom wheelchairs necessary to maximize profit, given several labor and material constraints.


The WORD FILE IS AN INSTRUCTION ONLY. SO YOU DON'T HAVE TO DO ANYTHING ON IT.

Unformatted Attachment Preview

Materials and Labor Costs Hourly Rate Primary Materials per unit Additional Materials per oz # of Units Produced Estimated Max Demand $ 10.50 $ 275.00 $ 2.15 Resources Per Unit Primary Materials per unit Additional Materials (oz) Selling Price Custom Build 27 <= Standard Build 33 <= 27 45 Custom Builds Standard Builds Revenue Costs Labor Primary Materials Additional Materials Profits Saved Solver Model Custom Build 15 Labor (hours) 1 25 $ 750.00 Material Constraints Labor (hours) Primary Materials per unit Additional Materials (oz) Totals Per Unit Standard Build 4 1 $ 11 450.00 Material Constraints Used 537 <= Available 350 60 <= 70 1,038 <= 1100 CustomerName " MostRecentPuchase ANDERSON RACHEL #" 20180215 "# BEASLEY CHARLES# " 20180630 # BAIRD EDWARD 20180420 #CARNEY LISA 20180214 ## CONNELLY SHANEEDA# 20180220 DAKIC BRAHIM# 20180115 FADWEI TRANH# 20180201 ##GAINES THOMAS 20180416 # GIFFORD MARJORIE# 20180807 #HANKINS ELIZA # 20181019 "## HOWARD SEAN " 20181031 # JEFFRIES PETER# 20181112 JOYCE DAVID.# 20180727 ##KRAUS CAROL 20180717 ##LAMB SAMANTHA 20180610 # LEWANDOWSKI ANGELA 20180602 Office 2016 – myitlab:grader – Instructions Excel Project YO16_XL_BU05_GRADER_PS2_HW - Product Mix Optimization 1.2 Project Description: 3-D CustomAble Designs is a medical device company that utilizes 3-D printing technology to manufacture wheelchairs. They offer a standard model for the majority of their customer base but have recently starting implementing a design-your-own-chair service in which the chairs can be customized with some unique features and designs. You have been asked to use your knowledge of Excel to help them clean up some of their customer names that were corrupted when exported from their CRM system. You have also been asked to help with a Solver model that will help to determine the optimal mix of standard and custom wheelchairs necessary to maximize profit, given several labor and material constraints. Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Points Possible Instructions 1 Start Excel. Open the downloaded file named e05_grader_h2_ProductMix.xlsx. Save the file with the name e05_grader_h2_ProductMix_LastFirst, using your last and first name. If necessary, enable the content. 0.000 2 Import the tab-delimited text file e05SampleCustomers.txt into cell A1 on the RecentCustomers worksheet. 10.000 3 In cell C1, type CleanNames as the column heading. 3.000 4 In the cell range C2:C17, insert a text function to clear any nonprinting characters and extra spaces from the names in column A. 5.000 5 In cell D1, type FirstName as the column heading. 3.000 6 In the cell range D2:D17, insert text functions to display only the first name from the right side of the clean names in column C. The names should be displayed in proper case. 8.000 7 In cell E1, type LastName as the column heading. 3.000 8 In the cell range E2:E17, insert text functions to display only the last name from the left side of the clean names in column C. The names should be displayed in proper case and not include any extra spaces. Adjust the column widths so that all data are visible. 8.000 9 On the MaximizeProfits worksheet, complete the following so that a linear Solver model can be created. In cell B18, calculate the revenue of custom-designed wheelchairs, using the selling price in cell E8 and the number of units produced in cell B11. Copy the formula over to cell C18 to calculate the revenue from standard wheelchairs. 8.000 Updated: 10/20/2017 1 Current_Instruction.docx Office 2016 – myitlab:grader – Instructions Step Excel Project Points Possible Instructions 10 In cell B21, calculate the costs of labor for custom-designed wheelchairs by multiplying the hourly rate in cell B5 by the number of hours necessary to create a custom wheelchair in cell E5 by the number of custom wheelchairs produced in cell B11. Use appropriate cell referencing so that the formula can be copied down to cell B23 to calculate the costs of primary and additional materials for custom wheelchairs and can be copied over to calculate all costs for standard wheelchairs. 8.000 11 In cell B25, calculate the profit of custom wheelchairs by subtracting the total costs from the revenue. Copy the formula over to cell C25 to calculate the profit from standard wheelchairs. 8.000 12 In cell D18, calculate the total revenue by adding the revenue from custom and standard wheelchairs. Copy and paste the formula into the cell range D21:D23 and cell D25. 8.000 13 Begin creating a Solver model by setting the objective to maximize the total profit in cell D25. Set the changing cells to be the number of units to produce in the cell range B11:C11. 5.000 14 Create a constraint that will ensure that the number of units to produce will be whole numbers. Create a constraint that will ensure that the number of units produced will not exceed the maximum expected demand in the cell range B13:C13. Create a constraint that will ensure that the used resources in the cell range F12:F14 will not exceed the available resources in the cell range H12:H14. 9.000 15 Use the GRG Nonlinear method and save the Solver model for use later, starting in cell A28. 6.000 16 Run Solver and create a Solver Answer Report. 8.000 17 Save the workbook, exit Excel, and then submit your file as directed by your instructor. 0.000 Total Points Updated: 10/20/2017 2 100.000 Current_Instruction.docx ...
Purchase answer to see full attachment

Tutor Answer

ProfessorArthur
School: University of Maryland

Attached.

CustomerName
ANDERSON RACHEL
BEASLEY CHARLES
BAIRD EDWARD
CARNEY LISA
CONNELLY SHANEEDA
DAKIC BRAHIM
FADWEI TRANH
GAINES THOMAS
GIFFORD MARJORIE
HANKINS ELIZA
HOWARD SEAN
JEFFRIES PETER
JOYCE DAVID
KRAUS CAROL
LAMB SAMANTHA
L...

flag Report DMCA
Review

Anonymous
Tutor went the extra mile to help me with this essay. Citations were a bit shaky but I appreciated how well he handled APA styles and how ok he was to change them even though I didnt specify. Got a B+ which is believable and acceptable.

Similar Questions
Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors