How To Make A Budget Plan

[simonjasimpson]

Hi.

A question from an Excel dummy (apologies for this !).

I need to make a budget plan which predicts the finances of a small
organisation.

There are a number of predictable expenditures (property maintenance)
which occur regularly but at different frequencies. For example:
decorating costs £1,000 every 5 years, the roof replacement costs
£10,000 every 30 years.

So I know the anticipated cost of each and how often each expenditure
is likely to occur.
I can make a simple calculation to determine how much to put aside
each year (in the example above £1,000 ÷ 5 plus £9,000 ÷ 30).
What I would like to have is a spreadsheet which shows what happens
each year, starting from year 'zero' as it were as, showing how the
finances will ebb and flow over the years. In the example above: at
the end of year 5 £2,500 will have been accumulated but £1,000 will
have been spent, thus the 'reserve fund' should stand at £1,500.

What I can then do, which is the whole purpose, is to see what the
fund actually stands at to see if we are financially on target or not.

Of course, the reality is somewhat more complex than the example, thus
my question.
It seems to me that it is is complicated to do this (but probably not
to you Excel wizzards though !).

All suggestions gratefully received.

Warm wishes.
Simon

 

[Carl Witthoft]

Not sure exactly which steps you're asking help for.

Start out with a pencil: make sure you can write out the equations you
want, such as

YearlyExpense = decorate/5 + roof/30

fundlevel = old_fund_level - yearlyexpense + yearly_income

If you can get all that done, you are just about home free.

SPend a little time getting used to setting up columns of data (perhaps
each row might be a given year) and a bunch of reference cells, and
learning how to setup relative and absolute references to cells in a
given cell's formula.

HTH