Exp19_Excel_Ch06_ML2_Deck_Builders
danevia
Exp19_Excel_Ch06_ML2_Deck_Builders_Instructions.docxGrader - Instructions Excel 2019 Project
Exp19_Excel_Ch06_ML2_Deck_Builders
Project Description:
You are the business manager for Indy Deck Builders, a local construction company that specializes in new deck installation. Your company offers three deck models. Model A which costs $2,000, model B costs $3,500, and model C costs $5,000. You currently have 45 contracts to build model A, 55 for model B, and 75 for model C. Once the contracts have been completed, you will have a surplus of materials (wood, hard-ware, and paint) remaining in inventory. After expenses your company’s net profit is $492,031.25. You would like to use Solver to forecast how many additional contracts you can accept to maximize profit while not exceeding the raw materials in inventory.
Steps to Perform:
|
Step |
Instructions |
Points Possible |
|
1 |
Start Excel. Download and open the file named EXP19_Excel_Ch06_ML2_HW_DeckBuilders.xlsx. Grader has automatically added your last name to the beginning of the filename. |
0 |
|
2 |
Calculate total building expensive for model A in cell C16. Total expense is calculated by multiplying manufacturing cost (C4) and total contracts (C8). Then use the fill handle to copy the formula into cells D16 and E16. |
8 |
|
3 |
Calculate total profit for model A in cell C17. Total profit is calculated by multiplying profit marging (C6) and total contracts (C8). Then use the fill handle to copy the formula into cells D17 and E17. |
8 |
|
4 |
Create range names based on the values in the range C19:C20 based on the values in the range B19:B20. |
6 |
|
5 |
In cell C19 use the SUM function to calculate total building expenses. The total building expenses are the sum of the range C16:E16. |
4 |
|
6 |
In cell C20 use the SUM function to calculate total profits. The total profit is the sum of the range C17:E17. |
4 |
|
7 |
If not loaded, load the Solver add-in. Set the objective cell to Max Net Profit (cell C21). |
10 |
|
8 |
Assign Total Contracts (range C8:E8) as the Changing Variables Cells. |
14 |
|
9 |
Set a constraint to ensure contracts are whole numbers. |
4 |
|
10 |
Set a constraint to ensure the raw material used do not exceed inventory. |
4 |
|
11 |
Set a constraint to ensure that the Total Contracts are greater or equal to the existing contracts. |
4 |
|
12 |
Create an Answer Report to outline your findings. |
18 |
|
13 |
Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side of both worksheets. |
16 |
|
14 |
Save and close Exp19_Excel_Ch06_ML2_HW_DeckBuilders.xlsx. Exit Excel. Submit the file as directed. |
0 |
|
Total Points |
100 |
Created On: 10/06/2020 1 Exp19_Excel_Ch06_ML2 - Deck Builders 1.3
