Auxiliary formulation for Annual profit

[alvinpoon]

Dear

I have a growing cash (LEVEL variable) for 60 months (5 years).
I want to know how to create a AUX variable (e.g. to name it as annual cash) which can calculate the every year cash amount as the sum of the past 12 monthly cash.

( refer to attached file , see the value of the table of the cash level at 12th month, at 24th month, at 36th month and so on)
for example. the graph of the AUX variable of the annual cash will show like:
x-axis (time) month 1st~12th (1st year) : a flat line to show 1st year cash level $18,150, then, month 13rd~24th (2nd year) another flat line to show the 2nd year cash level $14,810 (32,960-18,150)

what vensim functions or what formulation can do that ? ( I heard someone using Pulse functions to sample the amounts, I do not know how to do that in vensim), please help.

as you may know, company's financial budgeting take place annually at the end of the year, which require to record the annual amount as a reference to set next year budget e.g. percent of the cash for the investment etc. So I need that AUX variable.

Thank you

[tomfid]

The PULSE approach is one way to do it, but I find it annoying because it creates a sawtooth that's hard to interpret, except at the annual endpoints.

Another option is to integrate cash flow over a moving window. You can do that by creating a stock with an inflow of cash income and an outflow of cash income a year ago, using DELAY FIXED. That gives you a continuously-varying curve. If you need the reported value at discrete intervals, you can use SAMPLE IF TRUE to report the stock once a year.

[alvinpoon]

Hi Tom

Thanks a lot.

I finally tried to use PULSE TRAIN functions to set 2 LEVEL as old value 1 and old value 2 of the cash to calculate the annual cash.
If you can open my attached revised model, I get almost there. just the annual cash level lag 1 year behind. I do not why and how to fix it? Can you help take a look and advise?

I hope this would be useful for other members who may also need to set up this common annual variable in their modeling.

[tomfid]

I think the lag is from stringing together cash[t-1yr]-cash[t-2yr].

I would suggest a different approach, as in the attached.

test annual concept alt.mdl

(5.62 KiB) Downloaded 374 times

There are two versions. At top, I use DELAY FIXED to model the accumulation of reported cash over an accounting interval - this gives a moving average over a fixed window. Then I sample that from time to time.

At bottom, I use a SMOOTH (written out explicitly as a stock flow) without the discrete sampling. Simpler, but not as close to the real accounting numbers. However, real budgeting might not be all that close to the official accounting either, because in reality people have some information about how the year is going partway through, and may update plans accordingly.

[alvinpoon]

Thanks Tom.

It is exactly what I need with the top one.