• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Allocating spending over periods automatically in budget

H

Hayley Vogg

New Member
Hello! I am trying to figure out how to allocate spending over periods automatically in a budget spreadsheet I have for our IT Department. On the Itemized Expense and Cap Ex tabs in the workbook, expenditures are automatically sorted into monthly and yearly spending categories on the first two tabs based on their IT Code. This works well, except it does not allow for prorating spending. For example, if we were to buy a laptop for $3600 on April 1st, 2015 that has a useful life of 36 months, all $3600 would not be recorded in April. $100 would be recorded each month for 36 months. I do not know how to do this automatically. I have added a column on the expenditures tabs named "Periods" which refer to the number of months that the item's cost need to be dispensed across. I have attached the spreadsheet and would greatly appreciate any help on this. Thank you!
 

Attachments

Hi Hayley,
I am unable to comprehend what exactly you require assistance on. Request you to kindly share a working example you want at the end. Please talk about Laptop for instance. I couldn't find laptop in "2015 Budget Summary " sheet tab.
 
Hey Ravi,
I will explain further. When an item as added to the "Itemized Expense" and "Cap Ex" tabs, the invoice cost of that item is put in the appropriate IT Code category on the Budget Summary and Monthly Summary tabs in reference to the date on the invoice. The Account Title column on the first two tabs is just explaining what the IT Code is on the tab.

Referencing the laptops (row 13 on the Itemized Expenses tab) and how the workbook is set up currently, all $3000 of the cost of the laptops will be expensed on the budget for the month of January. However, the cost of the laptops need to be depreciated over the length of 36 months, as that is the useful life of the laptop. So, ideally, I would like the period cost (which refers to one month) to be applied to each month in the 36 month useful life. For example, if the laptops cost $3,000 with a useful life of 36 months, January should reflect an expense of $83.33 ($3000/36), February should reflect an expense of $83.33, and so on and so forth until the last month in the useful life, which would be January 2018.

On the 2015 Budget Summary tab, changing the year at the top right corner of the sheet will change the calculated actual values for both the Budget Summary sheet as well as the Monthly Expenses Summary sheet accordingly. Therefore, if I can get this formula to work, this sheet could serve as a budgeting workbook indefinitely, as the amortized or depreciable values of the various expenses would be dispensed over the entire useful lives of the items. Does that make sense?
 
Hi:
The easiest way is to add a helper column
Code: 
=[@[Invoice Amount]]/[@Periods]
in your itemized table and use that for Sum if formula, you can hide the helper column in your itemized table if needed. I am using excel 2010, your table slicers were not visible for me, but my guess it won't make much difference when you add a helper column.

Thanks
 
Thanks for replying, Nebu. The formula is helpful, but does not quite do what I need it to. The formula divides the invoice into the cost per period, but it does not dispense the formula over the time length of the number of periods. For example, if there were an item expense on January 1, 2015 that has two periods, I need the period value to be dispensed over both January and February. Right now it is only dispensing over January. Does that make sense?
 
You must log in or register to reply here.
Back
Top