Schmeckt Gut launch of Schmeckt Besser energy bar in Atollia

Schmeckt Gut plans the market launch for the Schmeckt Besser energy bar in Atollia within the next couple of months.

The Research Department of Schmeckt Gut has conducted a market analysis of Atollia. The results are provided for you in the EXCEL file in Module 4 Assessment.

Based on international sources, the Research Department is comfortable in making the following predictions on average incomes in Atollia, the inflation rate in Atolia and tariff rates on imports from Industria:

  • Average Incomes: could increase by 1% or 3% or 5% or 7%
  • Inflation rate: could increase by 2% or 3% or 4% or 5%
  • Tariffs on imports from Industria: could increase by 7.5% or 10% or 5% or there may be no tariffs, in which case there would be free trade between Industria (local) and Atolia (International)

The Board of Schmeckt Gut is interested in obtaining information on how the expected annual demand for the Besser energy bars would be under the Best Case Scenario, Worst Case Scenario or Middle (Base) Case scenario. To give you an understanding of what the scenarios mean, consider the best case scenario. The “Best Case Scenario” would occur when the annual demand for our energy bars is at its highest. The question to ask is: “Which income, inflation and tariff predictions would produce the highest possible demand? “ I am sure with this illustration you can decipher what the “Worst case scenario” stands for. The Base or Middle Case scenario is when normal or average conditions occur.

Your task:

Your task is to write a 3,000 word report (APA Reference Style- Minimum Reference 20) to the Board of Director of Schmeckt Gut in which you address the following:

  1. From the predictions on incomes, inflation and tariffs given above and using data in the provided EXCEL Spreadsheet data table (see section) suggest assumptions that would produce each of the following outcomes: (A) Worst Case Scenario (B) Base or Middle Case Scenario and (C) Best Case Scenario. Justify your reasoning. Consider the information in the Excel spreadsheet given to be the Base Case Scenario. Based on the Base Case Scenario prepare Excel spreadsheets showing the following variables for the Best Case Scenario and Worst Case scenarios. In fact you should end up having have three Excel tables each containing the following variables:

Dependent Variable – Annual Demand

Independent Variables:   X1 = Average income per person

X2= Tariff on Imports of energy bars

X3 = Number of stores

X4 = Inflation

Each Excel table should be similar in form to the one for the Base Case below but have different figures for the independent variables due to the fact that assumptions will change from case to case.

Example of Excel Table – Base Case

Annual Demand Average Income per person Tariff on energy bars Number of stores Inflation
Y X1 X2 X3 X4

 

Remember to create Excel tables for the for the Worst Case and Best Case scenarios as well.

  1. Discuss the effects on supply and demand of energy bars as a result of changes in the variables such as incomes, tariffs and inflation. In your answer also make references to the concepts of aggregate demand and aggregate supply, the Philipps Curve and the Laffer curve. This part requires application of economic theory (both micro and macroeconomics).

 

  1. Conduct a multiple regression analysis (in log-linear form) under each of the three scenarios above i.e. Base (Middle) Case, Worst Case and Best Case scenarios. In the interpretation of the regression results discuss the impact of the different assumptions on the annual demand of Schmeckt Gut energy bars I Atolia.

 

  1. Having performed the scenario analysis make a recommendation to the Board of Directors on what it should do in order to create value for the business as it seeks to expand its market into Atolia.

IMPORTANT INFORMATION ON LOG LINEAR REGRESSION

Normal Regression equation is Y =bo +b1X1 +b2X2 +b3X3 +b4X4 + error term

Log Linear form is log Y = bo +b1X1 +b2X2+ b3X3+b4X4 +error term

Please calculate the Log of Y base e (this is = LN(Y) in Excel. Do not calculate the logs of X1, X2, X3 and X4 as discussed in class. Perform the regression with log of Y against the values of X1, X2, X3 and X4 which have not been transformed to logarithms.

Note: Take particular note of the meaning of Log Linear form of the Regression Equation. You only need to calculate the logarithm base e of the annual demand but should not determine the logarithm of inflation, incomes and tariffs.

1-2 references to be taken from OpenStax College, Principles of Economics, OpenStax College, 19 March 2014: retrieved from

http://cnx.org/content/col11613/latest

http://cnx.org/contents/aWGdK2jw@11.332:JgDXaOLP@11/Introduction

 

 

Excel Spreadsheet Data Table

Annual average demand of energy bars per person Average income per person Tariff rate on imports of energy bars Number of stores where energy bars are offered
106 15500 5 15
90 15810 5 15
93 16395 5 15
92 16887 5 15
91 17495 5 15
110 18282 5 16
109 19013 5 16
122 19508 5 16
82 19898 10 16
84 20276 10 16
102 20702 10 17
92 21550 10 17
115 22197 10 20
112 22330 10 20
109 22754 10 20
148 23619 7.5 20
143 23855 7.5 20
139 24452 7.5 20
158 24941 7.5 23
142 25514 7.5 23
158 25948 7.5 23

 

 

HOW TO PERFORM THE REGRESSION IN EXCEL

 

  1. I am assuming that the Regression function is not automatically present in your EXCEL, in which case you need to add it in.
  2. You do this by opening an Excel spreadsheet and then going to File………..Options……….Add-ins……..ToolPak. It is the ToolPak that contains a variety of functions of which Regression is part.
  3. Once that has been done you can do the following:

Click on Data………Data Analysis…………….Regression and then you are asked to input the Y and X ranges. Y range is data for the dependant variable and the X range is for the independent variable. When this has been done, click okay. Note that the default is 95% confidence interval which means the level of significance is 5% in this case.

  1. When you have pressed okay, regression results appear like magic. You need to decide where to put them.
  2. I have provided an example of performing regression using Excel with Monthly salary as a dependant variable and independent variables (X1) which is Age and X2 – Number of certificates).
  3. Should you encounter any problems let me know so that we can resolve them.

 

INTERPRETING THE RESULTS

The form of the regression equation in this case is Y = bo +b1X1+ b2X2 +error term

We are estimating bo, b1 and b2.

In the Summary Output you pick up the following:

  • Values of b0, b1 and b2

These are under coefficients:

bo is the intercept = -828.8538 (4 decimal places)  b1= 73.7708 (4 decimal places)

b2 =181.7247 (4 decimal places)

  • Are the coefficients significant?

 

To answer this you look at the p values. If p value is less than 5% (0.05) then the coefficient is significant. In the example all the three p values are less than 0.05 making them significant.

  • How strong is the relationship between Y and the X values?

 

The answer lies in the adjusted R squared. It is 0.8573 which is 85.73%. This means that 85.73% of the changes in Y are explained by the changes in X1 and X2.

This strength is significant given that the significance F value is less than 0.05 (in this case it is 2.53007E-08).

Thus the relation Y = -828.8538 +73.7708X1 +181.7247X2 relates monthly salary to “Age” and “number of certificates” held by an individual.

 

FURTHER CLARIFICATION ON INDEPENDENT VARIABLES TO USE

Please find below further clarification on the variables to use in the regressions in Assessment 3. The independent variables being considered are average income, tariffs, number of stores and inflation.  Please take the tariff amounts given as in dollars ($) rather than as a % (remember tariffs can be quoted as a fixed amount or on an ad valorem basis which is a %).

It is better to commence analysis from the BASE Case Scenario. This is the middle case which represents what could happen under normal or average conditions. Let say in this case inflation is expected to increase by 3%, incomes by 5% and tariffs by 7.5%. It would be difficult to have inflation as a separate variable, what is needed is to determine real incomes in which you increase incomes by 5% but adjust their effect by incorporating inflation. An income of 15500 would produce a real income of 15800.97 (calculated as 15500×1.05/1.03).

ORIGINAL DATA

Annual average demand of energy bars per person Average income per person Tariff rate on imports of energy bars Number of stores where energy bars are offered
106 15500 5 15
90 15810 5 15
93 16395 5 15
92 16887 5 15
91 17495 5 15
110 18282 5 16
109 19013 5 16
122 19508 5 16
82 19898 10 16
84 20276 10 16
102 20702 10 17
92 21550 10 17
115 22197 10 20
112 22330 10 20
109 22754 10 20
148 23619 7.5 20
143 23855 7.5 20
139 24452 7.5 20
158 24941 7.5 23
142 25514 7.5 23
158 25948 7.5 23

 

 

 

 

 

REVISED FIGURES UNDER BASE CASE USING ASSUMPTIONS MADE

(Incomes increase by 5%, inflation by 3% and tariffs increase by 7.5%)

Average demand Income Real Income Old Tariff New Tariff No. of stores
106 15500 15800.97 5 5.375 15
90 15810 16116.99 5 5.375 15
93 16395 16713.35 5 5.375 15
92 16887 17214.9 5 5.375 15
91 17495 17834.71 5 5.375 15
110 18282 18636.99 5 5.375 16
109 19013 19382.18 5 5.375 16
122 19508 19886.8 5 5.375 16
82 19898 20284.37 10 10.75 16
84 20276 20669.71 10 10.75 16
102 20702 21103.98 10 10.75 17
92 21550 21968.45 10 10.75 17
115 22197 22628.01 10 10.75 20
112 22330 22763.59 10 10.75 20
109 22754 23195.83 10 10.75 20
148 23619 24077.62 7.5 8.0625 20
143 23855 24318.2 7.5 8.0625 20
139 24452 24926.8 7.5 8.0625 20
158 24941 25425.29 7.5 8.0625 23
142 25514 26009.42 7.5 8.0625 23
158 25948 26451.84 7.5 8.0625 23

 

The variables and figures to use in the regression would be those highlighted in yellow. Should you encounter a scenario in which you assume that there are no tariffs then you will not have “tariffs” as one of your independent regression variables.