Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
"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 .. |
#5
|
|||
|
|||
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 .. |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
"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. |
#8
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I forecast monthly and annual totals from previous year's . | Excel Worksheet Functions | |||
Copying multiple sheets from one book 2 another and undertake spec | Excel Discussion (Misc queries) | |||
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions | |||
Automatically copy selective sheets from one workbook to another | Excel Discussion (Misc queries) | |||
Multiple sheets selected | Excel Discussion (Misc queries) |