AP: Run Monthly Reports and Reconcile Department Net Costs in Excel

Learn how to export monthly closing reports from your reporting portal and calculate departmental net costs and percentages in Excel.

September 25, 2026 By Autumn Bowman

Purpose

This guide explains how to export monthly report data from the reporting portal and reconcile department net cost allocations in Microsoft Excel. Completing this workflow allows you to subtotal transaction records, compute departmental percentage distributions, and verify grand totals against statement balances.

Scope

This procedure applies to finance, accounting, and operations team members responsible for end-of-month reconciliation and departmental reporting. It covers exporting monthly statements from the web portal, subtotaling driver net costs, and applying Excel formulas to determine net loss distributions across construction, executive, and replacement departments.

Export Monthly Report Data

Access the fleet management portal to run and download the monthly transaction report for the target accounting cycle.

Step #1: In the left sidebar, go to Reports > View my templates, locate the monthly report template, and choose Run Now from the actions menu. — In the left sidebar, go to **Reports** > **View my templates**, locate the monthly report template, and choose **Run Now** from the actions menu.

Step #2: Select the desired billing period from the Closing Date dropdown and click Download to save the report file. — Select the desired billing period from the **Closing Date** dropdown and click **Download** to save the report file.

Subtotal Driver Net Costs in Excel

Perform these steps after exporting the monthly data file to summarize net cost amounts grouped by driver. Once applied, each driver group will display an aggregated net cost total.

Step #1: Right-click the Driver First Name column header to sort or select the driver data. — Right-click the **Driver First Name** column header to sort or select the driver data.

Step #2: In the Excel ribbon, go to Data > Subtotal, configure the subtotal settings, and click OK to calculate totals by driver. — In the Excel ribbon, go to **Data** > **Subtotal**, configure the subtotal settings, and click OK to calculate totals by driver.

Reconcile Transactions with the Invoice Statement

Perform this reconciliation to verify that your calculated Excel driver net costs match the billing figures displayed on the web invoice statement. Once completed, your transactional sums will be confirmed against the external invoice balance.

Step #1: Open the invoice statement in your web browser to check the total billed balance. — Open the invoice statement in your web browser to check the total billed balance.

Step #2: In the Excel transaction report showing Account Name, Account Number, and Net Cost, calculate the sum of the Net Cost column. — In the Excel transaction report showing **Account Name**, **Account Number**, and **Net Cost**, calculate the sum of the **Net Cost** column.

Step #3: Compare the calculated net cost sum against the total shown on the invoice statement to confirm the balances match. — Compare the calculated net cost sum against the total shown on the invoice statement to confirm the balances match.

Calculate Department Net Cost Percentages

Perform this procedure after reconciling your statement transactions to establish each department's proportional share of net costs. Completing these steps calculates departmental percentages, applies standard percentage formatting, and populates the remaining rows.

Step #1: In the cell next to the first department name, enter the division formula, then click the percentage formatting button in the Home ribbon toolbar. — In the cell next to the first department name, enter the division formula, then click the percentage formatting button in the Home ribbon toolbar.

Step #2: Drag the fill handle down to copy the formula and percentage formatting to the remaining department rows. — Drag the fill handle down to copy the formula and percentage formatting to the remaining department rows.

Step #3: In cell D512, enter the department total calculation formula. — In cell D512, enter the department total calculation formula.

Step #4: In cell H515, calculate the subsequent department percentage. — In cell H515, calculate the subsequent department percentage.

Calculate Department Net Loss and Grand Totals

Perform these calculations after establishing the department percentages to allocate the overall net loss and compute the final grand totals. Once completed, the spreadsheet will display full net loss figures across construction, executive, and replacement departments.

Step #1: Click the net loss cell to calculate the initial department allocation. — Click the net loss cell to calculate the initial department allocation.

Step #2: Select the construction net loss cell and enter the allocation formula. — Select the construction net loss cell and enter the allocation formula.

Step #3: Select cell J507 to calculate the executive department net loss. — Select cell **J507** to calculate the executive department net loss.

Step #4: Select cell J509 to calculate the replacement department net loss. — Select cell **J509** to calculate the replacement department net loss.

Step #6: Click cell J516 to adjust the formula range references. — Click cell **J516** to adjust the formula range references.

Step #7: Click the final total sum cell to complete and confirm the grand total calculation. — Click the final total sum cell to complete and confirm the grand total calculation.

Step #8: Breakout OSC's total to reflect their department cost and send to Kirsta — Breakout OSC's total to reflect their department cost and send to Kirsta

Verify Final Financial Summary

Perform this final check after calculating all departmental breakdowns to ensure the summary figures balance. This confirms that all totals match across your financial report before final submission.

Step #1: Verify that the summary totals match the calculated sum across the financial report in cell J516. — Verify that the summary totals match the calculated sum across the financial report in cell **J516**.

FAQ

Q: Which template should be selected for end-of-month reconciliation?

A: Choose the Monthly Report template from the "View my templates" section under Reports.

Q: How do I group transactions by department or driver cost in Excel?

A: Use the Subtotal feature located on the Data tab after opening the downloaded spreadsheet to group and calculate transaction sums.

Q: How should department allocation percentages be formatted?

A: Apply standard percentage formatting using the percentage toolbar button after computing the ratio for each department row.