budgeting and forecasting- can excel even do this?
Discussion
hi All,
Just creating a cashflow forecast for a new business plan and i'm trying to do some scenario planning, but i'm not sure if excel really can do what i want it to.
A big chunk of the new work for this business will be based on known income flows- ie 6 consecutively monthly payments followed by a 7th payment at month 15.
There are different project types (with different revenue streams - ie one is paid quarterly for 6 periods, one is monthly for 12 months, one is monthly for a few months followed by a balloon, What i can do is tabulate the revenue structure and use a dropdown to select the relevant receipt profile, but this always populates into adjacent cells. How do i arrange it so i can select the payment profile and then get it start the profile from month 7, or month 14, or month 15....and then to be able to quickly change the start month. As you can probably tell we are trying to model out the effect of the timing of future known cash flows and be able to change them quickly.
I reckon this needs VBA, which is beyond me, so i've been trying to do it with Match, Arrays and vlookups......any help very very greatefully received.
We're only gonig out 36 months if that helps!
Just creating a cashflow forecast for a new business plan and i'm trying to do some scenario planning, but i'm not sure if excel really can do what i want it to.
A big chunk of the new work for this business will be based on known income flows- ie 6 consecutively monthly payments followed by a 7th payment at month 15.
There are different project types (with different revenue streams - ie one is paid quarterly for 6 periods, one is monthly for 12 months, one is monthly for a few months followed by a balloon, What i can do is tabulate the revenue structure and use a dropdown to select the relevant receipt profile, but this always populates into adjacent cells. How do i arrange it so i can select the payment profile and then get it start the profile from month 7, or month 14, or month 15....and then to be able to quickly change the start month. As you can probably tell we are trying to model out the effect of the timing of future known cash flows and be able to change them quickly.
I reckon this needs VBA, which is beyond me, so i've been trying to do it with Match, Arrays and vlookups......any help very very greatefully received.
We're only gonig out 36 months if that helps!
whatleytom said:
Couldn't you just use an Hlookup to pick up the relevant start payment on your payment profile table?
I don't think i can because i'm not looking anything up. This is the typical payment profile:
and its going into (an excert) here:
What we need to do is change the "year" and "Month" column so we can move the payment profile. We might think the first payment will arrive in yr 1 month4, but we also need to very quickly be able to change the "month" to 7, and for the whole payment profile to move with it! Does that make sense?
You need to define the columns on the left with the names in the top row, and the month numbers as the name “Month”.
To name, highlight the column including name, and type Alt I N C (shortcut for Insert > Name > Create and then check Top Row and OK.
Alternatively, DM me and I’ll email it to you!
Gassing Station | Finance | Top of Page | What's New | My Stuff