Marketing Decision Models, Summer 2023

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

Marketing Decision Models, Summer 2023

Time Series and Forecasting

Use the File “6 - ad_spend_forecasting_practice” to calculate the following.  Please NOTE that you’ll need to consider each Advertising Type independently since they have very different values and are on different spending scales:

a. Deseasonalize the data (doing each advertising type independently) (15%)

a. See slides, section ‘Calculating Seasonality Effect and Deseasonalizing Data’

b. Apply the methods discussed in class (25%)

a. Calculate the forecasts using three methods: 3 quarter average, moving average and Exponential Smoothing Method (use a = .4)

b. Calculate the forecasts, forecast accuracy (MSE), and forecast bias for each method

c. Which method works better?

c. Use different regression models to identify the best forecasting fit (35%)

a. Create a pivot table with advertising type as the columns, Period as Rows and Deseasonalized sales as values.  Copy and paste the data without totals below the pivot and draw the scattered graph for deseasonalized data

b. For three of the five advertising types, add trendline, Regression model, and R² (Slides: Using Regression Analysis in Projecting and Forecasting)

c. Find the best fit (i.e., change the trendline options)

i. Calculate various methods, and for moving average, try different period numbers

d. Write your final regression formula

e. Calculate forecasts for each row using the trend

f. Bring the seasonality effect by multiplying each value by the seasonality effect

d. What is your forecast for Ad Dollars for each the remainder of 2023 and 2024 and how does it differ between the three advertising types? (10%)

e. What factors may affect your forecast? Explain each factor. (15%)

Deliverable: An Excel file with all your analysis included. You must show all your work, explain all steps, and everything must be clear and easy to follow. Use screenshots and include them in the Excel file if necessary. Use different worksheets (tabs) in your Excel file for different sections.