Share
Explore BrainMass

Forecasting using Excel

ALOMEGA PHARMACEUTICALS

Alomega Pharmaceuticals is a small to mid size pharmaceutical company. The accounting department began extending its historical records by recording the sales volume for each month over the past four years, starting in January.

The company engages in various kinds of direct to consumer advertising and, until recently, has never studied the effect its advertising dollars have on sales, although some data had also been collected. Their hope was that after several months the collected data could be examined to possibly reveal any relationships that would help in future advertising expenditures.

The accounting department besides extending its historical records by recording the sales volume for each month, they also collected advertising dollars for both magazine and TV spots. They also recorded both these values lagged one and two months. This was done because some people on the executive committee thought that sales might lag advertising expenditures rather than respond in the month the ads appeared. It was also believed that sales experienced a seasonal effect and management further wondered about any trend in the sales volume. Finally, it was believed that Alomega's advertising dollars might have an effect on its major competitors' advertising budgets the following month. For each following month it was decided that competitors' advertising could be classified as: (1): little; (2): a moderate amount, or; (3): a great amount.

After a few months of data collection and analysis of past records, the accounting department completed an Excel file using the following variables:
Sales dollars
Magazine advertising dollars
TV advertising dollars
Code 1, 2, or 3 to indicate competitors' advertising efforts the following month.

The data is in the Excel file in the worksheet called data.

Problem 1 Part 1 (for certain questions you may want to copy and paste a worksheet from the Forecast file we used earlier during the semester)
1. In the Excel file, in the worksheet called MA(3), build a 3-period moving average model.

2. In the Excel file, in the worksheet called Exp(.1), build an exponential model, using α = .1.

3. In the Excel file, in the worksheet called class decomposition, build a model using the classical decomposition model (multiplicative).

4. Evaluate the three models, i.e., which one is the best and explain why. (put you answer below)

Problem 1 Part 2
1. In the Excel file, in the worksheet called regression ts, build a regression model with only the time series components—trend and seasonal components.

Below, evaluate the above model, i.e., is it a good model? If so, why, or if not, why? Consider all the appropriate tests, α  = 0.05. (explain your conclusions).
DO ALL APPROPRIATE TESTS--COMPLETELY!!!!

2. Using the model you developed in 1 (of part 2), in your worksheet forecast the next 12 months (in the future). (put these on your Regression ts worksheet)

3. Build a model regression model with only the causal variables, paper advertising, tv advertising, and competitors' advertising (compet).

Below, evaluate the above model, i.e., is it a good model? If so, why, or if not, why? Consider all the appropriate tests, α  = 0.05. (explain your conclusions).
DO ALL APPROPRIATE TESTS--COMPLETELY!!!!

4. Compare the models, moving average, exponent smoothing, classical decomposition, regression with time series and regression with causal variables, which one is the best and explain why.

Attachments

Solution Preview

Please see the attachments.
The solution provides step by step method for the computations so that you can understand the topic easily.

ALOMEGA PHARMACEUTICALS

Alomega Pharmaceuticals is a small to mid size pharmaceutical company. The accounting department began extending its historical records by recording the sales volume for each month over the past four years, starting in January.

The company engages in various kinds of direct to consumer advertising and, until recently, has never studied the effect its advertising dollars have on sales, although some data had also been collected. Their hope was that after several months the collected data could be examined to possibly reveal any relationships that would help in future advertising expenditures.

The accounting department besides extending its historical records by recording the sales volume for each month, they also collected advertising dollars for both magazine and TV spots. They also recorded both these values lagged one and two months. This was done because some people on the executive committee thought that sales might lag advertising expenditures rather than respond in the month the ads appeared. It was also believed that sales experienced a seasonal effect and management further wondered about any trend in the sales volume. Finally, it was believed that Alomega's advertising dollars might have an effect on its major competitors' advertising budgets the following month. For each following month it was decided that competitors' advertising could be classified as: (1): little; (2): a moderate amount, or; (3): a great amount.

After a few months of data collection and analysis of past records, ...

Solution Summary

The solution assists with forecasting using Excel. The expert builds a model regression for causal variables.

$2.19