Excel

by


Grader – Instructions Excel 2019 Project

YO19_Excel_BU05_PS2_Product_Mix_Optimization

 

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 started 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.

 

Steps to Perform:

Step Instructions Points Possible
1 Start Excel. Open the downloaded file named Excel_BU05_PS2_ProductMix.xlsx. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files. 0
2 You have been asked to import corrupted data from a text file. The data needs to be corrected for future use. Establish a connection to the tab-delimited text file Excel_BU05_PS2_SampleCustomers.txt using Get and Transform. 8
3 Under the Home tab and in the Transform group, use Split Columns to split the columns by a Delimiter. If the Tab character is not autodetected, select it from the list. Then use the first row as headers. 3
4 Clear any nonprinting characters and extra spaces from the text in the CustomerName column. Display the names in proper case. All of the names are arranged by last name, a space character, and the first name of the customer. Split the names into two columns titled, Last Name and First Name and remove any extra spaces. 18
5 Close and Load the customer names into cell A1 of the RecentCustomers worksheet. 5
6 On the MaximizeProfits worksheet, complete the following so that a linear Solver model can be created to maximize profits for 3-D CustomAble Designs. In cell B18, calculate the revenue of custom-designed wheelchairs, using the selling price in cell F8 and the number of units produced in cell B11. Copy the formula over to cell C18 to calculate the revenue from standard wheelchairs. 8
7 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 F5 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
8 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
9 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
10 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
11 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. 15
12 Use the GRG Nonlinear method and save the Solver model for use later, starting in cell A28. 6
13 Run Solver and create a Solver Answer Report. 8
14 Save and close Excel_BU05_PS2_ProductMix. Exit Excel. Submit your files as directed. 0
Total Points 100

 

Created On: 01/22/2021 1 yo19_excel_bu05_ps2