Modelling 30 years monthly across sheets
 

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

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


"NICK" wrote in message
...
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

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

"Andy Wiggins" wrote:

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


"NICK" wrote in message
...
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

"NICK" wrote in message
...

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

..

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

"Alan" wrote:

"NICK" wrote in message
...

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

..

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


"NICK" wrote in message
...
thanks Andy however, my firm is unlikely to buy that software ~ do you
have
any suggestions revolving around Excel itself?

"Andy Wiggins" wrote:

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


"NICK" wrote in message
...
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

"NICK" wrote in message
...

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.

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


"NICK" wrote:

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