Great Deal! Get Instant $10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

Page 1 of 3 M6A1 Instructions Background It is that time of year that managers of all types love to hate: budgeting for the next year. Connie Smith needs forecasts of sales for the next year she can...

1 answer below »
Page 1 of 3
M6A1 Instructions
Background
It is that time of year that managers of all types love to hate: budgeting for the next year. Connie Smith
needs forecasts of sales for the next year she can build her budgets around. She would also like some idea of
what 2018 could look like, though she realizes the forecasts that far in the future are a bit uncertain. She asks
you to develop forecasts for 2017 as a beginning.
M6A1 Requirements
Download Fast Technologies Database in the Excel workbook M6A1_Data
Start Excel. Open the workbook M6A1-Fast Data. Immediately save the workbook with a new name. Use your
name and include the assignment name, e.g. Wright-Dawn-M6A1. This will ensure you have a good copy in
case you make mistakes. It will also make your instructor happy when grading your work, which is a good
thing.
1. Using the Fast 2000 GB Sales data for January 2012 through Dec 2016:
a. Prepare a scatter plot (with lines instead of data points) with Month as the predictor (x) variable
and World Sales as the response (y) variable.
How to Do It 1a.1: Excel Scatter Plot https:
youtu.be/QdVei772BgY
i. Add a trend line with the linear regression equation and R2.
How to Do It 1a.2: Excel Trendline & Equation https:
youtu.be/mmbTgrNCKcc
ii. What does the plot communicate about the Fast 2000 GB World sales pattern?
https:
thestatsfiles.com/wp-content/uploads/2018/06/M6A1_Data.xlsx
https:
youtu.be/QdVei772BgY
https:
youtu.be/mmbTgrNCKcc
Page 2 of 3
1b. Using the regression equation from 1a:
i. Forecast Fast 2000 GB World Sales for each month from January 2012 through December 2016.
How to Do It 1b.1: Excel Forecast Basics https:
youtu.be/4driMYqddFs
ii. Find MAD, MSE, RMSE, and MAPE for your regression forecast.
How to Do It 1b.2: Use Excel to Calculate MAD, MSE, RMSR & MAPE https:
youtu.be/H_N5_vxs8Tg
1c. Using a Moving Average (MA) with k=3 and k=9, develop forecasts for Fast 2000 GB World sales for
each month from January 2012 through December 2016. You can either develop the MA using basic
Excel functions and formulas in Evans or use the Data Analysis tool pack.
How to do It 1c.1: Excel Forecasts: Moving Average https:
youtu.be/W66qDonMoQo
i. Create a single plot of the actual data and the two MA forecasts.
How to do It 1c.2: Plot Two Data Sets on One Graph https:
youtu.be/AytFoe1tdsM
ii. Find MAD, MSE, RMSE, and MAPE for the MA forecasts.
1d. Using Exponential Smoothing (ES) with α=0.3 and α=0.9, develop forecasts for Fast 2000 GB World
sales for each month from January 2012 through January 2016. You can either develop the ES using
asic Excel functions and formulas in Evans or use the Data Analysis tool pack.
How to Do It 1d.1 Simple Exponential Smoothing https:
youtu.be/wq0tCugdvfs
i. Create a single plot of the actual data and the two ES forecasts.
ii. Find MAD, MSE, RMSE, and MAPE for two ES forecasts.
1e. Create a summary table of the e
or metrics for part 1. Which e
or metric do you think is best?
2. Using the Fast 2000 GB World Sales data:
a. Develop regression forecasts for seasonality-only for each month Jan 2012 through Dec 2016.
How to do it 2a: Seasonality https:
youtu.be/PgGWlv-P2FI
i. How does the adjusted R2 compare to the R2 of the regression model in #1?
ii. Create a line plot showing the actual sales and forecast sales for the months Jan 2012 through
Dec 2016 using the seasonality-only regression model.
iii. Find MAPE for this model.
https:
youtu.be/4driMYqddFs
https:
youtu.be/H_N5_vxs8Tg
https:
youtu.be/W66qDonMoQo
https:
youtu.be/AytFoe1tdsM
https:
youtu.be/wq0tCugdvfs
https:
youtu.be/PgGWlv-P2FI
Page 3 of 3
Note: if the trend component of your data is very strong, the R2 may drop or be very low when you are
only regressing the seasonality component. The p-values of the seasonality-only regression coefficients
may also be high and not significant. When we add in the trend component, the characteristics of the
model will improve.
Important: You must complete part 2 using only basic Excel tools and the Data Analytics, i.e. following
the process shown in the Part 2 How-to videos. You may not use the Excel 2016 Forecast sheet or
function to do this.
2b. Develop regression forecasts including seasonality and trend for each month Jan 2012 through Dec
2016 for Fast 2000 GB World sales.
How to do it 2b: Seasonality & Trend https:
youtu.be/x5eB2U3rDtg
i. How does the adjusted R2 compare to the R2 of the seasonality only regression model?
ii. Create a line plot showing the actual and forecast sales from Jan 2012 to Dec 2016 using the
seasonality and trend regression model.
iii. Calculate MAPE for this model’s forecasts.
2c. Check to see if any of the dummy variables in the 2b model are not statistically significant. If so, rerun the
egression model with the non-significant variables removed and see if R2 and MAPE improve.
2d. Select the best regression model in part 2 (2a, 2b, or 2c) and develop forecast values for the twelve months Jan
2017 to Dec 2017.
How to do it 2d: Seasonality & Trend Forecast https:
youtu.be/HQr3_nrqiaU
i. Prepare a final graph showing the actual data and the forecast values for the 72 months Jan 2012 to
Dec 2017.
3. MAPE is useful for comparing forecasts because the measurement scale is eliminated (response
variables of different magnitudes and units).
a. Prepare a table (similar to the format of the table in 1e above) of all the forecast models in this
assignment but showing just the MAPE values.
. What do you conclude about the forecasting ability of the models?
4. Organize and format your Excel file to make it easy for Connie (and your instructor) to find and
understand your work and results. Make sure the tabs are logically named and located. Include an Index
tab at the beginning and put hyperlinks to the problem solutions tabs.
Optional
Excel 2016 has tools (FORECAST.ETS and the Forecast ri
on) that enable you to model both seasonality
and trend in very few clicks. Excel 2016 Forecast Tool https:
youtu.be/SKZcd2xJvjA
Consider upgrading to this Excel version if you think you will do forecasting after finishing this course.
https:
youtu.be/x5eB2U3rDtg
https:
youtu.be/HQr3_nrqiaU
https:
youtu.be/SKZcd2xJvjA
qfile_636884490189731170_125345_1.pdf qfile_636884490189731170_125345_2.xlsx
Answered Same Day Mar 17, 2021

Solution

Mohammad Wasif answered on Mar 27 2021
132 Votes
1a
    Month    World Sales    Trend    Regression    Forecast fct    Trend fct    Trend    Intercept    -55431
    Jan-12    1,592    1,395.69190    1,393.32486    1393.3248635743    1393.3248635743        Slope    1.3891
    Feb-12    1,711    1,438.75400    1,436.38544    1436.3854442448    1436.3854442448
    Mar-12    1,810    1,479.03790    1,476.66792    1476.6679229365    1476.6679229365    Forecast    Intercept    -55431.3620606051
    Apr-12    1,867    1,522.10000    1,519.72850    1519.728503607    1519.728503607        Slope    1.3890509894
    May-12    1,779    1,563.77300    1,561.40003    1561.4000332882    1561.4000332882
    Jun-12    1,740    1,606.83510    1,604.46061    1604.4606139586    1604.4606139586
    Jul-12    1,826    1,648.50810    1,646.13214    1646.1321436398    1646.1321436398
    Aug-12    1,695    1,691.57020    1,689.19272    1689.1927243103    1689.1927243103    SUMMARY OUTPUT
    Sep-12    1,681    1,734.63230    1,732.25330    1732.2533049808    1732.2533049808
    Oct-12    1,663    1,776.30530    1,773.92483    1773.9248346619    1773.9248346619    Regression Statistics
    Nov-12    1,825    1,819.36740    1,816.98542    1816.9854153324    1816.9854153324    Multiple R    0.9076224211
    Dec-12    1,720    1,861.04040    1,858.65695    1858.6569450136    1858.6569450136    R Square    0.8237784592
    Jan-13    1,761    1,904.10250    1,901.71753    1901.7175256841    1901.7175256841    Adjusted R Square    0.8207401568
    Feb-13    2,035    1,947.16460    1,944.77811    1944.7781063545    1944.7781063545    Standard E
or    344.3993496493
    Mar-13    2,142    1,986.05940    1,983.67153    1983.6715340569    1983.6715340569    Observations    60
    Apr-13    2,340    2,029.12150    2,026.73211    2026.7321147274    2026.7321147274
    May-13    2,280    2,070.79450    2,068.40364    2068.4036444086    2068.4036444086    ANOVA
    Jun-13    2,271    2,113.85660    2,111.46423    2111.4642250791    2111.4642250791        df    SS    MS    F    Significance F
    Jul-13    2,154    2,155.52960    2,153.13575    2153.1357547602    2153.1357547602    Regression    1    32159114.0850812    32159114.0850812    271.1311592862    1.56539690643411E-23
    Aug-13    2,146    2,198.59170    2,196.19634    2196.1963354307    2196.1963354307    Residual    58    6879432.89825211    118610.912038829
    Sep-13    2,085    2,241.65380    2,239.25692    2239.2569161012    2239.2569161012    Total    59    39038546.9833333
    Oct-13    1,970    2,283.32680    2,280.92845    2280.9284457823    2280.9284457823
    Nov-13    1,936    2,326.38890    2,323.98903    2323.9890264528    2323.9890264528        Coefficients    Standard E
or    t Stat    P-value    Lower 95%    Upper 95%    Lower 95.0%    Upper 95.0%
    Dec-13    1,850    2,368.06190    2,365.66056    2365.660556134    2365.660556134    Intercept    -55431.3620606051    3527.0220775214    -15.7161936734    1.43628371103035E-22    -62491.4638201461    -48371.2603010642    -62491.4638201461    -48371.2603010642
    Jan-14    2,000    2,411.12400    2,408.72114    2408.7211368045    2408.7211368045    Month    1.3890509894    0.0843584269    16.4660608309    1.56539690643412E-23    1.2201892513    1.5579127275    1.2201892513    1.5579127275
    Feb-14    2,324    2,454.18610    2,451.78172    2451.781717475    2451.781717475
    Mar-14    2,510    2,493.08090    2,490.67515    2490.6751451774    2490.6751451774
    Apr-14    2,672    2,536.14300    2,533.73573    2533.7357258479    2533.7357258479
    May-14    2,780    2,577.81600    2,575.40726    2575.407255529    2575.407255529
    Jun-14    2,813    2,620.87810    2,618.46784    2618.4678361995    2618.4678361995
    Jul-14    2,716    2,662.55110    2,660.13937    2660.1393658806    2660.1393658806
    Aug-14    2,581    2,705.61320    2,703.19995    2703.1999465511    2703.1999465511
    Sep-14    2,476    2,748.67530    2,746.26053    2746.2605272216    2746.2605272216
    Oct-14    2,317    2,790.34830    2,787.93206    2787.9320569028    2787.9320569028
    Nov-14    2,324    2,833.41040    2,830.99264    2830.9926375733    2830.9926375733
    Dec-14    2,080    2,875.08340    2,872.66417    2872.6641672544    2872.6641672544
    Jan-15    2,202    2,918.14550    2,915.72475    2915.7247479249    2915.7247479249
    Feb-15    2,540    2,961.20760    2,958.78533    2958.7853285954    2958.7853285954
    Mar-15    2,867    3,000.10240    2,997.67876    2997.6787562978    2997.6787562978
    Apr-15    3,348    3,043.16450    3,040.73934    3040.7393369683    3040.7393369683
    May-15    3,550    3,084.83750    3,082.41087    3082.4108666494    3082.4108666494
    Jun-15    3,432    3,127.89960    3,125.47145    3125.4714473199    3125.4714473199
    Jul-15    3,400    3,169.57260    3,167.14298    3167.1429770011    3167.1429770011
    Aug-15    3,261    3,212.63470    3,210.20356    3210.2035576716    3210.2035576716
    Sep-15    3,209    3,255.69680    3,253.26414    3253.2641383421    3253.2641383421
    Oct-15    3,132    3,297.36980    3,294.93567    3294.9356680232    3294.9356680232
    Nov-15    3,027    3,340.43190    3,337.99625    3337.9962486937    3337.9962486937
    Dec-15    2,777    3,382.10490    3,379.66778    3379.6677783748    3379.6677783748
    Jan-16    2,821    3,425.16700    3,422.72836    3422.7283590453    3422.7283590453
    Feb-16    3,209    3,468.22910    3,465.78894    3465.7889397158    3465.7889397158
    Mar-16    3,553    3,508.51300    3,506.07142    3506.0714184076    3506.0714184076
    Apr-16    3,820    3,551.57510    3,549.13200    3549.1319990781    3549.1319990781
    May-16    4,133    3,593.24810    3,590.80353    3590.8035287592    3590.8035287592
    Jun-16    4,476    3,636.31020    3,633.86411    3633.8641094297    3633.8641094297
    Jul-16    4,436    3,677.98320    3,675.53564    3675.5356391108    3675.5356391108
    Aug-16    4,256    3,721.04530    3,718.59622    3718.5962197814    3718.5962197814
    Sep-16    4,067    3,764.10740    3,761.65680    3761.6568004519    3761.6568004519
    Oct-16    3,890    3,805.78040    3,803.32833    3803.328330133    3803.328330133
    Nov-16    3,816    3,848.84250    3,846.38891    3846.3889108035    3846.3889108035
    Dec-16    3,717    3,890.51550    3,888.06044    3888.0604404846    3888.0604404846
World Sales    40909    40940    40969    41000    41030    41061    41091    41122    41153    41183    41214    41244    41275    41306    41334    41365    41395    41426    41456    41487    41518    41548    41579    41609    41640    41671    41699    41730    41760    41791    41821    41852    41883    41913    41944    41974    42005    42036    42064    42095    42125    42156    42186    42217    42248    42278    42309    42339    42370    42401    42430    42461    42491    42522    42552    42583    42614    42644    42675    42705    1592    1711    1810    1867    1779    1740    1826    1695    1681    1663    1825    1720    1761    2035    2142    2340    2280    2271    2154    2146    2085    1970    1936    1850    2000    2324    2510    2672    2780    2813    2716    2581    2476    2317    2324    2080    2202    2540    2867    3348    3550    3432    3400    3261    3209    3132    3027    2777    2821    3209    3553    3820    4133    4476    4436    4256    4067    3890    3816    3717    
1
    Month    World Sales    Actual    Forecast    Absoulte Value of E
or    Square of E
or    Abs Value of E
ors Divided by Actual Value
    Jan-12    1,592    1,395.69    1,393.32    2.3670    5.6029    0.0017
    Feb-12    1,711    1,438.75    1,436.39    2.3686    5.6101    0.0016
    Mar-12    1,810    1,479.04    1,476.67    2.3700    5.6168    0.0016
    Apr-12    1,867    1,522.10    1,519.73    2.3715    5.6240    0.0016
    May-12    1,779    1,563.77    1,561.40    2.3730    5.6310    0.0015
    Jun-12    1,740    1,606.84    1,604.46    2.3745    5.6382    0.0015
    Jul-12    1,826    1,648.51    1,646.13    2.3760    5.6452    0.0014
    Aug-12    1,695    1,691.57    1,689.19    2.3775    5.6524    0.0014
    Sep-12    1,681    1,734.63    1,732.25    2.3790    5.6596    0.0014
    Oct-12    1,663    1,776.31    1,773.92    2.3805    5.6666    0.0013
    Nov-12    1,825    1,819.37    1,816.99    2.3820    5.6739    0.0013
    Dec-12    1,720    1,861.04    1,858.66    2.3835    5.6809    0.0013
    Jan-13    1,761    1,904.10    1,901.72    2.3850    5.6881    0.0013
    Feb-13    2,035    1,947.16    1,944.78    2.3865    5.6954    0.0012
    Mar-13    2,142    1,986.06    1,983.67    2.3879    5.7019    0.0012
    Apr-13    2,340    2,029.12    2,026.73    2.3894    5.7092    0.0012
    May-13    2,280    2,070.79    2,068.40    2.3909    5.7162    0.0012
    Jun-13    2,271    2,113.86    2,111.46    2.3924    5.7235    0.0011
    Jul-13    2,154    2,155.53    2,153.14    2.3938    5.7305    0.0011
    Aug-13    2,146    2,198.59    2,196.20    2.3954    5.7378    0.0011
    Sep-13    2,085    2,241.65    2,239.26    2.3969    5.7451    0.0011
    Oct-13    1,970    2,283.33    2,280.93    2.3984    5.7521    0.0011
    Nov-13    1,936    2,326.39    2,323.99    2.3999    5.7594    0.0010
    Dec-13    1,850    2,368.06    2,365.66    2.4013    5.7665    0.0010
    Jan-14    2,000    2,411.12    2,408.72    2.4029    5.7738    0.0010
    Feb-14    2,324    2,454.19    2,451.78    2.4044    5.7811    0.0010
    Mar-14    2,510    2,493.08    2,490.68    2.4058    5.7877    0.0010
    Apr-14    2,672    2,536.14    2,533.74    2.4073    5.7950    0.0009
    May-14    2,780    2,577.82    2,575.41    2.4087    5.8020    0.0009
    Jun-14    2,813    2,620.88    2,618.47    2.4103    5.8094    0.0009
    Jul-14    2,716    2,662.55    2,660.14    2.4117    5.8165    0.0009
    Aug-14    2,581    2,705.61    2,703.20    2.4133    5.8238    0.0009
    Sep-14    2,476    2,748.68    2,746.26    2.4148    5.8311    0.0009
    Oct-14    2,317    2,790.35    2,787.93    2.4162    5.8382    0.0009
    Nov-14    2,324    2,833.41    2,830.99    2.4178    5.8456    0.0009
    Dec-14    2,080    2,875.08    2,872.66    2.4192    5.8527    0.0008
    Jan-15    2,202    2,918.15    2,915.72    2.4208    5.8600    0.0008
    Feb-15    2,540    2,961.21    2,958.79    2.4223    5.8674    0.0008
    Mar-15    2,867    3,000.10    2,997.68    2.4236    5.8740    0.0008
    Apr-15    3,348    3,043.16    3,040.74    2.4252    5.8814    0.0008
    May-15    3,550    3,084.84    3,082.41    2.4266    5.8885    0.0008
    Jun-15    3,432    3,127.90    3,125.47    2.4282    5.8959    0.0008
    Jul-15    3,400    3,169.57    3,167.14    2.4296    5.9031    0.0008
    Aug-15    3,261    3,212.63    3,210.20    2.4311    5.9105    0.0008
    Sep-15    3,209    3,255.70    3,253.26    2.4327    5.9178    0.0007
    Oct-15    3,132    3,297.37    3,294.94    2.4341    5.9250    0.0007
    Nov-15    3,027    3,340.43    3,338.00    2.4357    5.9324    0.0007
    Dec-15    2,777    3,382.10    3,379.67    2.4371    5.9396    0.0007
    Jan-16    2,821    3,425.17    3,422.73    2.4386    5.9470    0.0007
    Feb-16    3,209    3,468.23    3,465.79    2.4402    5.9544    0.0007
    Mar-16    3,553    3,508.51    3,506.07    2.4416    5.9613    0.0007
    Apr-16    3,820    3,551.58    3,549.13    2.4431    5.9687    0.0007
    May-16    4,133    3,593.25    3,590.80    2.4446    5.9759    0.0007
    Jun-16    4,476    3,636.31    3,633.86    2.4461    5.9834    0.0007
    Jul-16    4,436    3,677.98    3,675.54    2.4476    5.9906    0.0007
    Aug-16    4,256    3,721.05    3,718.60    2.4491    5.9980    0.0007
    Sep-16    4,067    3,764.11    3,761.66    2.4506    6.0054    0.0007
    Oct-16    3,890    3,805.78    3,803.33    2.4521    6.0126    0.0006
    Nov-16    3,816    3,848.84    3,846.39    2.4536    6.0201    0.0006
    Dec-16    3,717    3,890.52    3,888.06    2.4551    6.0273    0.0006
                    144.6618    348.8240    0.0595
        n    60
        MAD    2.41
        MSE    5.81
        RMSE    2.41
        MAPE    0.10
1c
    Month    World Sales    k = 3    k = 9    E
or    E
or^2    Abs Value of E
ors Divided by Actual Value    E
or    E
or^2    Abs Value of E
ors Divided by Actual Value
    Jan-12    1,592            k = 3            k =...
SOLUTION.PDF
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here
Attach File