Spread Sheet Modelling - Article Example

Spread Sheet Modelling More often than not, the "old" business plan - though still produced and of value in its own right - is given less attention than the newer Strategic Plan. Unlike the Business Plan, which tends to be a very short document, the Strategic Plan is likely to be much more substantial and detailed. The Business Plan provides the foundation and framework for the Strategic Plan.1 Senior business managers are often so occupied with immediate issues that they can easily lose site of the long-term objectives of the business - objectives upon which the business can thrive if attained or fail completely if not. Because of this, a Strategic Plan today is a virtual necessity. Most managers tend to see the Strategic Plan as a 'living' document; one that, with careful foresight, consideration and development is written at the start of a business planning period, then reworked as circumstances within the company and business climate change throughout the planning period.2 The writing and preparation of a Strategic Plan is an important effort, demonstrating that careful consideration has been given to the business's development; however, the ultimate goal of the Strategic Plan is its own realization. With the advent of the personal computer and spreadsheet development, the Strategic Planning process today is made easier with the many current spreadsheet programs available to aid in the Plan' A Short History of the Spreadsheet The term "spread sheet" (nowadays "spreadsheet) has a long history, beginning with the non-computerized version, a reference to which was made in accounting books 2.2 Layout plan 1. The user can either click "Run Random 10 Day Simulation" to generate outputs over 10 days using random values for all inputs OR 2. Enter a temperature for the next day OR click the "Randomly Generate Temperature" button to generate one. 3. The user then enters their intended: a) Price per cup b) Initial Investment 4. The user also needs to either enter their: a) Amount of Vodka per Jug b) Number of Ice Cubes per Cup OR click the buttons to optimize these variables to maximize demand. 5. Then clicking the "Calculate Demand" button will automatically take them to sheet 2, calculate the predicted demand and the required quantities of stock to satisfy this demand. 6. The user must enter their intended stock purchases subject to data validation to avoid them trying to purchases items in quantities that are unavailable or that cost more than there is funds available for. Alternatively they can also click the button labeled "Optimize Purchases" to have the best possible combination of purchases displayed for them. 7. The user can then click the button "Run Simulation", this will take them to sheet 3, where the outputs for that day will be displayed. 8. Clicking "Next day" will then save all the inputs and outputs in sheets 5-7, reset all the values on sheets 1-3 and return them to the front sheet to repeat the process. 9. When the user has cycled through the process 10 times they will automatically be taken to sheet 4 where the cumulative totals are displayed. They can also access this at any earlier point by clicking "Display Cumulative Totals". 10. When on sheet 4 the user can click "Reset Model" which will delete all saved data and return them to the front sheet. 2.3. Assumptions - Drinks are sold by the cup only. No requests, doubles/shots etc. will be considered. - The price per cup cannot be set lower than 20p. - Price and temperature are constant over the day. - Demand is the number of people who will definitely purchase a cup. - The user cannot overdraw from their available funds. - There are no storage costs for stock that is carried over to the next day. - Only vodka and lemonade are mixed in jugs and then poured into cups with constant amounts of ice in them. 2.4. Strengths - Simple layout - The user works on the model in a structured manner starting from sheet1. Read and Batson (1999) state that "Using multiple sheets ' creates a model that is much easier to navigate." The layout is easy to follow even for someone not familiar with using Excel. The structure of the spreadsheet also demonstrates clearly any dependencies and assumptions to the user (Ronen , Palley & Lucas, 1989). - Flexibility - Most users prefer to make their own decisions regarding repurchasing of stock. Giving the user the option to do this adds major flexibility to the model. - User Control - As there are multiple worksheets, the user can decide the level of depth that they wish to go into. They can let the macros guide him through the model, or switch between sheets anytime to make changes/reviews. The use of navigation macros makes "it easier for users to find their way around a large model." (Read and Batson, 1999) and avoids any confusion. - Robust - The model although simple, is capable of solving complex multi-variable problems. These variables may or may not be linearly related. - Extendible - The simple layout and structured approach easily allows the model to run over periods greater than 10days. - Graphs/Charts - Graphs and charts can be shown on relevant pages in order to make the values obtained more understandable to the user than a sheet of numbers (Read and Batson, 1999). Too many graphs/charts on a single sheet tend to confuse the user. 2.5. Weaknesses - Need to switch - Every time the user wishes to change or review information entered, they will have to switch between sheets. This can get annoying after sometime. - Advanced Users - This kind of layout might not be suitable for advanced excel users who prefer having information in fewer sheets. - Layout - Most users prefer a style in which most information can be entered in a single page, with output displayed on another, which seems more logical. 3. Macro Specification The spreadsheet will allow the user to run a 10-day simulation of the store operations. This will be done by using a macro, which will run for 10 days as follows: - The user will enter a desired price per cup, which will be steady for the entire period - Temperature for each day will be generated from the given distribution - These two figures will be used to calculate the demand - Maximum demand factors will be used in determining the amount of vodka per jug and ice cubes per cup to achieve as highest demand as possible - Consequently the number of jugs required will be estimated - The number of jugs produced will then be calculated - Daily revenues and expenditures will be calculated - Stock levels will be updated - Daily profit will be accumulated into Total Profit and this amount will be used for repurchasing decisions 4. Informative Chart Idea A breakeven chart is a strategic tool used to plot the financial revenue of a business unit against sales to determine the point when revenue generated is equal to cost of sales i.e. the breakeven point. Sales above the breakeven point indicate profits are being generated. In this situation, as variable costs differ each day due to differing purchasing decisions there is no single variable cost attributable to each sale. The total cost therefore goes up in stages as, for instance, 100 cups are purchased but these then last 3 days. The breakeven point could be calculated for both a random simulation or for a worst-case scenario, which is likely to be appropriate given the model is being built to reassure financial backers. References "Developing a Business Strategy," Plan Ware Invest-Tech Ltd., http://www.iol.ie/resource/planware/strategy.htm#1. 2 From interview with Consumers Water Company employee, Barbara Delage, Executive Assistant to the President and C.E.O. and Vice President and C.O.O. 3 Spreadsheet: Its First Computerization by Richard Mattessich-Professor, University of British Columbia 4 From: George J. Murphy,"Mattessich, Richard V., The History of Accounting--An International Encyclopedia (New York: Garland Publishing Co., Inc, 1997): 405. 5 Biography of Daniel Bricklin, Adam M. Fleming-Virginia Tech, 1997. 6 Exl-Plan Software, http://www.iol.ie/resource/planware/exlplan.htm 7 Analyze-It for Microsoft Excel, http://www.analyse-it.com/info/default.htm 8 How to Write a Strategic Management Case, Dr. Randall S. Hansen, Stetson University http://www.stetson.edu/'rhansen/strategy_case_writing.html 9 Getting to Know Microsoft Excel, Client Services Group, University of Saskatchewan, 1994 http://www.usask.ca/dcs/online_docs/excel.html#WHATIS 10 How to Write a Strategic Management Case, Dr. Randall S. Hansen, Stetson University http://www.stetson.edu/'rhansen/strategy_case_writing.html Read More