1. Business
  2. Economics
  3. the excel screen shots are the first two images and...

Question: the excel screen shots are the first two images and...

Question details
The excel screen shots are the first two images. And the rest are instructions.

Delta Paint One-Variable Data Table: Production Input 4 Units Sold 5 Unit Selling Price 6 Employee Hourly Wage 7 Maximum Capabilty per week 100 $30.00 15.00 200 Limitations 10 Raw Materials in Units 11 Required raw materials per unit 12 Required Labor Hours Per Gallon 3 Labor Hours available 15000 0.25 2,000 Expenses 6 Raw Materials Consumec 7 Labor Hours Consumed 8 Total Production Cost 300 25 $375.00 :0 1 Gross Profit 2 Net Profit ncome $3,000 $2,625
Two-Variable Data Table: Production and Manufacturing Time 25 26 27 28 30 32 37 40 42 45
Stepu Instructions 1 Open the download-file exploring e06 grader Capstone.xisx a 0.000x Create appropriate range names for Total-Production Cost (cell B18) and Gross Profit (cell B21)-by selection, using the valuesin the left column. 2x 3.000M When.complete, your range names.should-be Total.Production. Costand-Gross Profit ロ 3 Edit the existing name range Emplovee.Hourly Wage to-Hourly Wages2018.0 3.000m Use the newly createdrange names to create a formula-to calculate Net Profit (in cell B22).1 In Step 4, the original formula in cell B22 has unnecessary parentheses. When you replace deducted 4.000m 4Hthis formula with your new-one, make sure-not to include parentheses or marks will be Create .a.new.worksheetlabeled.Range-Names, paste the newly.created range-name. 5H information in cell A1, and resize the columns as needed for proper display. 5.000m Hint:-Use the Use-in Formula-button in the Formulas tab-to paste in the list of names.o On the Forecast sheet, start in cell E3. Complete the series of substitution values ranging from 2.000 10 to 200 at increments of 10-gallons vertically down column E.D
Enter references to-the IotalProductionCost gross Profit,and-Net Profit-cells in the where indicated.D Complete-the one-variable data table in-therange F3:H22 using cell B4 as the column-input correct locations (F2, G2, and-H2 respectively)forarone-variable -data table. Use range names 3.000x cell, and-then format-the results with Accounting Number Format with two decimal places. Hint: Make sure to select E2:H22whencreating the datatable. Apply custom number-formats tomake the-formula references appear as descriptive column headings and substitution values. 8x 5.000x 9x headings. In F2, Total-Costs;in-G2, Gross-Profit, in H2, Net Profit.Bold and center the 3.000n Points Possiblex Step Instructionsn Copy the number of gallons produced substitution values from the one-variable data table, 4.000x and then paste the values starting in cell E26.0 Type $15-in cell F25. Complete the series of substitution values-from $15-to $40 at s5. I increments. 11H 4.000m Enter the reference to the net profit formula in the correct location for a two-variable data 12 table.D 4.000M
Complete-the-two-variable data-table in-the range F26:K45. Use cell-B6 as the Row input cell decimal places.o Apply a custom number format to make the-formula reference (you entered in-Step-12) substitution values where necessary.D Create a scenario named Best Case, using-Units Sold, Unit Selling Price, and Employee Hourly 13* and-34 as-the Column input cell. Formalt theresults with-Accounting Number Formatwith two10.00 14x Wages 3.000x Wage (use cell references). Enter-these values for-the scenario: 200, 30, and-15a Create a second scenario named-Worst Case, using the same changing cells. Enterthese values-for-the scenario: 100, 25, and-20. 4.000x Create a third scenario named MostLikely, using the same changing cells. Enter these values for the scenario:150, 25, and 15.0 4.000x Generate a scenario summary report using the cell references for Total-Production Cost and Net-profit. 5.000m 18m Load the Solver add-in if it is not already loaded. Set the objective to calculate the highest Net Profit possible. 5.000 4.000 20x Use the units sold as changing variable cells
Use-the -Limitations section of the spreadsheet model-to-set a constraintforrawmaterials. Hint: You need to-have the raw materials consumed-to-be-less-than or equal-to raw materials 21x 4.000M in units.D Set aconstraint forlabor hours. Hint: You need the labor hours consumed to-be less than or equal to the laborhours available 22x 4.000m Stepx Instructionst Points iblex Set a constraint for maximum production capability. 4.000h Hint: You need the units sold to-be-less than or equal to the maximum capability per week.a 24% | Solve theproblem-Generate theAnswerReportand-KeepSolverSolution.O 5.000x Create a footer on all four worksheets with your name on the left side, the sheet name codein the center, and the file name code on the rightside. 4.000 25H Hint: When creating the-footer, makesure to use the appropriate buttons to insert the sheet name and-file name. Do not type the sheet name or filername directly into the footer. Save and close the file. Based on your instructors directions, submit exploring e06_grader Capstone.xisx. 0.000m 26m
Solution by an expert tutor
Blurred Solution
This question has been solved
Subscribe to see this solution