Statistics for Financial Decisions Module Assessment

Learning Outcomes -

1. Analyse and present data graphically using spreadsheet software (Excel).

2. Critically evaluate summary statistics against suitable benchmarks.

3. Apply judgement to select appropriate methods of data analysis drawing on knowledge of regression analysis, probability, probability distributions and sampling distributions.

4. Select and apply a range of data analysis tools to inform problem solving and decision making.

5. Conduct quantitative research both individually and as part of a team and articulate and present findings to a wide range of stakeholders, from accounting and non- accounting backgrounds.

Assignment

Introduction:

The Board of Directors of Schmeckt Gut would like to forecast sales figures for the Federated Islands for this year (2016) based on a data set that has collected information over the past 25 years.

The dataset which is provided in the EXCEL file is for the Federated Islands and includes other variables.

Assume that the development of the sales of Energy Bars (in US$) in a country is highly dependent on:

• The development of the income of that country approximated by GDP data in US$
• The development of prices approximated by an average price index (in %)
• The population development (15-65 years of age)
• Satisfaction of customers with the product which is approximated with a survey score - this is the average result of a customer satisfaction survey (0=not satisfies, 10=very satisfied)
• The advertisement of energy bars - number of average advertisements, and
• The Number of stores where the energy bars can be purchased.

If we would rewrite this relationship with a regression:

Sales_t = α₀ + α₁GDP_t + α₂PriceIndex_t + α₃Population_t +α₄Satisfaction_t + α₅Advertisement_t + α₆Stores_t + ε_t

We are now interested to estimate what a 1% change in these figures would have on the development of the sales.

A common approach in econometric analysis is to apply the natural logs (ln). Doing so will return coefficients that estimate a percent change in your dependent variable for a unit change in your independent variable. For example, a coefficient of 0.25 for the GDP variable implies that a 1% change in GDP will change sales by 0.25%.

The model in log-linear form is the presented as follows:

lnSales_t = α₀ + α₁lnGDP_t + α₂lnPriceIndex_t + α₃lnPopulation_t +α₄lnSatisfaction_t + α₅lnAdvertisement_t + α₆lnStores_t + ε_t

In EXCEL, for a transformation of data into natural logs, simply apply '=ln(data)'.

Your tasks:

(a) Provide a statistical overview of the data provided in the EXCEL file.

(b) Conduct a correlation analysis between the sales results, and:

(i) Satisfaction scores

(ii) Number of advertisement

(iii) Number of stores

(c) Run a time series regression of the above equation (2) and interpret the results.

(d) Assuming that for 2016 we are provided with the following forecast estimates:

- GDP to grow by 2.5%

- Prices to be at 2%

- Population to grow by 0.5%

- A satisfaction score of 7.5

- Number of advertisements: 18

- Number of stores: 12

What would be the approximate sales for 2016 based on the economic analysis?

(e) Can you think of alternative forecasting techniques you could apply? Compare your results from (d) with your alternative forecasting approaches.