The marketing department for BER Inc. has been struggling withhow they will spend their advertising budget in the coming year.You have been requested to make a recommendation. You have beenprovided the monthly values for net sales (SALES), and advertisingexpenditures for mailings (MAIL), print(PRINT), and billboards (BB)along with their sum (TOTAL = MAIL+ PRINT + BB), in the fileBER.JMP, covering the months from July 2014 through June 2020. Yourassignment is to evaluate the effectiveness of each of theadvertising components (mailings, print and billboards) withregards to net sales.

1. Determine an appropriate forecasting equation using regressionanalysis. (use the following page to show you work)

b. Final estimated equation

(State equation in the form Y_t =   bo +b₁ X₁   + b₂    X₂   + b₃ X ₃      +......   )

c. What is your recommendation regarding where (what media) tospend additional advertising dollars? Justify your answer.

c. What is your recommendation regarding where (what media) tospend additional advertising dollars? Justify your answer.

     d. Make a forecast for July 2020.Assume that the amount to be spent on the three types ofadvertising (MAIL, PRINT and BB), for July 2020, are identical towhat was spent in June 2020.

Model Building Problem 5

     d. Make a forecast for July 2020.Assume that the amount to be spent on the three types ofadvertising (MAIL, PRINT and BB), for July 2020, are identical towhat was spent in June 2020.

Model Building Problem 5

Month Sales Print Mail BB Total

07/2014   577563   1565.00  336.45   5488.75   7390.20
08/2014   542948   2092.75  352.72   3634.50   6079.97
09/2014   499066   1590.75  290.59   3608.75   5490.09
10/2014   485132   2229.25  267.92   4923.50   7420.67
11/2014   511742   1458.00  304.23   4525.50   6287.73
12/2014   631313   1370.50  270.26   6218.00   7858.76
01/2015   529692   2257.00  384.13   3451.00   6092.13
02/2015   637138   1384.25  277.97   6231.75   7893.97
03/2015   481165   1302.00  370.73   3106.00   4778.73
04/2015   580945   2066.50  265.48   5647.00   7978.98
05/2015   891906   1466.00  326.28   5426.25   7218.53
06/2015   685383   1558.75  356.62   5714.00   7629.37
07/2015   611582   1460.50  331.90   4719.50   6511.90
08/2015   613348   2302.25  312.57   5768.00   8382.82
09/2015   483268   1883.75  368.41   2756.00   5008.16
10/2015   466770   1318.50  227.59   3757.25   5303.34
11/2015   402431   1275.25  185.93   3514.00   4975.18
12/2015   401063   1899.75  269.74   3461.00   5630.49
01/2016   459923   1884.00  208.35   4377.75   6470.10
02/2016   449089   1972.25  302.45   3599.25   5873.95
03/2016   527236   2101.25  237.71   4840.50   7179.46
04/2016   607349   1462.00  310.25   6173.25   7945.50
05/2016   1019440   1622.75  382.90   5827.50   7833.15
06/2016   692305   2194.75  361.40   5928.50   8484.65
07/2016   634111   1849.00  388.14   4992.25   7229.39
08/2016   663151   2448.75  380.32   6131.00   8960.07
09/2016   535825   1751.25  388.75   3414.00   5554.00
10/2016   458804   1672.75  324.81   3371.25   5368.81
11/2016   415963   1905.25  250.31   3565.50   5721.06
12/2016   416112   1952.50  253.94   3924.00   6130.44
01/2017   518759   2392.25  261.97   5210.75   7864.97
02/2017   506471   2095.25  298.46   3971.75   6365.46
03/2017   562210   1581.75  299.79   5609.50   7491.04
04/2017   629802   2303.50  311.84   5965.75   8581.09
05/2017   1002376   2001.25  370.22   5697.50   8068.97
06/2017   692552   2465.50  407.25   5984.50   8857.25
07/2017   626601   1453.50  425.41   4289.75   6168.66
08/2017   517671   1873.25  304.05   3055.00   5232.30
09/2017   555945   2165.75  233.85   5968.00   8367.60
10/2017   459841   1724.25  341.83   3857.25   5923.33
11/2017   575057   1768.50  333.82   5766.75   7869.07
12/2017   314564   2419.00  281.04   3639.50   6339.54
01/2018   308211   2424.25  293.47   4544.25   7261.97
02/2018   324154   2397.25  265.65   5392.00   8054.90
03/2018   395687   1824.25  344.61   5340.00   7508.86
04/2018   591948   2281.25  322.29   3931.75   6535.29
05/2018   961455   1895.75  318.26   5671.50   7885.51
06/2018   606309   1482.75  361.22   5345.75   7189.72
07/2018   551793   1277.25  379.22   3969.50   5625.97
08/2018   500598   2359.75  326.13   3801.25   6487.13
09/2018   517344   1495.75  262.42   5073.25   6831.42
10/2018   468414   2135.00  320.52   2921.75   5377.27
11/2018   397045   1845.25  257.48   3208.25   5310.98
12/2018   449809   2062.75  261.89   5032.75   7357.39
01/2019   572024   2099.25  274.75   5374.75   7748.75
02/2019   507434   1282.50  359.94   2796.75   4439.19
03/2019   447931   1802.75  248.67   3388.50   5439.92
04/2019   477764   1276.00  221.05   5217.75   6714.80
05/2019   860729   1721.75  338.66   4119.75   6180.16
06/2019   642444   1879.50  271.87   5999.00   8150.37
07/2019   571933   2488.50  358.47   4290.00   7136.97
08/2019   476864   1633.00  320.58   2773.50   4727.08
09/2019   535468   1268.50  239.17   5687.00   7194.67
10/2019   471865   2325.75  315.12   3747.00   6387.87
11/2019   607926   1566.00  307.19   5858.75   7731.94
12/2019   621473   1855.50  335.55   5997.25   8188.30
01/2020   636665   1918.75  405.20   4208.25   6532.20
02/2020   615563   1301.50  331.18   5448.25   7080.93
03/2020   469326   2438.00  355.10   2735.25   5528.35
04/2020   528467   1642.50  273.02   5150.50   7066.02
05/2020   821140   2360.00  287.62   4226.25   6873.87
06/2020   467031   1816.25  308.22   3757.75   5882.22