“I’m not such a big fan of having a linear answer to things.” [Adam Driver]

Abstract

How can you break down annual data such as:

to monthly data which is piece-wise linear:

?

In cells A2:B5 you enter your annual input data. In cells A8:A56 you enter the chronological month ends. For a start you can enter into B8: =B2/12. In B9 you enter =VLOOKUP(DATE(YEAR($A9)-1,12,31),A$8:B8,2)+(MONTH($A9))*(VLOOKUP(YEAR($A9),$A$2:$B$5,2)-VLOOKUP(DATUM(YEAR($A9)-1,12,31),A$8:B8,2)*12)/78 and copy down. In D8 enter =STDEV(B9:B56) In case you need EXACT numbers rounded to the cent, preserving the original sum, enter into C9 =ROUND(B9,2) and into D10 =ROUND(SUM(B$9:B10),2)-SUM(C$9:C9) and copy down. This is just a pragmatic rounding method without VBA. If you like to perform this correctly, look up RoundToSum.

You will get a piece-wise linear output:

If you like to smoothen the linear graph, execute the Excel Solver with D8 as destination cell, and look for a minimum by changing cell B9.

Now you will get:

If this approach of breaking down data does not satisfy you, you might want to consider the page Budget Control.

Example of Use

ExcelForum.com (External link!) Interpolating monthly data from yearly actuals

Download

Please read my Disclaimer.

Linear_Breakdown_Annual_to_Monthly.xlsx [21 KB Excel file, open and use at your own risk]