Lab Report

SCM 310: Individual Project

Fall 11

The project is due on Nov. 16, Wednesday by Midnight (11:59 PM).

• Download and work on the Excel template for the project

• Submit your completed project to the MASTER website where you find this document

• Show all your work

• Highlight the answers

• Round the number off at two digits after the decimal point unless indicated otherwise (e.g. 19.44444 = 19.44)

• Make sure to save the document as an Excel file, ending in .xls or .xlsx. Double check to make sure the submitted document will open

The project is worth 100 (regular) + 8 (extra) points. 5 (out of 100) points are for correct procedure and clarity.

Question 1 (20 points): The worksheet Q1 contains data on the demand of a particular product for each month of the previous year. Work on the following three questions:

a) Add a linear trend line to estimate the relationship between time and demand of this product. What is the forecasted demand for March of this year? Note your answer should include a graph.

b) Calculate the forecasted demands for the past 12 months using 1) the estimates from the linear trend line; 2) the exponential smoothing method with α=0.6.

c) Calculate the Mean Absolute Deviation (MAD) and the Mean Absolute Percentage Deviation (MAPD) for the forecasts you derived from BOTH methods in the previous year. According to MAD, which forecasting model should be chosen? According to MAPD, which model should be chosen? Type your answers/explanations below your analysis on worksheet Q1.

Question 2 (10 points): The owner of a relatively small business is trying to determine the sales level of the company’s single product. The fixed cost for manufacturing this product is $24,000 per year. Assume that the overhead spreads evenly over the 12 months in a year. The unit cost of producing this product is $50. The owner believes that the unit selling price is best described by the expression 125 – 0.01D, where D...