Homework #7
You work for a pharmaceutical company that produces 6 products at their plant.
One of these products (Prod 5) is used to treat a seasonal illness. Demand for all other products is relatively stable.
- You want to develop a method to forecast the demand for Prod 5.
- a. You have gathered the weekly demand information for the last 3 seasons (Sept –Apr) and the average temperature for those weeks (see the Datafile – Data worksheet).
i. Does the temperature appear to be a good indicator for demand? (HINT: Scatter plot)
ii. If so, develop a projection method based on the expected temperature for the coming week (production occurs one week prior to demand). (HINT: Trendline, Regression formula)
iii. If the average temperature in the coming week is expected to be 32°, what is your forecast for demand?
- You have now been asked to put together a production schedule that will maximize the variable profit for your plant. Key information about each of the products is shown in the datafile (at the top of the worksheet called “Solver”).
- a. Production of each pound of finished product requires labor and raw material. The hours and labor needed for each product are shown (rows 2&3). For example, product 1 requires 6 hours of labor and 3.2 pounds of raw material to produce one pound of finished product (FP).
- b. The unit price and variable cost per pound of finished product are shown. Based on this calculate the profit per pound (row 6). (HINT: Profit = Unit Price minus Variable Cost)
- c. The total demand is also included (row 7) – this is the total demand for the product, your company is not the only manufacturer so they do not need to supply the full demand, but they should not produce more than the total demand in any given week. For Prod 5, use the demand that you calculated in Step 1.
- d. As a starting point, the pounds of each product that were produced last week are shown (row 9).
- e. Calculate the Total profit for each product (HINT: Units produced times profit per unit).
- f. Then calculate the total units produced and total profit by summing the values across all the products.
- g. Calculate the total labor hours used and total materials used in cells B15 and B16. (HINT: =SUMPRODUCT function will be useful here).
- You are now ready to find an optimal production schedule that will maximize your profits.
- a. Let’s think about the constraints that we need to establish:
• You have 45,000 hours of labor and 16,000 pounds of raw material available.
• Production should not exceed total demand.
• Ensure that all production quantities are non-negative
• Ensure that no partial pounds of finished product are made
(HINT: make a new constraint and look for “int” in the middle box, this means to make the solution an integer / whole number).
- b. You want to find optimal solutions subject to the above constraints for a couple of scenarios. In rows 22-24, make a note of the optimal production quantities for each of scenario (HINT: Copy – Paste Values after each scenario) as you will use these quantities to summarize the scenarios in the next step.
• No minimum production for each product (Scenario: No Min).
• You realize that if you don’t produce at least some of each product there could end up being an overall supply shortage, therefore explore the following additional scenarios.
i) Minimum production for each product of 1000 pounds (Scenario: Min 1000).
ii) Minimum production for each product equal to 10% of total demand (Scenario: Min 10% of Demand)
- Now, using Scenario Manager, prepare a summary of each of these 3 scenarios, showing
i. the production quantity for each product
ii. the resulting total variable profit for the plant
iii. total pounds produced
iv. total labor used
v. total material used (HINT: you can add additional results by separating them by a comma in the results cells box).
- a. Apply range names to the cells that will appear on the summary to improve the readability of the summary.
- b. Which scenario will you recommend to management?
- What is the limiting constraint? In other words, which limitation prevents us from making more product?
- a. If our goal is still to maximize profit and we removed that constraint, how much more of the constrained item would we need to acquire (i.e. labor hours or pounds of raw material) for the revised production plan? (HINT: Remove that constraint).
- b. If that item cost $10 per unit, would it make sense to do it? (HINT: How would profit (vs. the recommended scenario) be impacted by this decision?)
- Finally, prepare a waterfall chart to show the profit contribution of each product –assuming Scenario: Min 10% of Demand.
