# 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:

- 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.**

- 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).

- 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.

- 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**

- I am assuming that the Regression function is not automatically present in your EXCEL, in which case you need to add it in.
- 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.
- 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.

- When you have pressed okay, regression results appear like magic. You need to decide where to put them.
- 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).
- 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.