Modelling 30 years monthly across sheets

[Guest]

Hi All

I have been asked to model cashflows for a company for 30 years monthly
(i.e. 360 data calculation cells). The calculations are complicated and
cannot be replicated going down the page as 256 cells is not enough for the
calculations.

Therefore, I feel my only option is to use two sheets (or two books?)
however, there are obviously issues with this too:
a) if a change is needed it must be replicated in two sheets;
b) linking the first cell in the second sheet will be a unique formula
equalling the last of the first sheet
etc...

Has anyone had any experience with this and can suggest what the most
efficient solution is?

Regards
Nick

 

[Andy Wiggins]

You might consider using Quattro Pro for this exercise. It has far more
columns than you require.

--
Regards
-
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy

 

[Guest]

thanks Andy however, my firm is unlikely to buy that software ~ do you have
any suggestions revolving around Excel itself?

 

[Alan]

Hi All

I have been asked to model cashflows for a company for 30 years
monthly (i.e. 360 data calculation cells). The calculations are
complicated and cannot be replicated going down the page as 256 cells
is not enough for the calculations.

Therefore, I feel my only option is to use two sheets (or two books?)
however, there are obviously issues with this too:
a) if a change is needed it must be replicated in two sheets;
b) linking the first cell in the second sheet will be a unique formula
equalling the last of the first sheet
etc...

Has anyone had any experience with this and can suggest what the most
efficient solution is?

Regards
Nick

Hi Nick,

I think you need to go back and explore the business requirements for
a model that uses monthly periods for 30 years.

I find it very hard to imagine that there is a rational business case
for such an effort - who really thinks that the forecast for period
356 will be accurate enough to matter to the month?

Three years by month (tactical outlook for most organisations) and
then quarterly or annually should really be the rational solution.

If worst comes to worst, then you *could* do 20 years by month if you
really have to.

HTH,

Alan

..

 

[Guest]

Alan

I completely agree and previously have developed models which use monthly
cashflows for five years and then quarterly for a further 20 years. However,
what the client wants is monthly data, so it's not really up to us to
question this.

Regards
Nick

 

[Andy Wiggins]

I suggested QP because (relatively speaking) it is inexpensive (less than
100 $ or £). This is a cheaper option than making the thing fit into Excel.

That said, how about 30 sheets, one for each year, then summarizing them on
a master sheet.

--
Regards
-
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy

 

[Alan]

Alan

I completely agree and previously have developed models which use
monthly cashflows for five years and then quarterly for a further 20
years. However, what the client wants is monthly data, so it's not
really up to us to question this.

Regards
Nick

Hi Nick,

Obviously your call, but if it was my client I would be questioning
it - in part I don't believe they will actually derive value from the
effort put in, wihch means either they are potentially going to look
to underpay you, or they will regret it afterwards, neither of which
is good.

However, if they absolutely want it, then surely they will fork out a
small sum for a tool that would do it (QB seems to have been
suggested). That would be a drop in the ocean compared to the total
cost of producing such a model I imagine.

Good luck!

Alan.

 

[Guest]

Why is it you can't take the months DOWN the rows, rather than across the
columns?