Quick Development Feasibility (Excel)

[boods99]

Hi all,
I have just put together a quick feasibility model enabling you to find out the profitability of a development.
My question to all you excel experts out there is; does anyone know of a way to enable the cashflow spreadsheet (2nd tab) to represent the development and construction timeline? For example; if the project was to take 24 months from go to whoa, are we able to make the size of the spreadsheet, together with the colour coded "phases" such as design/construction/settlement etc to show the actual imputs? I use the total cap interest on this spread sheet to arrive at the value that appears on the "feasibility" sheet.
Hope my question isn't too confusing
BTW, any other comments or suggestions are very welcome.

Boods

 

[eggnog]

should be easily done through VB codeing.

 

[WinstonWolfe]

Hi Boods,

I think I understand what you want.
Play with this xls, and see if it does similar to what you want.

I'd recommend you nominate on the feasibility wsheet, how many weeks each step requires, and a start date.

Then those dates and durations can be used to create the cash flow time line, as I've done on my xls. You can then play whatif when a particular stage runs late.

I could adjust your sheet to automatically make a time line....shouldn't be any macros required.

 

[Jonathon]

Hi Boods

As with most things in Excel, there are many different approaches you could take, here are just some:

1) VBA to copy the formulae across the required number of columns
2) Conditional formatting, so all the formuale stay there, but you just show the relevant formulae, and in different colours if you wish
3) Use flags - formulae which 'flag' which stage you're in at any particular period

My day job is financial modelling (I'm an accountant by training) and in the 10 years I've been doing modelling I've seen dozens of different ways of approaching a problem like this. Although I like using VBA myself for certain tasks, I'd generally advise against using it. Flags would be a far better approach.

To use flags, you'd typically use an IF statement to test if the current period matches the relevant criteria, if it is then the IF statement returns 1, otherwise it returns 0. You then multiply that 1 or 0 by the relevant data.

Cheers
Jonathon

 

[boods99]

Thanks all for your help!

WW - I think you have interpreted my question correctly! What is involved in actually setting this thing up? I will post an unprotected version for editing.

Jonathon -what exactly is a VBA? I'm relatively new to this so you will have to excuse me for my ignorance! I'm assuming it is the same as what Cashflowplus mentioned?

Boods

 

[boods99]

unprotected for your pleasure...

Boods

 

[WinstonWolfe]

Boods, before I can automate a timeline, you'd have to design the feasibility wsheet similar to how I've made my input section. i.e. each expense needs a way of relating a date to it, and a method of payment (cash, interest, or loan draw).

your sheet currently adds capitalized interest into cash flow and doesn't have provision for gst or the margin scheme.

If you have a serious project in mind, and aren''t familiar with the accounting principles involved, I'd recommend you talk to your accountant about the right way to set up a project dev't spreadsheet, or try a program called feastudy.

 

[boods99]

Thanks WW.
My spreadsheet is designed only to give an indication on the viability of a proposed project before embarking upon a full blown feasibilty study. For that we have EstateMaster.
Point taken about the addition of cap interest into the cashflow. Also, I will work on changing the input section as suggested.

Once again, cheers.

Boods

 

[TheShuffler]

Boods - without being too harsh, if you have access to Estate Master I wouldn't even bother with your simple version. I'm a massive fan of EM and it can be as simple (if not simpler) than your model or as complex as you like. If you just want it for some rough and ready numbers, you could even spend a little bit of time just to set up some templates then quickly plug away and the cashflows plus gantt sheet will look after themselves! Cheers

 

[grossreal]

hi boods99
my view is the same as above
if you want a simple way
use a small calc
and the back of an envelope
and then use estate or dev7
for me the question is simple will you make money
and excel is not going to tell you that
if you have the real programme why try to make some thing simple.
does not make alot of sense to me
people try to make making money simple
its not
your question quick development feaso
there is not one
you need to do a full feaso to see if it works
and an excel is good but so is a cheap calc
my advice on developing is don't try to make short cuts
why
because there are no short cuts
there are alot of cuts but they are not short
just my view

 

[T Money]

Protection

Hi Boods,

I think I understand what you want.
Play with this xls, and see if it does similar to what you want.

Hi,

Any chance of getting the pasword for your excel doc (refer link above)?

Cheers

T