Sales, Tables & Arrays… Oh My! (97)

Topic

New Reply

The row called Cumulative Profit does not appear to be cumulative profit; it appears to be annual profit minus initial investment outlay. Can you explain that part?

Reply | Quote

Replies

For our purposes this is the same as cumulative cash flow. We us cumulative cash flow (profit) to determin payback.

I just threw this example together to provide more details of how it feeds into the table.

Reply | Quote

Is the $18,000 an initial outlay or an annual outlay?

Reply | Quote

B5 (Investment) is only used in 2 calculations: IRR & Payback. The 18000 is a one time expense in year 0. It is not part of any of the COGS, Labor, or Fixed Cost.

-Leslie

Reply | Quote

I’m sorry, but I do not understand your model. In my interpretation, if the $18,000 is an initial investment, and all other transactions are on a cash basis, using the 100,000 initial sales model, then the five year period generates gross revenue of 144,989, or a net yield and total cash flows of 126,989. The payback period is sometime in the second year (1.46 years), not the the third, and IRR is much higher than your calculation, which I believe is not using the range you intend as it’s argument (press F2 and see the range which is addressed).

Reply | Quote

Hello..

I need help.. I think the appropriate way to solve my problem is with array’s, but I’m new at them, and still learning how they work.

In the following attachment I am trying to figure out the sales sensitivity based on the attached cash flow. I would like for the IRR & Payback (yrs) columns to compute automatically without having to manually enter each sales level (like I have done for 100,000, 120,000 etc.)

Please take a look at the attachment and let me know if you have any ideas…

Reply | Quote

See the attachment for my proposed solution. I think we’re getting lost in the theories behind the Cash Flow model when the question is how to do the actual sensitivity table. What you need to do is ensure that the different sales levels are linked into the model which produces the IRR and Payback results. Then link the results as your headings for the Table to copy. Highlight the whole Table (B23 thru D28) and choose Data —> Table and enter C10 at the “Column Input Cell” prompt. This tells the program that you want to insert your sensitivity sales (B24 thru B28) into the Cash Flow Model and produce the IRR and Payback result. See the attachment to follow the links. A quick look at the trend of IRR and Payback based on different sales levels indicates that this is probably working as intended. Hope this helps!

Reply | Quote