Month & Duration for financial forecasting (Excel for Mac)

[ara.darakjian]

I have built a model that is a 10y (by month) forecasting tool for our business.

Columns - Values
A - account
B - GL #
C - Beg Value $, or % of TR
D - Annual Max $
E - Annual Escalation
F - Year/Mth Beginning
G - Duration the expense should be recognized over for the year

H-Q - Annual Summaries
S-EH - Monthly statement

Currently the formula in place works great for expenses that are recognized over 12 months for the year.

What I am trying to create is a formula that will be able to use the Mth Beginning and the Duration to correctly recognize the expense on an annual basis.

For example, snow plowing expense of $60,000 begins in October and lasts for 6 months. So I would want the formula to see Mth Beginning is equal to 10 and duration is 6 so it would take $60,000 and divide it by 6 and start to recognize the expense in Oct through Mar of the following year.

If there are no values in Mth beginning or duration, then the expense is a monthly expense over all 12 months of the year

I have attached a workbook, pleas use row 19 to help me solve this problem.

[AliGW]

This is for GoogleSheets, is it? Just checking that you have posted in the right place.

[ara.darakjian]

My apologies
it should be for excel for Mac

[ara.darakjian]

I am going to close this thread and repost on the correct forum.

[ara.darakjian]

This is for GoogleSheets, is it? Just checking that you have posted in the right place.

Can I move this post to Excel for Mac?

[AliGW]

It is already in the correct place - there is no special section for Excel for Mac. This section is called:

For Other Platforms(Mac, Google Docs, Mobile OS etc)

I am going to close this thread and repost on the correct forum.

No, you can't, and it is already in the right section.

You've had plenty of views - 93 or so - so there's something stopping members from helping. This could be that they aren't sure what you want or feel you are asking to much. Maybe if you add some more detail, you might get a bite.

[janmorris]

For example, snow plowing expense of $60,000 begins in October and lasts for 6 months. So I would want the formula to see Mth Beginning is equal to 10 and duration is 6 so it would take $60,000 and divide it by 6 and start to recognize the expense in Oct through Mar of the following year.

If there are no values in Mth beginning or duration, then the expense is a monthly expense over all 12 months of the year

I have attached a workbook, pleas use row 19 to help me solve this problem.

The problem i see at the moment is that you have not included any manually calculated results that accurately reflect your description.

Please update your file and give source data and manually calculated results that mirror what you are describing.

Also, it would be helpful if you update your profile with the version of Excel for Mac that you have (ie. show the year, not the "16.38"). by doing so then we will be in better position to give a solution that will work with your version of excel, and not ours (if different).

[ara.darakjian]

my apologies for the delay. I have attached the workbooks with the field computed to show an example of what I am trying to get accomplished.

In addition, There is an Ann Esc field that is the % increase year over year the expense grows. I have included that in the calculation of the values.

[janmorris]

it seems you are hoping to calculate the yearly percentage increase, and at the same time divide that into the relevant months, after which to sum each year with an offset formula.

I have used different logic:
first i am calculating the yearly amount with interest increases, and then i am using those values to split the amount into the relevant months.

You can see this in cells H19:AD26.

to calculate the yearly amounts, this formula in I19 and dragged across:

Please Login or Register  to view this content.

then to allocate the monthly amounts, this into S19, then dragged across, and dragged down:

Please Login or Register  to view this content.

using this logic, for the following year you would change $H19 to $I19, and drag across and down, and repeat this procedure for each year.