2 3 4 5 6 7 8 9 10 11 12 13 14 Start Excel Download...

  

Transcribed Image Text:

2 3 4 5 6 7 8 9 10 11 12 13 14 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 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 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 Create range names based on the values in the range C19 C20 based on the values in the range B19 B20 In cell C19 use the SUM function to calculate total building expenses. The total building expenses are the sum of the range C16 E16. In cell C20 use the SUM function to calculate total profits. The total profit is the sum of the range C17 E17 If not loaded, load the Solver add-in Set the objective cell to Max Net Profit (cell C21) Assign Total Contracts (range C8 E8) as the Changing Variables Cells Set a constraint to ensure contracts are whole numbers. Set a constraint to ensure the raw material used do not exceed inventory Set a constraint to ensure that the Total Contracts are greater or equal to the existing contracts Create an Answer Report to outline your findings 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. Save and close Exp19_Excel Ch06_ML2_HW_DeckBuilders xlsx Exit Excel. Submit the file as directed. Total Points D 8 8 6 A 10 14 4 4 18 16 O 100 1 2 3 5 6 7 87 B 9 0 1 2 3. File 1 Paste Clipboard A Home Insert X (6 S Calibri BIU V B Indy Deck Builders Raw Material Wood Hardware Paint Model Manufacturing Cost Selling Price Profit Margin Existing Contracts Total Contracts Font Draw Page Layout Model Building Expense Total Profit Deck Builders Total Building Expense Total Profit Net Profit Y fx 555 $ $ AA == A- A S 566.25 $ $ 2,000.00 $ $ 1,433.75 $ 15 Existing Contracts Formulas A 45 0 D B B Datal Alignment 828.75 $ 3,500.00 $ 2,671.25 $ 55 0 Review View Wrap Text Merge & Center Cost Per Unit Units in Inventory Total Units Consumed $ 2.00 70000 20000 0.75 1.25 35000 E C C 1,258.75 5,000.00 3,741.25 75 0 0 S 0 0 F Help General $ % 98 98 Y 00 Number 15 Conditional Format as Cell Formatting Table Styles Styles Raw Material Required Per Model Wood Hardware Paint Raw Materials Consumed Wood Hardware Paint V H A A 200 55 100 000 B B 310 70 125 0 OOO 0 0 Insert Delete Format Cells + C 500 95 150 C 000 OFIM Σ K 2 3 4 5 6 7 8 9 10 11 12 13 14 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 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 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 Create range names based on the values in the range C19 C20 based on the values in the range B19 B20 In cell C19 use the SUM function to calculate total building expenses. The total building expenses are the sum of the range C16 E16. In cell C20 use the SUM function to calculate total profits. The total profit is the sum of the range C17 E17 If not loaded, load the Solver add-in Set the objective cell to Max Net Profit (cell C21) Assign Total Contracts (range C8 E8) as the Changing Variables Cells Set a constraint to ensure contracts are whole numbers. Set a constraint to ensure the raw material used do not exceed inventory Set a constraint to ensure that the Total Contracts are greater or equal to the existing contracts Create an Answer Report to outline your findings 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. Save and close Exp19_Excel Ch06_ML2_HW_DeckBuilders xlsx Exit Excel. Submit the file as directed. Total Points D 8 8 6 A 10 14 4 4 18 16 O 100 1 2 3 5 6 7 87 B 9 0 1 2 3. File 1 Paste Clipboard A Home Insert X (6 S Calibri BIU V B Indy Deck Builders Raw Material Wood Hardware Paint Model Manufacturing Cost Selling Price Profit Margin Existing Contracts Total Contracts Font Draw Page Layout Model Building Expense Total Profit Deck Builders Total Building Expense Total Profit Net Profit Y fx 555 $ $ AA == A- A S 566.25 $ $ 2,000.00 $ $ 1,433.75 $ 15 Existing Contracts Formulas A 45 0 D B B Datal Alignment 828.75 $ 3,500.00 $ 2,671.25 $ 55 0 Review View Wrap Text Merge & Center Cost Per Unit Units in Inventory Total Units Consumed $ 2.00 70000 20000 0.75 1.25 35000 E C C 1,258.75 5,000.00 3,741.25 75 0 0 S 0 0 F Help General $ % 98 98 Y 00 Number 15 Conditional Format as Cell Formatting Table Styles Styles Raw Material Required Per Model Wood Hardware Paint Raw Materials Consumed Wood Hardware Paint V H A A 200 55 100 000 B B 310 70 125 0 OOO 0 0 Insert Delete Format Cells + C 500 95 150 C 000 OFIM Σ K

Answer rating: 100% (QA)

Raw material consumed Raw material required Total contracts Total uniits consumed s... View the full answer