Allocate Cost Across Periods
This came from one of my past participants. She was trying to allocate the cost of items across different periods, in her case, across financial years. Manually done, the steps should be: identify the start and end date, apportion the cost for these 2 periods. If the duration cross over more than 2 periods, then those period in between the 2 dates should have a constant periodic (yearly) cost. It looked like a pretty straight forward solution. So I thought. It turned out that the solution is
a lot more complicated and required the use of more complex formulas. If this was only for one cost item, I would have stopped pursuing a solution. But this was for multiple lines of cost, so the effort to do this manually was going to snowball. Calculating multiple lines of cost will be taxing for the person and increased the tendancy to make human errors as well. So I persisted and wa glad that a solution that was easy to manage could be found in the end and was described below. Before that, let me gave you the names of the data I used in my explanation.
1. Start with a HLOOKUP formula to look up for the start date across the starting date of all the periods. It will identify which period it fell into.
2. Compare this with the period starting date in row 1.
HLOOKUP date was less than the item start date, it will return false, an indication that the cost item does not start in that year.
3. Create another HLOOKUP without the square brackets [=HLOOKUP($B4,$F$1:$K$2,2)] and this time, lookup for the cost end date in the ending date of each year. Compare the result with the ending date of each year. If the ending date of the period is greater than the cost end date, it means that the cost is completed.
4. Now that the duration is identified, we can calculate the cost. For the first period of the cost allocation, it is not a full year. For in between periods, it is the starting date of the period. So we have to find the later date between the cost start date and period start date and take whichever is later. This can be achieved with the max formula. There are 12 months in each period and the cost has to be allocated on a per month basis. Hence the formula need to identify the start date of the month (either for the starting period of the year or the cost start date). The formula is
EOMONTH(MAX(F$1,$A4),-1)
5. The last period where the cost ends, the ending date of the cost is earlier than the period end date. In between periods, the period end date is earlier. We can use the min formula to identify it is the period end date or the cost end date. As it is calculating cost on a monthly basis, we need to identify the last day of the month for the date. The formula is
EOMONTH(MIN(F$2,$B4),0)
6. Using the 2 EOMONTH formulas, you can find the number of months for the cost allocation in each period using the DateDiff formula. Combining the formulas to gether, we get
DATEDIF(EOMONTH(MAX(F$1,$A4),-1),
EOMONTH(MIN(F$2,$B4),0),”m”)
7. With the number of months calculated, we can then calculate the cost apportioned to each period by multiplying with the monthly cost for the items
DATEDIF(EOMONTH(MAX(F$1,$A4),-1),
EOMONTH(MIN(F$2,$B4),0),”m”)*$E4
8. This formula should be applied for the periods between the cost start and end date. To exclude periods that fall outside this period, we can use the 2 sets of HLOOKUP formula we have created earlier. We can use the IF formula to combined all of them together to get this complete formula.
=IF(AND(HLOOKUP($A4,$F$1:$K$2,1)=F$1,HLOOKUP($B4,$F$1:$K$2,2)>=F$2),DATEDIF(EOMONTH(MAX(F$1,$A4),-1),EOMONTH(MIN(F$2,$B4),0),”m”)*$E4,0)
=IF(AND(HLOOKUP($A4,$F$1:$K$2,1)<=F$1,
HLOOKUP($B4,$F$1:$K$2,2)>=F$2)
,DATEDIF(EOMONTH(MAX(F$1,$A4),-1),
EOMONTH(MIN(F$2,$B4),0),”m”)*$E4,0)
9. Note the $ signs for each reference. With the proper $ placement, you can safely copy the formulas across the periods and down the rows for all the cost items.
New! Comments
Have your say about what you just read! Leave me a comment in the box below.
Share this page:
Enjoy this page? Please pay it forward. Here’s how…
Would you prefer to share this page with others by linking to it?
- Click on the HTML link code below.
- Copy and paste it, adding a note of your own, into your blog, a Web page, forums, a blog comment, your Facebook account, or anywhere that someone would find this page valuable.
<a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a><a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a>
Excel Courses for Business Professionals
Copyright © advanced-excel.com 2007 – 2019. All Rights Reserved. Privacy Policy
Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
advanced-excel.com is in no way associated with Microsoft
