YouTubersLoveExcel#20: Variable Growth Cash Flow Formula

Hi there -

I'm trying to determine how to calculate two things in a business model, and need some formula help - the company has $X in cash today, and I need (1) to automate display of the month/year when current cash runs to zero, and then (2) the total sum from the zero point in step 1 to the cash flow positive point.

On one row J41 through BL41 is the cumulative cash flow total; I am moving around costs/revenues fluidly, so wondering how/if these two items listed above can be automated in display.

Thanks!
Brian

Hello,

I'm working on a cash flow analysis, and I am looking for some help (hopefully not requiring a macro solution if possible).

In looking at a net cash flow analysis, I have a variable setup that says in X years, we will be looking to fully replace equipment with a value of Y. How can I get my cash flow to subtract that value depending on how many year we expect to we will need to replace the equipment?

For example-
(X) Replacement Cost: 100,000
(Y) Years: 3
Annual Estimated Net Cash Flow: 250,000

Year 0: -100,000 (initial equipment purchase)
Year 1: 250,000
Year 2: 250,000
Year 3: 150,000 (first replacement)
Year 4: 250,000
Year 5: 250,000
Year 6: 150,000 (second replacement)

Anyway, I'm hoping to make this dynamic so management can easily change the variables (except for cash flow which is inputted from another formula).

Thank you!
Brian

Ok Experts! Is there a formula that will help me calculate the total income flow on the bottom of column E? I cannot wrap my head around this....

http://spreadsheets.google.com/ccc?k...lCtPwqrjIH_MKQ


Thanks!

I have a series of hypothetical cash flows from D3:Y3. In cell B11, I have a list representing possible start dates, eg. "Q2-Y1" represents second quarter of year 1, etc... D9:W9 has 20 quarters laid out in this format (i used this row to create my list in B11).

My goal was to have the cash flows start at the date I chose in B11 and follow the typical pattern in row 3 after the start date. To do this I used the following formula in cell D11 and copied it accross to W11 (NB: Column C is blank):

=IF($B11=D$9,OFFSET($C$3,,(MATCH($B11,$D$9:$W$9,0)-
COUNTA($C$9:C$9))),IF(C110,INDEX($D$3:$Y$3,,SUMPRODUCT(--($C$11:C110))+1),0))

This formula worked well, except for the fact that I cannot have any cash flows equal to zero (as a temporary fix, made 0 cash flows equal to 0.0001 instead).

But is there a more efficient / elegant formula that can accomplish my goal of having an entire cash flow pattern move according to the starting point i choose?

Would really appreciate if someone could help me with a formula based on the following:

My company builds homes in phases of approx. 10 at a time which take approximately 6 months to complete.

Based on future start dates, (by month), for each phase, and a defined schedule of how much of the budget is spent in month 1, 2, 3 etc as a % of the overall budget,

I would like to create a formula that would refer to a cell range of future starts, by month and by project, which would then calculate dollars spent based on the defined schedule.

Example: If Jan is month 1 and my future start date on 10 homes is month 6 June, and my overall budget is 1,000,000 the formula(s) would automatically spread the budget from month 6-12 based on the defined schedule, ie 2% mo. 6, 8% mo. 7, 12% mo. 8 etc.

Any help would be greatly appreciated.

Thanks.

Larry

I need to write a formula in a financial model that will automatically draw on a line of credit to bring the cash balance on my balance sheet to zero. I have done this before but can't remember how to get around the circular reference issue.

For example:


Month1 Month2 Month 3
Cash 100 -50 -75
Account Receivable 150 100 100

Total Assets 250 50 25

Liabilities

Line of Credit 50 50 50
Stockholder's equity 100 100 100
Retained Earnings 100 -100 -125

Total liabilities 250 50 25

In the above example I would to have a formula that would increase the line of credit in month 2 by 50 to bring cash to zero. I get a circular reference when I write the formual that the line of credit balance in month two =if(cash

I have a start date in column B (d/m/yy), a finish date in column C (d/m/yy), a dollar value in column D. I need a cash flow of the amounts in column D apportioned over the months shown in row 2 (mmm/yy).

The result would be something like $20,000; $25,000; $5,000. This would represent a task starting towards the begining of the first month, continuing through the second month and finishing one fifth of the way through the third month.

Does anyone have anything that does something like this?

Question, I doing a 60 month cash flow for a small apartment. There are 10 units (A thru J) in column A. Some units have different rents (1 bed room vs. 2 bed rooms) and rents starts at different months. I need to set up the cash flow where every 12 month after rent started it increases 3% for inflation. Also, I need to be able to change the rent start month and still calculate the 3% increase. For example - Unit A rent starts in month 2, so 3% increases in month 14. But if I change rent start for Unit A to month 4, it should show the 3% increase in month 16, then another 3% in month 28, etc...
Thanks in advance for the help.

I have read a lot, but still can't figure out the best way to do this:

I have 60 cells aligned horizontally: (Jan-05 to Dec-09)
Above the dates I have a project cash flow.

First I need to report the date at the first occurence of cash flow (a
negative number).

Secondly, I need to report the date at the last occurence of cash flow
(a positive number).

This is so I could set up for the XIRR function. The problem is that
the cash flow appears in various dates and durations based on the
project parameters, so I can't lock the XIRR calc to specific cells.

Any help or guidance is greatly appreciated!

Hi,

I am trying to set-up a cashflow statement that will allow users to enter
data against various categories for each day of use: i.e.

01-Jan-05 02-Jan-05 03-Jan-05 04-Jan-05 etc
Revenue:
Sales
Rental Income
etc


Expenses:
Rent
Overheads
Miscellaneous
etc

So in effect users would have a grid to enter their data into.

What I want to be able to do is:

for each of these expenses / revenues, group them first of all weekly
(running Sun-Sat) on a separate sheet, then group them monthly on a separate
sheet, which would allow analysis of weekly / monthly trends.

What is the best way of doing this?

I've managed to do this for the weekly groups by using a combination of the
sum and offset functions (as each week is a constant number of days (7), the
offset function allows me to "jump" across by 7 columns each time). however,
getting something for the monthly groupings is more difficult as each month
obviously varies in the number of days within it.

Any suggestions greatly appreciated,

Regards,

Gary T.