Question

In: Statistics and Probability

You are the production manager for Delta Paint, a regional manufacturing company that specializes in customized paints.

 

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 Possible

1

Open the download file exploring_e06_grader_Capstone.xlsx.

0.000

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.000

3

Edit the existing name range Employee_Hourly_Wage toHourly_Wages2018.

3.000

4

Use the newly created range names to create a formula to calculate Net Profit (in cell B22).

4.000

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.000

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.000

7

Enter references to the Total_Production_CostGross_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.000

8

Complete the one-variable data table in the range F3:H22 using cell B4 as the column input cell, and then format the results with Accounting Number Format with two decimal places.

5.000

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.000

10

Copy the number of gallons produced substitution values from the one-variable data table, and then paste the values starting in cell E26.

4.000

11

Type $15 in cell F25. Complete the series of substitution values from $15 to $40 at $5 increments.

4.000

12

Enter the reference to the net profit formula in the correct location for a two-variable data table.

4.000

13

Complete the two-variable data table in the range F26:K45. Use cell B6 as the Row input cell andB4 as the Column input cell. Format the results with Accounting Number Format with two decimal places.

10.000

14

Apply a custom number format to make the formula reference appear as a descriptive column heading Wages. Bold and center the headings and substitution values where necessary.

3.000

15

Create a scenario named Best Case, using Units Sold, Unit Selling Price, and Employee Hourly Wage (use cell references). Enter these values for the scenario: 20030, and 15.

4.000

16

Create a second scenario named Worst Case, using the same changing cells. Enter these values for the scenario: 10025, and 20.

4.000

17

Create a third scenario named Most Likely, using the same changing cells. Enter these values for the scenario: 15025, and 15.

4.000

18

Generate a scenario summary report using the cell references for Total Production Cost and Net Profit.

5.000

19

Load the Solver add-in if it is not already loaded. Set the objective to calculate the highest Net Profit possible.

5.000

20

Use the units sold as changing variable cells.

4.000

21

Use the Limitations section of the spreadsheet model to set a constraint for raw materials.

4.000

22

Set a constraint for labor hours.

4.000

23

Set a constraint for maximum production capability.

4.000

24

Solve the problem. Generate the Answer Report and Keep Solver Solution.

5.000

25

Create a footer on all four worksheets with your name on the left side, the sheet name code in the center, and the file name code on the right side.

4.000

26

Save and close the file. Based on your instructor’s directions, submit exploring_e06_grader_Capstone.xlsx.

0.000

 

Total Points

100.000

 

 

Solutions

Expert Solution


Related Solutions

3. A paint manufacturing company claims that the mean drying time for its paints is not...
3. A paint manufacturing company claims that the mean drying time for its paints is not longer than 60 minutes. A random sample of 20 gallons of paints selected from the production line of this company showed that the mean drying time for this sample is 63.50 minutes with a sample standard deviation of 4 min utes. Assume that the drying times for these paints have a normal distribution. Test the claim that the mean drying time for its paints...
You are the production manager for a part manufacturing company. There are 2 manufacturing locations, plant...
You are the production manager for a part manufacturing company. There are 2 manufacturing locations, plant A and B. you suspect there is a difference in the proportion of rejected parts that are manufactured at Plant A as compared to plant B. for a week you observed: Plant A Plant B total Rejected parts 120 80 200 Acceptable parts 1880 1920 3800 2000 2000 4000 The proportion of rejected parts was 200/4000=5%. If there were no difference between the proportion...
Bear Paints is a national paint manufacturer and retailer. The company is segmented into five divisions:...
Bear Paints is a national paint manufacturer and retailer. The company is segmented into five divisions: Paint Stores (branded retail locations), Consumer (paint sold through home improvement stores), Automotive (sales to auto manufacturers), International, and Administration. The following is selected divisional information for its two largest divisions: Paint Stores and Consumer. Net Sales Operating Income Average Total Assets Paint Stores $3,690,000.00 $408,000.00 $1,405,000.00 Consumer 1,529,000.00 168,000.00 1,580,000.00 Management has specified a 21% target rate of return. Calculate each division’s ROI....
You work for a candy company and the manufacturing manager claims that the production line produces...
You work for a candy company and the manufacturing manager claims that the production line produces bags of candy with an average of exactly 50 candies per bag.   You are skeptical about this and you decide to test the claim by counting the candies in a sample of 25 bags. You discover in your sample that x = 48and s = 5. Determine whether or not you have enough statistical evidence to reject the manager's claim with a significance level...
You are the production manager at a parachute manufacturing company. Parachutes are woven in your factory...
You are the production manager at a parachute manufacturing company. Parachutes are woven in your factory using a synthetic fiber purchased from 1 of 4 different suppliers. The factory uses 2 types of looms, the Athenian and the Spartan. You are concerned about the consistency of parachute strength, so you measure the strength for 5 parachutes made with each of the 4 fiber suppliers, using each of the 2 looms. Perform an ANOVA with these data for main effects and...
Inventory Control You are the production manager of GVT Manufacturing, a company that manufactures a wide...
Inventory Control You are the production manager of GVT Manufacturing, a company that manufactures a wide range of products, including fire extinguishers. Data shows that GVT is projected to manufacture 30,000 of these fire extinguishers next year. A production-year for GVT covers 300 days. Information on the product also reveals that each extinguisher requires one handle. Additional information gleaned from the production records shows the following: annual carrying cost per handle is US$1.50; production setup cost is US$150 and daily...
You are a project manager at a construction company that specializes in building office buildings. You...
You are a project manager at a construction company that specializes in building office buildings. You were approached by a representative of a company that was interested in your company’s services. He told you that their company was growing and they decided to build a new office building to address their needs. Then he told you that they already started the process with another construction company. They signed a contract with the other construction company and they started the building...
Color Paint Shop, Inc. is an accrual basis taxpayer that paints automobiles. During 2018, the company...
Color Paint Shop, Inc. is an accrual basis taxpayer that paints automobiles. During 2018, the company painted Sam’s car and was to receive $1,000 payment from his insurance company. Sam was not satisfied with the work, however, and the insurance company refused to pay. In December 2018, Color and Sam agreed that Color would receive $800 for the work, subject to final approval by the insurance company. In the past, Color had come to terms with customers only to have...
You are the manager of a shoe producer. Your company specializes in basketball shoes and a...
You are the manager of a shoe producer. Your company specializes in basketball shoes and a cleaning product for basketball shoes. While the shoes bring in more revenue ($500,000 per year), the cleaning product is a strong seller as well ($150,000 of revenue per year). You are considering a 2% decrease in the price of your company's basketball shoes. Assume that the shoes have an own price elasticity of demand of -1.8 and the shoes and cleaning product have a...
You are the manager of a shoe producer. Your company specializes in basketball shoes and a...
You are the manager of a shoe producer. Your company specializes in basketball shoes and a cleaning product for basketball shoes. While the shoes bring in more revenue ($500,000 per year), the cleaning product is a strong seller as well ($150,000 of revenue per year). You are considering a 2% decrease in the price of your company's basketball shoes. Assume that the shoes have an own price elasticity of demand of -1.8 and the shoes and cleaning product have a...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT