Excel formula to allocate an amount into daily columns

Allocating amounts such as contracts, prepayment, revenue, cost, etc. across different date ranges is a common Excel challenge that can be solved in many ways. Lets assume you have

Excel formula to allocate an amount into daily columns

Allocating amounts such as contracts, prepayment, revenue, cost, etc. across different date ranges is a common Excel challenge that can be solved in many ways. Lets assume you have some rent contracts with start date, end date, and the amount which needs to be split into months based on the exact number of days in each month.

For Con-01 in the table below, the amount of 110,000 has to be divided by 291 days and multiplied by 22 days in Jan-2020, 29 days in Feb-2020 and so on, Oct-2020 will have 26 days. looks pretty simple?

The challenge is that any addition of new contracts or changes in contract periods should automatically adjust the number of rows and columns, in the meantime calculating the monthly amounts correctly as explained above.

The Pivot Table below shows the desired result that we are trying to achieve here. It is giving the flexibility to analyze the above contracts in various date dimensions, even with a large number of records.

When I started searching on the web on this topic, I came acrossChris Webbsblogwhere he uses a neat technique in Power Query to allocate amounts equally across months. I also found another blog over atExcel University blogwhere Excel formulas are used cleverly to solve somewhat similar challenges but, I had to come up with the following Power Query solution as I wanted the exact number of days in each month for allocation.

If you would like to watch the video version of this post, please click below or continue reading as I explain the steps briefly assuming that you are already familiar with Power Query.

You candownloadthe Excel file for this post and follow along, even use it as a template in a similar allocation needs.

Power Query Steps

  • First, I imported the Rent Contracts table into Power Query and changed the data type of the Start and End Date to DATE then renamed the Query to Contracts.
  • The key element of this solution is the function that I created to generate a list of months and days in each month. I then setup two parameters (pStart and pEnd) for the function to receive the Start and End dates of each contract.
  • Next, I built a query which will be converted to a function later starting with a list of records using the List.Generatefunction which is so powerful in scenarios like this.
    This was the challenging part in the whole process.
  • Now I have got a nice list of records, rest of the steps are simple
  • I converted the List of records on to a table and expanded the records
  • Set proper Data Type for fDate and fDay fields as DATE and WHOLE NUMBER
  • Then, I created a function namedfnPeriodfrom the query using the pStart and pEnd parameters. you can see the completed function below.let Source = (pStart as date, pEnd as date) => let Source = if Date.EndOfMonth(pStart) = Date.EndOfMonth(pEnd) then {[fDate = pStart, fDay = Duration.Days(pEnd - pStart) + 1]} else List.Generate( () => [fDate = pStart, fDay = Date.DaysInMonth(fDate) - Date.Day(fDate) + 1], each [fDate] <= pEnd, each let EoM = Date.EndOfMonth(Date.AddMonths([fDate], 1)) in if EoM > pEnd then [ fDate = Date.AddDays(Date.EndOfMonth([fDate]), Date.Day(pEnd)), fDay = Date.Day(fDate) ] else [fDate = EoM, fDay = Date.Day(fDate)] ), #"Converted to Table" = Table.FromList( Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error ), #"Expanded Column1" = Table.ExpandRecordColumn( #"Converted to Table", "Column1", {"fDate", "fDay"}, {"fDate", "fDay"} ), #"Changed Type" = Table.TransformColumnTypes( #"Expanded Column1", {{"fDate", type date}, {"fDay", Int64.Type}} ) in #"Changed Type" in Source
  • Next, I invoked the function in the Contract table passing the Start and End dates to the parameters pStart and PEnd respectively.
  • Expanded the newly added column, now I have two columns giving me the month and days in each month.
  • Added one more column as Monthly Amount to calculate the proportionate amount for each month:[Amount]/[Days] * [fDay])
  • After removing unnecessary columns, the query is ready to be loaded ontoa worksheet.let Source = (pStart as date, pEnd as date) => let Source = if Date.EndOfMonth(pStart) = Date.EndOfMonth(pEnd) then {[fDate = pStart, fDay = Duration.Days(pEnd - pStart) + 1]} else List.Generate( () => [fDate = pStart, fDay = Date.DaysInMonth(fDate) - Date.Day(fDate) + 1], each [fDate] <= pEnd, each let EoM = Date.EndOfMonth(Date.AddMonths([fDate], 1)) in if EoM > pEnd then [ fDate = Date.AddDays(Date.EndOfMonth([fDate]), Date.Day(pEnd)), fDay = Date.Day(fDate) ] else [fDate = EoM, fDay = Date.Day(fDate)] ), #"Converted to Table" = Table.FromList( Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error ), #"Expanded Column1" = Table.ExpandRecordColumn( #"Converted to Table", "Column1", {"fDate", "fDay"}, {"fDate", "fDay"} ), #"Changed Type" = Table.TransformColumnTypes( #"Expanded Column1", {{"fDate", type date}, {"fDay", Int64.Type}} ) in #"Changed Type" in Source
  • Now, you can use this table to build a Pivot Table to do the analysis.
  • Refreshing the query will automatically update the results for any contract addition or modifications.
  • The final query for the the above table is below and the file can be downloaded fromhere.let Source = (pStart as date, pEnd as date) =>    let Source =        if Date.EndOfMonth(pStart) = Date.EndOfMonth(pEnd) then {[fDate = pStart, fDay = Duration.Days(pEnd - pStart) + 1]}        else          List.Generate(            () => [fDate = pStart, fDay = Date.DaysInMonth(fDate) - Date.Day(fDate) + 1], each [fDate] <= pEnd, each let EoM = Date.EndOfMonth(Date.AddMonths([fDate], 1))              in if EoM > pEnd then [                    fDate = Date.AddDays(Date.EndOfMonth([fDate]), Date.Day(pEnd)), fDay  = Date.Day(fDate)                  ]                else                  [fDate = EoM, fDay = Date.Day(fDate)]          ), #"Converted to Table" = Table.FromList(        Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error      ), #"Expanded Column1" = Table.ExpandRecordColumn(        #"Converted to Table",        "Column1", {"fDate", "fDay"}, {"fDate", "fDay"}      ), #"Changed Type" = Table.TransformColumnTypes(        #"Expanded Column1",        {{"fDate", type date}, {"fDay", Int64.Type}}      )    in #"Changed Type"in Source

Conclusion

I hope you found this article useful and learned some new techniques as well? If you have any challenging allocation issues, feel free to share it with me.

Please leave your comments and questions below.

Have a nice day!
Please enable JavaScript in your browser to complete this form.Email *Download

I usedPower Query formatterto format and embed M codes in this blog. Its in BETA.

Fowmy Abdulmuttalib

Fowmy is the founder of ExcelFort Consulting and is a Microsoft Certified Solution Associate (MCSA). He is a qualified and well experienced Finance Professional with over 15 years of experience. Business Analytics. Business Intelligence, reporting, and business accounting are his major strengths.Post Views: 760

Like this:Like Loading...Spread the word

Video liên quan