This needs to be complete in excel , mess up this first question just a little to show the understanding of it.
1. The following times series shows the demand for a particular product over the past 10 months.
a. Use α = 0.2 to compute the exponential smoothing values for the time series. Compute MSE and a forecast for month 11.
b. Compare the three-month moving average forecast with the exponential smoothing forecast using α = 0.2. Which appears to provide the better forecast based on MSE?
3. A manufacturer of mp3 players is preparing to set the price on a new model. Demand is thought to depend on the price and is represented by the model
D = 2,500 – 3P
The accounting department estimates that the total costs can be represented by
C = 5,000 + 5D
Implement your model on a spreadsheet and construct a one-way data table to estimate the price for which profit is maximized.
4. The weekly price at an extended-stay hotel (renting by the week for business travelers) is $950. Operating costs average $20,000 per week, regardless of the number of rooms rented. Construct a spreadsheet model to determine the profit if 40 rooms are rented. The manager has observed that the number of rooms rented during any given week varies between 32 and 50 (the total number of rooms available).
a. Use data tables to evaluate the profit for this range of unit rentals.
b. Suppose the manager is considering lowering or increasing the weekly price by $100. How will profit be affected?
5. Malloy Milling grinds calcined alumina to a standard granular size. The mill produces two different size products form the same raw materials. Regular Grind can be produced at a rate of 10,000 pounds per hour and has a demand of 400 tons per week with a price per ton of $900. Super Grind can be produced at a rate of 6,000 pounds per hour and has a demand of 200 tons per week with a price of $1,900 per ton. A minimum of 700 tons has to be ground every week to make room in the raw material storage bins for previously purchased incoming raw material by rail. The mill operates 24/7 for a total of 168 hours/week.
a. Develop and solve a linear optimization model to determine the number of tons of each product to produce each week to maximize revenue.
b. What impact will changing the required minimum number of tons per week (currently 700) have on the solution? Explain using the Sensitivity Report.
c. If the per ton for Regular Grind is increased to $1,100, how will the solution be affected?
d. If the price per ton for Super grind is decreased to $1400 because of low demand, how will the solution change?