Answered Essay: Looking for help with a textbook case study. I tried to create the model in Excel and I just can’t figure it

Looking for help with a textbook case study. I tried to create the model in Excel and I just can’t figure it out – I really want to know the steps to creating the forecast model so please include formulas and result screen shots.

An important part of planning manufacturing capacity is having a good forecast of sales. Elizabeth Burke is interested in forecasting sales of mowers in each marketing region to assess future changes in market share. Develop a historical analogy, time series and regression (seasonality and trend) forecasting models for mower unit sales data.

Mower Unit Sales
Month NA SA Europe Pacific China World
Jan-10 6000 200 720 100 0 7020
Feb-10 7950 220 990 120 0 9280
Mar-10 8100 250 1320 110 0 9780
Apr-10 9050 280 1650 120 0 11100
May-10 9900 310 1590 130 0 11930
Jun-10 10200 300 1620 120 0 12240
Jul-10 8730 280 1590 140 0 10740
Aug-10 8140 250 1560 130 0 10080
Sep-10 6480 230 1590 130 0 8430
Oct-10 5990 220 1320 120 0 7650
Nov-10 5320 210 990 130 0 6650
Dec-10 4640 180 660 140 0 5620
Jan-11 5980 210 690 140 0 7020
Feb-11 7620 240 1020 150 0 9030
Mar-11 8370 250 1290 140 0 10050
Apr-11 8830 290 1620 150 0 10890
May-11 9310 330 1650 130 0 11420
Jun-11 10230 310 1590 140 0 12270
Jul-11 8720 290 1560 150 0 10720
Aug-11 7710 270 1530 140 0 9650
Sep-11 6320 250 1590 150 0 8310
Oct-11 5840 250 1260 160 0 7510
Nov-11 4960 240 900 150 0 6250
Dec-11 4350 210 660 150 0 5370
Jan-12 6020 220 570 160 0 6970
Feb-12 7920 250 840 150 0 9160
Mar-12 8430 270 1110 160 0 9970
Apr-12 9040 310 1500 170 0 11020
May-12 9820 360 1440 160 0 11780
Jun-12 10370 330 1410 170 0 12280
Jul-12 9050 310 1440 160 0 10960
Aug-12 7620 300 1410 170 0 9500
Sep-12 6420 280 1350 180 0 8230
Oct-12 5890 270 1080 180 0 7420
Nov-12 5340 260 840 190 0 6630
Dec-12 4430 230 510 180 0 5350
Jan-13 6100 250 480 200 0 7030
Feb-13 8010 270 750 190 0 9220
Mar-13 8430 280 1140 200 0 10050
Apr-13 9110 320 1410 210 0 11050
May-13 9730 380 1340 190 0 11640
Jun-13 10120 360 1360 200 0 12040
Jul-13 9080 320 1410 200 0 11010
Aug-13 7820 310 1490 210 0 9830
Sep-13 6540 300 1310 220 0 8370
Oct-13 6010 290 980 210 0 7490
Nov-13 5270 270 770 220 0 6530
Dec-13 5380 260 430 230 0 6300
Jan-14 6210 270 400 200 0 7080
Feb-14 8030 280 750 190 0 9250
Mar-14 8540 300 970 210 0 10020
Apr-14 9120 340 1310 220 5 10995
May-14 9570 390 1260 200 16 11436
Jun-14 10230 380 1240 210 22 12082
Jul-14 9580 350 1300 230 26 11486
Aug-14 7680 340 1250 220 14 9504
Sep-14 6870 320 1210 220 15 8635
Oct-14 5930 310 970 230 11 7451
Nov-14 5260 300 650 240 3 6453
Dec-14 4830 290 300 230 1 5651

Expert Answer

There are five steps involved in time series forecasting using regression (seasonality and trend).

Step 1. Plot the historical sales chart and display linear trend line and equation on the chart.

World
Year Month Period (x) Sales
2010 1 1              7,020
2 2              9,280
3 3              9,780
4 4            11,100
5 5            11,930
6 6            12,240
7 7            10,740
8 8            10,080
9 9              8,430
10 10              7,650
11 11              6,650
12 12              5,620
2011 1 13              7,020
2 14              9,030
3 15            10,050
4 16            10,890
5 17            11,420
6 18            12,270
7 19            10,720
8 20              9,650
9 21              8,310
10 22              7,510
11 23              6,250
12 24              5,370
2012 1 25              6,970
2 26              9,160
3 27              9,970
4 28            11,020
5 29            11,780
6 30            12,280
7 31            10,960
8 32              9,500
9 33              8,230
10 34              7,420
11 35              6,630
12 36              5,350
2013 1 37              7,030
2 38              9,220
3 39            10,050
4 40            11,050
5 41            11,640
6 42            12,040
7 43            11,010
8 44              9,830
9 45              8,370
10 46              7,490
11 47              6,530
12 48              6,300
2014 1 49              7,080
2 50              9,250
3 51            10,020
4 52            10,995
5 53            11,436
6 54            12,082
7 55            11,486
8 56              9,504
9 57              8,635
10 58              7,451
11 59              6,453
12 60              5,651

Step 2: After plotting the trend line in the graph, calculate the Trend line data using the regression equation as determined by the trend line plot. The regression equation in this case for the World Sales data is y = -9.8901x + 9449.7

Step 3: Determine Seasonal ratio for each month. Seasonal ratio is Actual sales / Trend line.

Step 4: Determine Seasonal index for each month. Seasonal index is calculated only for the first 12 months of the time series. It is calculated as average of the seasonal ratio of each corresponding month of every year. For example, Seasonal index for January is calculated as average of seasonal ratio of January month of each year.

Step 5: Seasonally adjusted forecast is calculated as product of trend line and the seasonal index of the corresponding month.

Time series regression forecasting (with seasonality and trend) of the World Sales is following.

Forecasting of World Sales for year 2015 using the time series regression forecasting model as developed above, is as follows

World Trend Seasonal Seasonal Seasonally adjusted
Year Month Period (x) Sales Line (y) Ratio Index Forecast
2015 1 61            8,846                –         0.764 6,755
2 62            8,837                –         1.000 8,835
3 63            8,827                –         1.087 9,592
4 64            8,817                –         1.201 10,587
5 65            8,807                –         1.271 11,191
6 66            8,797                –         1.331 11,711
7 67            8,787                –         1.202 10,564
8 68            8,777                –         1.064 9,335
9 69            8,767                –         0.921 8,071
10 70            8,757                –         0.824 7,212
11 71            8,748                –         0.715 6,250
12 72            8,738                –         0.623 5,441

The formulas are following

Buy Essay
Calculate your paper price
Pages (550 words)
Approximate price: -

Help Me Write My Essay - Reasons:

Best Online Essay Writing Service

We strive to give our customers the best online essay writing experience. We Make sure essays are submitted on time and all the instructions are followed.

Our Writers are Experienced and Professional

Our essay writing service is founded on professional writers who are on stand by to help you any time.

Free Revision Fo all Essays

Sometimes you may require our writers to add on a point to make your essay as customised as possible, we will give you unlimited times to do this. And we will do it for free.

Timely Essay(s)

We understand the frustrations that comes with late essays and our writers are extra careful to not violate this term. Our support team is always engauging our writers to help you have your essay ahead of time.

Customised Essays &100% Confidential

Our Online writing Service has zero torelance for plagiarised papers. We have plagiarism checking tool that generate plagiarism reports just to make sure you are satisfied.

24/7 Customer Support

Our agents are ready to help you around the clock. Please feel free to reach out and enquire about anything.

Try it now!

Calculate the price of your order

Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

HOW OUR ONLINE ESSAY WRITING SERVICE WORKS

Let us write that nagging essay.

STEP 1

Submit Your Essay/Homework Instructions

By clicking on the "PLACE ORDER" button, tell us your requires. Be precise for an accurate customised essay. You may also upload any reading materials where applicable.

STEP 2

Pick A & Writer

Our ordering form will provide you with a list of writers and their feedbacks. At step 2, its time select a writer. Our online agents are on stand by to help you just in case.

STEP 3

Editing (OUR PART)

At this stage, our editor will go through your essay and make sure your writer did meet all the instructions.

STEP 4

Receive your Paper

After Editing, your paper will be sent to you via email.

See all services
× How can I help you?