Exces Project EX16 XL_CH06_GRADER_CAP_HW - Delta Paint 1.5 Project Description:
ID: 3751383 • Letter: E
Question
Exces Project EX16 XL_CH06_GRADER_CAP_HW - Delta Paint 1.5 Project Description: You are the production manager for Delta Paint, a regional manufacturing company that specializes in customized paints. Your company sells paint by the gallon, and you have the task of forecasting the best production blends to maximize profit and most effectively utilize resources. Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Instructions Points 1 Open the download file exploring e06 grader Capstone Start.xisx. Create appropriate range names for Total Production Cost (cell B18) and Gross Profit (cell B21) by selection, using the values in the left column. Edit the existing name range Employee Hourly_Wage to Hourly_Wages2018 Note, Mac users, in the Define Name dialog box, add the new named range, and delete the original one. Use the newly created range names to create a formula to calculate Net Profit (in cell B22) Create d new eet labeled Range Names, paste the newly created range name information in cell A1, and resize the columns as needed for proper display. On the Forecast sheet, start in cell E3. Complete the series of substitution values ranging from 6 10 to 200 at increments of 10 gallons vertically down column E. Enter references to the Total_Production_ Cost, Gross Profit, and Net Profit cells in the where indicated 7 correct locations (F2, G2, and H2 respectively) for a one-variable data table. Use range names 3 Complete the one-variable data table in the range E2:H22 using cell B4 as the column input 8cell, and then format the results with Accounting Number Format with two decimal places Apply custom number formats to make the formula references appear as descriptive column headings. In F2, Total Costs; in G2, Gross Profit, in H2, Net Profit. Bold and center the headings and substitution values. 9 Copy the number of gallons produced substitution values from the one-variable data table, and then paste the values starting in cell E26. 10 Current_InstructExplanation / Answer
Answer:
Step 1: Open the excel file "exploring_e06_grader_Capstone.xlsx"
Step 2: choose cell B18. Then go to Formula tab - Define Names tab in Defined Named section
?Enter the "Total_Production_Cost" in the Name field and click ok
Step 3: Select cell B21. Then go to Formula tab - Define Names tab in Defined Named section
?Enter "Gross_Profit" in the Name field and click ok
Step 4: To Edit the existing name range Employee_Hourly_Wage to Hourly_Wages2018.? - Formula tab - Name Manager tab in Defined Named section - Select the name "Employee_Hourly_Wage" Press Edit - Change the Name to "Hourly_Wages2018"
Step 5: Select cell B22 enter the formula " Gross_Profit-Total_Production_Cost
Step 6: Click on the "+" icon next to the sheet name a new sheet is created- right click on the new sheet name and select rename- "Range Names" ?Select Cell A1 - Type the newly created range info Type "Gross_Profit" - B22 and ""Total_Production_Cost"- B18
Step 7: Go to sheet 'Forecast" select E3. Enter 10, Select E4 enter 20 .. continue till 200 will increment of 10.
Step 8: Select the one-variable-data table in cell F2 enter "=Total_Production_Cost," in cell G2 enter "=Gross_Profit" , and in cell H2 enter "=" and select the Net profit value cell
Step 9: Now select the range from E2 to H22 Your selection will include the references you entered in Step 8 and 9. Go to "what if analysis" - Data table. In the pop up box enter the column input range as B4
Step 10: Now format the data as acounting with two decimals.Select cell F2 go to Format Cells - choose Custom format- Choose the general option.now in the text bocx below type Enter " Total Production Cost" Press ok, Now cell F2 wil display " Total Production Cost". Repeat the same for the cell G2 andH2.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.