And You Thought We Were Done Talking About Dynamics 365 Business Central and Budgets…

In It's Budget Season, Part 1 and Part 2, we built G/L budgets in Business Central and then reported against them. Once the mechanics are down, another question quickly surfaces: where do the budget numbers come from when you can't rely on historical actuals? Fixed asset depreciation is a perfect example. Next year's depreciation isn't last year's expense plus a percentage, it has to be projected from the assets you own and their depreciation schedules. Fortunately, Business Central already has that information.

In this article, we'll use the Fixed Asset Projected Value (Excel) report to project depreciation for the coming budget year and turn that projection into monthly budget amounts.

Some Budget Numbers Need to Be Projected

Historical actuals are an excellent starting point for many budget accounts, but depreciation is an exception. An asset may become fully depreciated partway through the budget year. Another may have only part of a year's depreciation remaining. New acquisitions add depreciation that doesn't exist in history at all. A blanket percentage increase accounts for none of that.

Business Central, however, knows the depreciation schedule for every fixed asset on your books. Rather than estimating next year's depreciation from history, why not ask Business Central to project it? That question led me to the Fixed Asset Projected Value (Excel) report, and that's where this gets interesting.

The Fixed Asset Projected Value (Excel) Report

Search for Fixed Asset Projected Value (Excel) (report 4413). This newer Excel-based report projects how fixed asset ledger entries would depreciate for the dates and depreciation book you specify on the request page. The resulting workbook contains two worksheets:

1. Projected Value — a pivot table showing projected posting date, posting type, number of depreciation days, depreciation amount, and projected book value, with filters and slicers for class, subclass, location, and department or project.
2. FixedAssetData — the underlying detail behind the pivot table.

That sounds like almost exactly what we need for budgeting.

I Want a Year of Depreciation — By Month

My objective was straightforward: project depreciation for the next fiscal year so I could use the monthly amounts in a G/L budget. My first instinct was to enter the first and last dates of the fiscal year and turn on Use Accounting Period. My accounting periods are monthly, so that seemed logical.

The result wasn't what I expected. Rather than a projection for each month of the fiscal year, the output returned one large depreciation period. The annual amount was there, but I needed the amounts by month.

So, back to the request page.

Number of Days Gets You Close

The request page also includes Number of Days. By default, Business Central calculates depreciation using a standardized 360-day year of twelve 30-day months (unless Fiscal Year 365 Days is selected on the depreciation book), which makes a 30-day interval the natural choice for monthly budgeting. With Use Accounting Period turned off and Number of Days set to 30, the Fixed Asset Data worksheet showed projected depreciation in 30-day intervals across the entire fiscal year.

Closer, but not quite there. A 30-day depreciation interval and a fiscal month aren't necessarily the same thing: the depreciation calculation runs on 30-day months, while your G/L budget runs on actual posting dates and fiscal periods. I'd still have to line each 30-day amount up with the right fiscal month by hand before it went into the budget.

Which raised the better question: why didn't Use Accounting Period work in the first place?

The Real Fix: Create Next Year's Accounting Periods

The answer was simple. The accounting periods for the next fiscal year didn't exist yet. Use Accounting Period can only split the projection by periods that are actually in the Accounting Periods table. With nothing set up for the budget year, The accounting periods for the dates I was asking the report to use simply didn't exist yet.