|
|
|
You are here: Home > Educators > Using spreadsheets > Section 4: Using spreadsheets for 'what if' analysis |
|
|
|
|
Section 4: Using spreadsheets for 'what if' analysis
The section is divided into: Section 4.1: Developing a spreadsheet model: Interrelationships between markets
Section 4.1: Developing a spreadsheet model: Interrelationship between marketsThe example focuses on the following question: What are the consequences on the market price and quantity of tea and milk if there is a fall in market supply of coffee? The theory suggests that the demand for tea will increase and the demand for milk will fall (assuming that milk is consumed with coffee but not with tea). The spreadsheet illustrates how changes in one market have implications on the equilibrium price and quantity in other markets. Section 4.1.1: The ModelThe model is based on three interrelated markets; coffee, tea and milk. It is assumed that consumers view coffee and tea as substitutes, and drink coffee with milk, and tea without milk. The coffee market is represented by the following demand and supply conditions:
Where H is an exogenous variable that represents the current harvest conditions, while T is an exogenous variable that represents the level of taxation on producers. P and Q are the price and quantity of coffee, while b represents the price elasticity of demand for coffee and d represents the price elasticity of supply for coffee. The reduced form equations (solving for P and Q) determine the equilibrium price and output level.
The tea market is represented by the following demand and supply conditions:
Where Pt is the price of tea, P is the price of coffee, f is the price elasticity of demand for tea, j is the price elasticity of supply for tea, and g is the cross price elasticity of demand for tea with respect to the price of coffee. The positive sign before gP implies that the two goods are substitutes. The reduced form equations (solving for P and Q) determine the equilibrium price and output level for tea.
The milk market is represented by the following demand and supply conditions:
Where Pm is the price of milk, P is the price of coffee, l is the price elasticity of demand for milk, r is the price elasticity of supply for milk and n is the cross price elasticity of demand for milk with respect to the price of coffee. The negative sign before nP implies that the two goods are complements. The reduced form equations (solving for P and Q) determine the equilibrium price and output level for milk.
Section 4.1.2: Expected OutcomesAn increase in the market supply (improved harvest) will cause the price of coffee (P) to fall. This will have a knock on effect in other markets. The lower price for coffee will cause an increase in the quantity demanded for milk (Qdm = k - lPm - nP) and a decrease in the quantity demanded of tea (Qdt = e - fPt + gP). Therefore, a fall in the price of coffee (P) will lead to a fall in the price of tea (Pt). Section 4.1.3: Walk-through exerciseThe walk-through exercise is important as it allows you to check if the spreadsheet model is behaving properly. The aim is to make sure that the output from the spreadsheet corresponds with the output from the walk through exercise. In other words it acts as a safety check, if the two do not correspond then an error has occurred in either the spreadsheet or your calculations. Given the following input parameters, and solving the reduced form equations, the output should be as follows:
The output is:
If H is increased to 60, then:
Section 4.1.4: The spreadsheetThe following screen shots depict a suggested layout for the spreadsheet. Note the use of named sheets to give instructional information.
Screenshot 1: View of the simulation model 
Screenshot 2: View of the simulation model with comment boxes 
Screenshot 3: View of the Working Area 
Screenshot 4: Consequences of a good harvest 
The design blends both numeric and text information. By including text, numeric and graphical information, it enhances the value of the resource as it accommodates different learning preferences. Cross referencing the output area with the walk through exercise, it is evident that the model behaved as expected. The improvement in harvest conditions caused a decrease in the price of tea. Section 4.1.5: The spreadsheet formulas and functionsThis section outlines the formulas that were used in the spreadsheet. The cell addresses correspond with the spreadsheet illustrated through the previous screen shots. The first set of formulas that need to be developed are those that create a numeric value for the words entered in the Harvest (C6) and Taxation (C7) cells. The conversion into values of 50 for neutral, 40 for decrease and 60 for increase is undertaken in the Working Area. The formulas use a set of 'If' statements, i.e., if C6 is neutral then the cell value should be 50, however if C6 is increase then the cell value should be 40, if C6 does not equal neutral or increase then the cell value should equal 60. The formulas are:
The second set of formulas that need to be developed are those used to calculate the actual outputs (price, quantity and total revenue) for the three markets. These are based on the reduced form equations that were calculated earlier. The formulas are:
The third set is used to calculate the percentage change between the original values and the actual values. The original values are simply those calculated when harvest is set as 'neutral' and taxation is set as 'no change'. The values are stored in the Working Area. The calculation is rounded to one decimal place. The reason for this is that the values being used to calculate the growth rates are not all to one decimal place. Therefore, you may find that even with the harvest and taxation set as neutral, some growth rates are not zero. This problem is eradicated by the use of rounding within the equation. The formulas are:
The final stage is to convert the numeric growth rates into textual descriptions. This was achieved by the use of the 'IF' statement. For instance, if the growth rate is greater than zero then the term 'increase' is to be used. However, if the value is less than zero then the term 'decrease' is applied, while if the value equals zero then the term 'no change' is used. The formulas are;
Section 4.1.6: Improving the degree of sophisticationThis spreadsheet model has focused on supply conditions in the coffee market. However, you could further develop the model by including demand side conditions, such as tastes and fashions, or the level of consumer income.
Section 4.2: Mapping a learning objective to a spreadsheet exerciseThe aim of this section is not to build a spreadsheet but to think about the issues involved in achieving the learning objectives through the use of spreadsheets. The learning objective maybe that by the end of this activity the student should be able to - 'understand how changes in the external environment can impact on the business performance of the firm'. This learning objective may require the student to know how to measure business performance, for instance, gross/net profit and performance ratios, and to understand how these can change in response to changes in prices and/or costs. The learning objective requires that students should be able to evaluate the likely impacts on business performance from a specific change in the external environment for a firm. The student can use the simulation to test their understanding of the issue through experimenting with different 'what if' scenarios and assessing the impact on the firm's performance ratios. In the initial period the scenarios or tasks can be written by the teacher. It is important at the planning stage to account for the student needs, in particular, the teacher might wish to develop a set of models that can be used in a sequence and increase the degree of sophistication and realism. The reason for this is that students may find a simple 'abstract' model useful at the start to help understand the broader principles involved. After which they move to a more sophisticated and challenging model. The following scopes out a framework for a potential spreadsheet model: It is assumed that the firm has the following characteristics; the price is determined by the firm and the output is determined by a simple demand function. However, the firm exports all its output, therefore the price within the demand function needs to be determined by a combination of price set by the firm and the exchange rate. Total costs are determined by fixed and variable costs, where variable costs are cost per unit multiplied by the number of units sold (output). Then the simulation will allow the user to change the exchange rate to visualize the impact on the profit and loss account and other selected performance indicators.
After the students have understood how the simple model works then a second, more sophisticated model, could be developed. The more sophisticated model will allow the student to probe the relationship between the external environment and business performance. For instance, assume that:
|
