Description Will upload excel file to be worked on, once agreed upon. 2 Create appropriate range names for Total Production Cost (cell B18) and Gross Profit (cell B21) by selection, using the values in the left column. 3 3 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. 3 4 Use the newly created range names to create a formula to calculate Net Profit (in cell B22). 4 5 Create a new worksheet labeled Range Names, paste the newly created range name information in cell A1, and resize the columns as needed for proper display. 5 6 On the Forecast sheet, start in cell E3. Complete the series of substitution values ranging from 10 to 200 at increments of 10 gallons vertically down column E. 2 7 Enter references to the Total_Production_Cost, Gross_Profit, and Net Profit cells in the correct locations (F2, G2, and H2 respectively) for a one-variable data table. Use range names where indicated. 3 8 Complete the one-variable data table in the range E2:H22 using cell B4 as the column input cell, and then format the results with Accounting Number Format with two decimal places. 5 9 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. 3 10 Copy the number of gallons produced substitution values from the one-variable data table, and then paste the values starting in cell E26.
Recent Comments