Financial modelling

Excel help!

Didn't find your answer?

I am currently building a Financial model for our forecast.  I have been tasked with creating a model for the Directors that they can tweak numbers just by using a summary sheet. 

So far, I have created a revenue forecast, forecast P&L, cash flow, headcount and the corresponding actuals and forecast vs actuals sheets.  These all link in nicely and if one is updated, it links through to the other sheets.

The bit I’m struggling with is creating a summary which can be adjusted by the directors, but then feeds into the rest of the sheets.  They basically want to be able to add in headcount which will feed through to revenue (people are charged out to our client, hence why revenue is affected by number of people), P&L and cash flow.  But how do I create a summary that links to the rest of the sheets, without the need for them to touch the rest of the sheets?  I think I need what if or scenario, but I’m unsure how to use them – first time I’ve come across them!

Any help would be gratefully appreciated.

Replies (14)

Please login or register to join the discussion.

avatar
By Accountant A
31st Jan 2017 18:09

If you aren't confident in what you are doing, I would admit it. Not everyone is an expert in Excel. The worst thing you can do is create a spreadsheet that looks as if it works but doesn't give the right outputs. If it's to be used for decision-making, it needs to be right.

If you decide to persist, use what's available from MS, eg

https://support.office.com/en-us/article/Introduction-to-What-If-Analysi...

Thanks (3)
Routemaster image
By tom123
31st Jan 2017 18:29

One persons 'model' is another's hideous mess..

Try to make sure you use protections on the sheets so you don't get corruptions.

Try also to think about check digits (ie sums to zero), with maybe one on your first page for the 'master'.

Think about using pivot tables on your data.

Think also about using MS Access or similar instead.

Thanks (2)
avatar
By paulwakefield1
01st Feb 2017 08:57

It's difficult to answer without knowing a bit about the model. But it appears your directors want to be able to alter certain assumptions and see the effect. In which case why not just have the assumptions they want to be able to manipulate on the output page (or ideally a separate sheet)? Then link these into your assumptions that act as the drivers in the main part of the model?

This will preserve the integrity of the model (preferably with protection enabled) and let the directors play.

Most importantly, heed Accountant A's advice.

Thanks (0)
Replying to paulwakefield1:
avatar
By jul2614
01st Feb 2017 13:39

This is exactly what I want to do, to let the directors have a play! I will look into creating an assumptions sheet....

Btw, I am an advanced excel user with 20 years working in accounts, and have built many spreadsheets, but not one with assumptions....

Thanks (0)
avatar
By paulwakefield1
01st Feb 2017 15:26

OK - Sorry. It is sometime difficult to assess one's audience.

In which case, I would suggest:

Basic structure
Assumptions *
Workings
Outputs - P&L, Cashflow, Balance sheet
Controls
Instructions, version history, data version history, etc.

At the start I would have the Directors outputs/KPI and the assumptions they are allowed to play with. These would be a subset of the main assumptions page and would link straight into those corresponding assumptions thus preserving the logic flow and integrity (as long as you are not responsible for the assumptions made!). The directors' assumptions could be controlled by spin buttons or scroll bars if you wish.

* Purists would argue that the assumptions should be direct manual input only but I personally think this is a valid exception.

Thanks (0)
avatar
By pauljohnston
02nd Feb 2017 10:50

Although linked to Xero and Quickbooks Crunchboards I believe does this. You maybe better using a commercially produced product

Thanks (0)
avatar
By C-A
02nd Feb 2017 11:12

I always use an assumptions sheet when I present models for my colleagues to play with. I highlight input cells yellow to make it as clear as possible where they're "allowed" to play. I then let the rest of the workbook do its work.

Thanks (0)
FD4Cast
By James Power
02nd Feb 2017 12:10

Why don't you get an expert to review? It's very difficult to answer this questions without knowing a bit about the model.

I have 10 years experience in custom-build financial models in Excel for organisations.

Happy to do a no-charge review for you and make recommendations accordingly.

Just get in touch via PM or my website (just google my AW username).

Thanks (0)
avatar
By ziashah
02nd Feb 2017 13:04

You may consider Plan Guru - download a free trial from
planguru.com - give me a call if you need any help in setting up your company or analysis.

http://www.planguru.com/

Thanks (0)
avatar
By Fastlane
02nd Feb 2017 14:12

In addition to what paulwakefield1 has advised, you may wish to provide the interface for the directors to enter their assumptions in the form of a Scenario Manager.

One style for doing this is to build a table structure on a separate sheet where the assumption items they can tweak are listed down the sheet, with columns across holding the inputs for the various scenarios (give each a number). A selection cell allows the user to choose which scenario to activate, which through the use of either Offset or Choose functions (using the scenario number as the column offset value, or index argument respectively) will return the values from the selected scenario to an "Active Scenario" column. The cells in this column are what the drivers in the rest of the model link to.
Search the web for "scenario manager" in a financial modelling context (not the Excel feature) for info on this.

Thanks (0)
avatar
By royprice
02nd Feb 2017 14:46

How to contact you? [email protected]

Thanks (0)
avatar
By David Charles Jones
03rd Feb 2017 15:24

I have built a forecasting model which allows for "constant" (i.e. real) values; "current" (i.e. inflated) values and another set of values that allow for the "front-end-loaded" setting of interest ("cost of capital") rates
(to allow for "real plus anticipated inflation". This last
named ("deflated current price") is because debt service does not allow for inflationary changes. It is based on the initially set interest rates, which will (by inflation) be "front end loaded". The model also allows for changes in foreign exchange rates. In the latter case, of course, the debt service payments must be adjusted to allow for changes in exchange rates. The model is accompanies by explanations and by graphics. Contact me at if you would like a copy. I am a retired Chartered Public Finance Accountant and a Chartered Certified Accountant of the UK, living in USA>
The model was built at Harvard University Design School.

Thanks (0)
Replying to David Charles Jones:
avatar
By agneslo
10th Feb 2017 23:46

Hi, David,
I would like to see this forecasting model built by a prestige university, to learn from it and most likely to improve on my existing model, if you don't mind sharing.

My email: [email protected]

Thanks (0)
avatar
By stanbu
13th Feb 2017 16:24

Probably the simplest solution is to build your spreadsheet with an assumptions sheet that the rest of your workbook accesses. In other words, have all your variables on one sheet.

When it is working satisfactorily, save a copy marked "Directors" and allow them to play with that spreadsheet to their hearts content. When they have finished, all they need to do is to copy the assumptions sheet to you and you can amend the master.

Never let anyone else play with your master spreadsheet!

Thanks (0)