“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(DATE(YEAR($A9)-1,12,31),A$8:B8,2)*12)/(12*(12+1)/2) 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:

The starting value in cell B8 is offering you one degree of freedom. With around 2,000,000 you will get:

Another example is a breakdown from quarterly or monthly data to daily data with this formula in cell L2: =VLOOKUP(DATE(YEAR($K3),MONTH($K3),0),K$2:L2,2)+(DAY($K3))*(VLOOKUP(DATE(YEAR($K3),MONTH($K3),1),$A$2:$C$7,3)
-VLOOKUP(DATE(YEAR($K3),MONTH($K3),0),K$2:L2,2)*VLOOKUP(DATE(YEAR($K3),MONTH($K3),1),$A$2:$C$7,2))
/(VLOOKUP(DATE(YEAR($K3),MONTH($K3),1),$A$2:$C$7,2)*(1+VLOOKUP(DATE(YEAR($K3),MONTH($K3),1),$A$2:$C$7,2))/2)

If this approach of breaking down data does not satisfy you and if you have seasonal weights for your data at hand, you might want to consider the better approach 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 [22 KB Excel file, open and use at your own risk]

Linear_Breakdown_Quarterly_or_Monthly_to_Daily.xlsx [47 KB Excel file, open and use at your own risk]