A Spreadsheet Model for a Business Plan by hjh63417


									A Spreadsheet Model for a Business Plan
The model represents a financial analysis of a go-kart business that a group of people are wanting to set up. They are able to buy
a plot of land of 3 Hectares for £40,000. It is likely that they will get planning permission to build a track and buildings
necessary for the business. The intention is to build two tracks, one for children’s go karts and one for adult karts. The Karts
cost, together with a full set of safety equipment, £4000 and £2500 each.

The intention of this spreadsheet is to try to model the business and examine its cash flows through the year.

How the spreadsheet is designed
There are two sheets in the workbook. Each is divided into sections.

1. Financial results (Balance Sheet)
      a)   The capital costs of the business, this includes the cost of the land, the purchase of the karts, building the track and
           the buildings. This section also calculates the cost of servicing the capital; it calculates the cost of borrowing the
           capital and also calculates the cost of depreciation.
      b) The monthly costs of the business. This takes the data from the capital section of the sheet but also includes costs for
         wages of staff, utilities, fuel and repairs.
      c)   The calculation of income from sales. The sales categories are divided into weekend and weekday sales for each
           type of sale… corporate sales, family tickets, adult tickets and child tickets.
      d) A balance showing the difference between income and expenditure each month

2. Background data ( Monthly forecasts)
      a)   Estimates of demand. In this section guesses have been made about how many people would want each kind of
           ticket per day in each month (The corporate tickets in which a company would take of the whole facility for a
           morning or an afternoon (4 hours) are estimated per week)
      b) The actual projected sales per week. These are based on demand and availability of karts (this depends on how
         many karts there are). If the karts are used for a corporate booking then they are not available for the general public
         for four hours; the spreadsheet algorithm calculates the number of go-karts sold for family tickets before allocating
         any karts for individual use.
      c)   The hours open each day and the number of days per week. The algorithm assumes that the facility will be open
           both Saturday and Sundays throughout the year but that it will close one weekday per each week.
      d) Th maximum capacity of the tracks. This is calculated from the hours available, assuming that each sale occupies a
         kart for one hour. Whenever there is a corporate sale it is assumed that the sale takes over all the karts for four hours.
         The karts used for family tickets are subtracted from the number available for the adult and child rides.
      e)   A calculation of the number of staff required for the facility. A table shows how many people you need for running
           the tracks and the section looks up that number based on projected sales that month. It is assumed that staff can be
           hired and fired at quite short notice. The cost of hiring staff must include “one-costs” such as national insurance and
           a contingency for sick leave etc.

Please turn over for the questions.

14dddea1-0f65-4767-8a32-9d5e45cc241d.doc         Page 1 of 2                     Created on 14/08/2000 08:50:00
1. Explore the model
On the sheet all the numbers in blue are calculated and are locked. Numbers in black are data that the spreadsheet uses to make
calculations. You can change these to see what happens to the business. You can change the formulae and improve the
algorithm for the model by switching off protection as soon as you understand it well enough.

      a)   Try changing the number of karts that you buy in the first place. What, according to this model for the business is
           the optimum number of karts to buy? Look at the sheet, why is there an optimum number of karts? Why do you
           make less money if you have to few and less money if you buy too many?
      b) Try changing the number of hours open per day, can you improve your profit by opening fewer hours in the winter
         and longer hours in the summer?
      c)   Do you think you should charge more. What happens if you put up the charge for adult rides? The spreadsheet has
           no way of calculating the effect of price on demand. As far as this sheet is concerned that is a matter for a business
      d) Have you got enough staff? Do you think you could achieve better profitability by buying new karts every two years
         and spending less on maintenance staff ?
      e)   Could you make more money by selling more corporate rides? At the moment the number of corporate rides sold is
           limited to 1 each weekend and three in the week. If you allowed more than that the number of sales you could make
           to the public would decrease, how would this affect you profitability?
      f)   You could save some money by building a shorter track for the children, how much difference would this make,
           would it be a good strategy?

2. Explore the algorithm

      a)   Can you find out how the fuel costs are calculated. Go to Balance Sheet E23 and work out how the formula works.
           What is the 4.33 there for?
      b) Find out how the costs of salaries for sales staff are calculated. Look at the formula in Balance Sheet D18 and see
         what all the component of the formula are.
      c)   How does the spreadsheet work out the number of staff it needs? Look in Monthly Forecasts!C48 and see if you can
           work out what the Vlookup formula does. Paste a copy of the formula into your answer and write an explanation of
           its components.
      d) How does the spreadsheet decide what the maximum capacity of the business is for rides? Look in Monthly
         Forecasts!C36 and see what the formula in there does. Paste it into your answer and point out what each of the cell
         references stands for. Remember the a family is assumed to have two adults and that a corporate booking takes out
         all the karts for 4 hours.
      e)   How does it work out the actual sales. Look in Monthly Forecasts C17 and find out how it decides how many sales
           will be made in a month of adult rides.
      f)   See if you can get the spreadsheet to give ridiculous results from sensible inputs. Will it ever predict negative sales
           for instance?

3. Thinking about the model

      a)   Do you think that the initial conditions set for this idea are reasonable? If you were the bank manager who had to
           decide whether or not to give this company a loan would you do it or not? Do you think the projections for demand
           are sensible? What questions would you want to ask of the people who want to run the company
      b) Can you find any aspects of setting up this business that the spreadsheet does not model properly?

14dddea1-0f65-4767-8a32-9d5e45cc241d.doc        Page 2 of 2                     Created on 14/08/2000 08:50:00

To top