Macro help to calculate run-up period for balance sheet using a logic nesting IF formula

Anything would be greatly appreciated / helpful

My take is you are looking for spreading of revenue, or in Accounting jargon "Deferred Income" I have seen a code for such task, but would not see any benefit over a formula. Infact, if you have got a great formula, it would much better than a code.
You need to run a code every time you have a new income and want to find out the accrued and deferred income for a particular period, unless of course you have en event-driven code, whereas formulae would be auto, and can easily be adjustable. That is my personal opinion.

Hey AB33

Thank you for your insight on this, I had not thought of a code, however, I am not sure how to construct one. Would this be something that could work with my formula or is it more along the lines of either you have the formula or code and they cannot co-exist if you will together?

Sincerely

R3con3d

R3con3d ,
By deferred income I mean:
If you have a contract and the contract goes over 1 month, you should not recognise(book) the whole contract value in the same month, rather you need to spread over the income over the length of the contract stipulates. This applies to most Accounting principles and GAAPS. Now days, there are specialities software where all the contract and spreading could be completed in one go, but lots of people still use excel for this task. The principle works the same in both excel and an accounting software.
You have a contract start date, end date, financial year. The formulae works within these parameters.
If you search in the general and formula sections, you should be able to find questions with attached sample.

Hey AB33

I think I get what your saying, our problem is that when we get a program and lets say for the year it is worth 100k over a 12 month fiscal year. This would come out to $8,333.33 per month average. However, we are not receiving the 100k upfront and then collecting from that 100k 8.33k at a time per month such as the deferred income definition states. We get paid immediately after we manufacture, process and ship our product. So the 100k does not end up on our books in whole until the year is completed, or monthly time period is completed. The other problem that we face is that even though will continue to run the same program for a number of years, the volume changes on a yearly basis (not really shown in my attached example). So I cant spread the $ value over numerous years without throwing off my other annual numbers.

Sincerely

R3con3d

"We are not receiving the 100k upfront"
There is no the issue of deferred (Spreading over) Income.
"We get paid immediately after we manufacture, process and ship our product"
Why you software could not cope with any other accounting issue then? In other words, why do you need to use excel in which your software could not do?

Hey AB33

In regards to "Why you software could not cope with any other accounting issue then? In other words, why do you need to use excel in which your software could not do"? Almost all of this budget is used to help forecast future incoming business. So we can accurately project future growth potential of our company as well as a variety of other factors to ensure we can handle all incoming business and accurately plan for needed changes to handle it (such as facility capacity, enough employees, raw material coming in, Etc.). Otherwise our software on a day-to-day basis can handle and compute what im asking for.

"How much dollar amount to spread over the remaining full production months so annual figures will still match up".
The cost spread works the same as the revenue, they are just do the opposite. So you can use revenue spread sheet for cost. Search for deferred income spread sheet.

AB33

Thanks for the lead ill look into it!

Hey AB33

Im not seeing online anywhere when searching deferred income spread sheet that aims/ talks about run-ups? Not sure if im missing something or for what Im looking for this just might not be possible on excel. I appreciate and further guidance or the help you have already given me!!

R3con3d

I am sure it is possible in excel . Why do not you re-post it in to excel formula section and see if people react?