How to create a great financial model for your 1-to-1 project

How to create a great financial model for your 1-to-1 project

We’ve been involved in the planning of a few 1-to-1 schemes now and at the core of the financial viability of all of them was a decent spreadsheet model.

As any KS3 child will tell you, spreadsheet modelling is useful because it allows us to ask ‘what if’ questions and see what the financial impact of decisions would be on an overall project cost, without actually having to find out in real life.

The process of constructing a model will help you to finesse your scheme to get the per-month per-pupil cost down to a figure that you judge will be attractive or at least acceptable to parents. In contributory schemes with which we have been involved, it has felt quite crucial to get this number below £10.

If Excel is not your strong point, recruit the help of your friendly local Computer Studies, Business Studies or Maths teacher, or the school’s Business Manager, for whom it almost certainly will be.

What does a good 1-to-1 financial model look like?

  • It needs to be built around those variables that are most likely to change over the course of your planning and which will have the largest impact on the cost – namely the quantity and type of device.
  • Include variables for case (£15-30) and insurance (£25-30) costs, these are fairly certain to be needed.
  • It is sensible to include a small annual cost for app purchases (£10? The most expensive Apple apps that used to be chargeable are now free).
  • It is helpful to separate out student devices and associated costs from those related to staff, as this will allow you to cleanly model the real-terms cost to families of equipping their child with a device without polluting this number with staff or infrastructure-related expenses. It is important at the marketing stage (if running a contributory scheme) to be able to demonstrate the cost effectiveness of what you are offering, compared to what they would pay in the shops.
  • Before calculating the per-student costs, introduce another variable – the figure that the school will contribute from other sources of income (Pupil Premium, et cetera). This figure should be used to offset the direct cost to families of the things that make up the package (device, case, insurance, apps), rather than swallowed up in scheme costs more generally. In this way, you can clearly demonstrate how specific funding streams have been spent on pupils.
  • These contribution costs should be surfaced as an overall package cost (e.g. £350) as well as per-month rentals and optional final payment. You will need the help of your leasing partner to get this bit right. Parents find this level of transparent detail very reassuring – they will want to know exactly and categorically what they are committing to.
  • Remember that you are modelling the cost of leasing devices, not owning them, so don’t include the Residual Value (the optional final payment) cost in the bottom line as this will never be paid by the school. Parents will want to see this, but staff devices only need to show the rental costs associated with them.
  • A simple tweak to these contribution costs is to also include a +5% and +10% line, to see what you would need to charge parents in you wanted to build a little fat into the model. This could be to create a small contingency or to allow for a limited number of defaulted contributions.
  • The model should have lines for every other infrastructural and background cost, the full extent of which may not become clear until you move through your diligence; the network cabling, switching, wireless, broadband, AV integration, joined-up systems, storage solutions, MDM.
  • When totalling the whole-scheme costs, include lines that show overall cost to the school including and excluding VAT, so that there is no confusion over this later.
  • It makes sense to operate to a ‘worst case scenario’, albeit moderated by what you know already, so that the school’s leadership is fully aware of the possible cost. For example, even if you think a free MDM may be sufficient, budget for a paid one. From that point on, every frighteningly expensive possibility that doesn’t materialize into a reality is a good news story and yet further evidence of your wisdom and prudence. Far better than the reverse!

 

If all this seems a bit complex, there is a good modelling tool, including up-to-date pricing on all the popular device types and the leasing figures you will need at http://www.educate1to1.org/calculator/

The modelling tool lets you enter the variables that you know (such as number of devices) and prompts you to estimate those you might not (e.g. case cost), filling in all the blanks like current device pricing. That’s fine as far as it goes – it gives you a good idea of the total capital cost of your project, but the really useful bit is the way the tool interacts with leasing rates, because it’s actually these numbers that you need to know to judge affordability. You can also tweak some lease variables (how many years you want the lease to run, whether you want to front- or back-load the payments, how much of the school’s money you want to put in, et cetera) and the model will produce monthly per-device per-pupil figures showing you how much the scheme costs in real terms.

There is no other way to do this without meeting with lots of lease companies and going through a protracted process of teasing out their costs into a meaningful format you can. The lease rates that drive the model have been provided by a lessor that we consider to be trustworthy, but there’s no obligation to do anything other than anonymously play around with some ‘what if’ scenarios. The tool is there to use to get an idea of what you’d need to find (either from school funds or recovered from contributions) in order to make your 1-to-1 project sustainable.

 

Image credit

Link to Educate 1-to-1 book

Submit a Comment

Your email address will not be published. Required fields are marked *

Share with your friends










Submit
Share with your friends










Submit