Question

In: Operations Management

A company has the following history of sales for an item. Month Sales Volume Jan. ...

A company has the following history of sales for an item.

Month Sales Volume

Jan. 120

Feb. 90

Mar. 100

Apr. 75

May 110

Jun. 50

Jul. 75

Aug. 130

Sep. 110

Oct. 90

Compute the monthly demand forecast for April through November using a 3-month moving average. Compute the monthly demand forecast for June through November using a 5-month moving average. Compute the monthly demand forecast for April through November using a 3-month weighted moving average. Use weights of 0.5, 0.33, and 0.17, with the heavier weights on the more recent months. Compute the mean absolute deviation (MAD) for June through October for each of the methods used. Which method would you use to forecast demand for November?

Expert Solution

3-month moving average: It is calculated by the average of the sales volumes of the previous 3 months

So, the 3-month moving average of April = (Sales Volume of Jan + Sales Volume of Feb + Sales Volume of March) / 3

The 3-month moving average from April to November is calculated below:

Month	Sales Volume	3-month moving average
Jan	120
Feb	90
Mar	100
Apr	75	103.33
May	110	88.33
Jun	50	95
Jul	75	78.33
Aug	130	78.33
Sep	110	85
Oct	90	105
Nov		110

5-month moving average: It is calculated by the average of the sales volumes of the previous 5 months

So, the 5-month moving average of June = (Sales Volume of Jan + Sales Volume of Feb + Sales Volume of March Sales Volume of April + Sales Volume of May) / 5

The 5-month moving average from June to November is calculated below:

Month	Sales Volume	5-month moving average
Jan	120
Feb	90
Mar	100
Apr	75
May	110
Jun	50	99
Jul	75	85
Aug	130	82
Sep	110	88
Oct	90	95
Nov		91

3-month weighted moving average: It is calculated by below formula

The 3-month weighted moving average of April = ((Sales Volume of Jan x Weight of Jan)+ (Sales Volume of Feb x Weight of Feb) + (Sales Volume of March x Weight of March) / (Total Weight)

The weights are 0.5, 0.33 and 0.17, and the highest weight shall be allocated to the most recent months.

So in the above formula, the Weight of March shall be 0.5, Weight of Feb shall be 0.33 and Weight of Jan shall be 0.17.

Also, Total weight = 0.5 + 0.33 + 0.17 = 1

The 3-month weighted moving average from April to November is calculated below:

Month	Sales Volume	3-month Weighted moving average
Jan	120
Feb	90
Mar	100
Apr	75	100.1
May	110	85.8
Jun	50	96.75
Jul	75	74.05
Aug	130	72.7
Sep	110	98.25
Oct	90	110.65
Nov		103.4

Mean Absolute Deviation:

Mean Absolute Deviation is calculated as the average value of absolute errors(Sales - Forecast) for all months.

So, for 3-month moving average:

Month	Sales Volume	3-month moving average	Error	Absolute Error
Jan	120
Feb	90
Mar	100
Apr	75	103.33	-28.33	28.33
May	110	88.33	21.67	21.67
Jun	50	95	-45.00	45.00
Jul	75	78.33	-3.33	3.33
Aug	130	78.33	51.67	51.67
Sep	110	85	25.00	25.00
Oct	90	105	-15.00	15.00
Nov		110

MAD = (28.33 + 21.67 + 45 + 3.33 + 51.67 + 25 + 15) / 7 = 190 / 7 = 27.17

For 5-month moving average:

Month	Sales Volume	5-month moving average	Error	Absolute Error
Jan	120
Feb	90
Mar	100
Apr	75
May	110
Jun	50	99	-49	49
Jul	75	85	-10	10
Aug	130	82	48	48
Sep	110	88	22	22
Oct	90	95	-5	5
Nov		91

MAD = ( 49 + 10 + 48 + 22 + 5 ) / 5 = 134 / 5 = 26.8

For 3-month weighted moving average:

Month	Sales Volume	3-month Weighted moving average	Error	Absolute Error
Jan	120
Feb	90
Mar	100
Apr	75	100.1	-25.1	25.1
May	110	85.8	24.2	24.2
Jun	50	96.75	-46.75	46.75
Jul	75	74.05	0.95	0.95
Aug	130	72.7	57.3	57.3
Sep	110	98.25	11.75	11.75
Oct	90	110.65	-20.65	20.65
Nov		103.4

MAD = ( 25.1 + 24.2 + 46.75 + 0.95 + 57.3 + 11.75 + 20.65) / 7 = 186.7 / 7 = 26.67

For forecasting the demand for November, I would use the 3-month weighted average method. This method considers the sales volume for 3 months and also gives more weightage to recent months. The 3-month moving average just considers the average of 3 months and doesn't give any weightage to months. The 5-month moving average considers a broader range ie 5 months, which may not be very accurate to forecast and also doesn't give any weightage to months.

Due to the above reasons, I would use the 3-month weighted average.

keosha answered 6 months ago

ABC Company has the following projected sales: Month Sales Jan $5,000 Feb $14,000...

ABC Company has the following projected sales: Month Sales Jan $5,000 Feb $14,000 Mar $27,000 Apr $56,000 14% of the sales are collected in the same month. 26% of the sales are collected after one month, 42% of the sales are collected after two months, and the remainder are collected after three month. What is the amount of the April collections? Enter your answer rounded off to two decimal points. Do not enter $ or...

The following data relate to the sales of a product over an 8-month period: MONTH Jan...

The following data relate to the sales of a product over an 8-month period: MONTH Jan Feb Mar Apr May June July Aug Sales (units) 56 72 70 65 68 75 66 67 Price ($) 75 65 59 69 69 49 59 59 a) Investigate whether sales are affected more by the level of price or by the change in price of the product. b) Interpret the regression coefficient of the explanatory variable. c) Forecast sales in September if price...

1.) A company is producing a product that has the following data: volume of sales per...

1.) A company is producing a product that has the following data: volume of sales per year = 3 00,000 units selling price per unit = $10 0 variable cost = $6 0 per unit fixed costs per year = $6 00,000 book depreciation = $4 00,000 tax depreciation = $50 0,000 debt interest = $1 00,000 tax rate = 40% With the above data determine (a) Before - tax profit (b) After - tax profit (c) Break - even...

Tuscarora, Inc., a merchandising company, has the following budgeted figures: Jan Feb Mar April Sales $57,300...

Tuscarora, Inc., a merchandising company, has the following budgeted figures: Jan Feb Mar April Sales $57,300 $60,000 $83,000 $98,000 Cost of goods sold 50% of sales Required ending inventory $10,000 + 30% of next month's sales Inventory on hand on Jan 1 $30,000 Calculate the budgeted purchases for the month of January. A. $28,000 B. $26,650 C. $650 D. $56,650

Tuscarora, Inc., a merchandising company, has the following budgeted figures: Jan Feb Mar April Sales $54,000.00...

Tuscarora, Inc., a merchandising company, has the following budgeted figures: Jan Feb Mar April Sales $54,000.00 $69,000.00 $86,000.00 $95,000.00 Cost of goods sold 50% of sales Required ending inventory $10,000.00 + 20% of next month's sales Inventory on hand on Jan 1 $27,500.00 Calculate the budgeted purchases for the month of January. A. $50,800.00 B. $23,300.00 C. $3,200.00 D. $23,800.00 Nyree Company is preparing its budget for the third quarter. The cash balance on June 30 was $32,000.00. Additional budgeted...

The table below shows monthly beer sales at Gordon’s Liquor Store in 2017. Month Sales Jan...

The table below shows monthly beer sales at Gordon’s Liquor Store in 2017. Month Sales Jan 900 Feb 725 Mar 1000 Apr 800 May 750 Jun 1200 Jul 1000 Aug 1100 Sep 1250 Oct 1050 Nov 1400 Dec 1600 Jan Forecast beer sales needed for January 2018 using an exponential smoothing method α = 0.2, α = 0.4 and α = 0.9 Plot the data and identify the method to forecast the beer sales

Suppose a company has the following sales for the last six months (January – June): Month...

Suppose a company has the following sales for the last six months (January – June): Month Period Sales January 1 16 February 2 25 March 3 20 April 4 20 May 5 31 June 6 35 July 7 26 August 8 (a) Use exponential smoothing (a= 0.10) to forecast the sales for August (to 2 decimals). What is the value? Compute the MSE for the exponential smoothing method from the above question (to 2 decimals). Use a 2-period moving average...

Company X has the following income statement for the month ending September. Sales &nbs

Company X has the following income statement for the month ending September. Sales 42,000 Cost of Goods Sold: Beginning Inventory 6,000 Purchases 37,800 Cost of Goods Available for sale 43,800 Less: Ending Inventory (14,400) Cost of Goods Sold 29,400 Gross Profit 12,600 Operating Expenses 10,500 Operating Profit 2,100 Sales are made on credit and collected as follows: 60 per cent in the month after the sale is made and 35 per cent in the second month after the sale Purchases and Operating Expenses are paid as follows: 80 per...

Your company has projected the following numbers for the third quarter of the year: Month Sales...

Your company has projected the following numbers for the third quarter of the year: Month Sales Labor & Raw Materials Purchases May $ 70,000 $75,000 June $ 90,000 $90,000 July $130,000 $95,000 August $120,000 $70,000 September $100,000 $60,000 Collections occur as follows: 15% pay within the month of sale, 65% pay during the month following the sale, and 20% pay in the second month following the sale. Payments for labor and raw materials occur in the month following the purchase....

The Cambridge Company has budgeted sales revenues as follows. Jan Feb Mar__ Credit sales $45,000 $36,000...

The Cambridge Company has budgeted sales revenues as follows. Jan Feb Mar__ Credit sales $45,000 $36,000 $27,000 Cash sales 27,000 76,500 58,500 Total sales $72,000 $112,500 $85,500 Past experience indicates that 60% of the credit sales will be collected in the month of sale and the remaining 40% will be collected in the following month. Purchases of inventory are all on credit; 40% is paid in the month of purchase and 60% in the month following purchase. Budgeted inventory purchases...