07.01 Building a Management Reporting Workbook

07.01 Building a Management Reporting Workbook

In this session we are going to build a month-end reporting pack from scratch using the FastClose Excel Add-In. By the end you will have a four-tab workbook containing a Profit & Loss, a Balance Sheet, an AR Aged Debt report, and an AP Aged Debt report — something that looks and behaves like a real reporting pack rather than a raw data export.

Rather than working through the Add-In menu by menu, we are going to introduce each feature at the point where it naturally solves a problem. That way, by the time we are done, you will not just know what each button does — you will know why you would reach for it.

Here is a video, or read below — don't forget the exercises at the bottom.

What We Are Building

The finished workbook will have four tabs:

Let's get started.

Step 1 — Getting Set Up

Open a fresh Excel workbook. With the FastClose Add-In installed, you will see a FastClose tab in the Excel ribbon. Click on it.

Navigation and Setup

Click Open and sign in if prompted. The home dialog will appear showing the reports available to you.

Home Dialog_Report selection

Before opening anything, rename the first worksheet tab from Sheet1 to P&L. Right-click the tab at the bottom of the screen and choose Rename.

image

Keeping tabs named clearly from the start is good practice — and as we will see in the next session, the worksheet name feeds through into Excel Table names too, so it pays to get it right early.

Step 2 — Opening the P&L Report

With the P&L tab selected, click Open from the FastClose ribbon and navigate to:

Solutions → 01 Finance → 01 GL P&L's, Balance Sheets etc. → Using Epicor Budgets → Positive Signed (+ve Revenues and +ve Expenses)

Select P&L Report

The report loads into the worksheet, formatted exactly as it was designed in the FastClose Designer.

P&L Opened

Look at the filter panel on the right. This is where you control what the report shows — Company, year, department, and so on. Set the filters to the Company and Year you want to report on, then click Run Report.

P&L Loaded

The P&L is now live. Before we move on, let's tidy it up.

Step 3 — Worksheet Options

Click Worksheet Options from the FastClose ribbon.

Worhskeet Options Ribbon
P&L Worksheet Options

For a P&L you will typically want:

Leave Display Results as Table off for now — we will come back to that in the next session.

Close the dialog and re-run the report to apply the changes.

Step 4 — Adding a Logo

A P&L landing on cell A1 leaves no room for branding. The Move feature solves this by repositioning the report further down and across the sheet, freeing up space above for a logo, report pack title, distribution list, confidentialty notice, or other header content.

Click Move from the FastClose ribbon and enter the cell where you want the top-left corner of the report to sit — for example B6 — which leaves five rows above and a column to the left.

Move 3
Move B6

Re-run the report and it will now appear from column B, row 6 onwards.

P&L B6

In the space above, insert your company logo using Insert → Pictures. Resize and position it to sit neatly in the top-left area. You might also add a report pack title in a cell above the data — something like "Month End Management Pack" — formatted to stand out.

P&L Logo Added

This one step transforms the sheet from a raw report into something that looks like a professional document.

Step 5 — Adding the Balance Sheet Tab

Click the + button at the bottom of the screen to add a new worksheet and rename it Balance Sheet.

With the Balance Sheet tab active, click Open from the FastClose ribbon and navigate to:

Solutions → 01 Finance → 01 GL P&L's, Balance Sheets etc. → Balance Sheet - Positive Signed (+ve Assets and +ve Liabilities & Equity)

Balance Sheet

Set the filters to the same Company and Year as your P&L (note this report presents the filters slightly differently to select Year/Period) and click Run Report.

Balance Sheet 1

Hierarchy options

Balance Sheet reports typically include a hierarchy — assets broken down into current and non-current, liabilities split similarly. Open Worksheet Options and explore the hierarchy settings.

These settings can also be set in the hierarchy options in designer. Setting saved in designer will be pulled through into Excel.

BS Worksheet Options

Rows to Depth set to 2 is a good starting point — this includes the top two levels and excludes everything below i.e. you can't expand to show them.

Other options are:

Re-run the report to apply.

BS Rows to Depth 2

Step 6 — Adding the AR Aging Tab

Add a new worksheet and rename it AR Aging.

Click Open and select the AR 01 Aging (As Of) report from the Accounts Receivable Templates.

Open AR Aging

Set the Company filter and — importantly — the Aging (As Of Date). This is the date the aging buckets are calculated from, so it needs to reflect the correct point in time for your reporting period.

AR Aging New

Run the report.

AR Aging New with data

Open Worksheet Options for this tab. An aging report is a flat list rather than a hierarchy, so the display options will look slightly different — four options are shown rather than three. Turn off Show Subtotals and Grand Totals unless you specifically want a grand total row at the bottom of the list.

AR Aging Worksheet Options

Step 7 — Adding the AP Aging Tab and the Problem It Creates

Add one more worksheet and rename it AP Aging. Open the AP 01 Aging (As Of) report from the Accounts Payable Templates.

Open AP Aging

Set the company filter and, importantly, the Aging (As Of Date).

AP Aging Loaded

Run the report.

AP Aging Run

You now have four tabs — P&L, Balance Sheet, AR Aging, and AP Aging. The workbook is looking like a proper pack.

But there is a problem lurking. The AR and AP aging reports both have an Aging (As Of Date) filter, and that date needs to be identical on both tabs. If it ever drifts — one tab on the last day of Period 9, the other still on Period 8 — the two reports are no longer comparable and the pack is misleading.

At month-end, when you roll the pack forward, you would need to update the aging date on both tabs separately. That is easy to forget, and easy to get wrong.

Linked Filters solve this.

Step 8 — Setting Up Linked Filters

Linked Filters let you tie the same filter field across multiple reports so that changing it in one place updates all of them automatically.

Click Linked Filters from the FastClose ribbon.

Linked FIlters Location

Click Add to create a new filter group. Give it a name — Aging Date works well here. Then select the aging date filter field from the AR Aging report, and add the matching field from the AP Aging report to the same group.

Add Linked Filter Set

Click Save. A small link icon will now appear next to the aging date filter on both tabs.

Linked FIlter Icon

From this point on, changing the aging date on either tab will automatically update the other. Try changing a date on one of the Aging tabs and you will see it changes on the other too.

While you have the Linked Filters dialog open, it is worth adding a second group for the Company filter — linking the company across the P&L, Balance Sheet, AR Aging, and AP Aging tabs so those update together too.

Step 9 — Drilling Into the Data

The workbook is almost complete. Before saving, let's look at one of the most useful features for day-to-day use — drilling.

Drilling lets you click on any value in a FastClose report and navigate straight into the detail behind it. On a P&L, that might mean seeing the individual journal entries that make up a cost line. On an ageing report, it might mean opening the individual invoices sitting in a particular bucket.

Switch to the P&L tab. Click on a value you want to investigate — for example, a cost line that looks higher than expected.

There are two ways to trigger a drill:

Right Click Drill
Drill Toolbar

The drill result opens either in a new workbook or a new worksheet tab, depending on the Drill to Report in New setting in Add-In Options. A new worksheet tab is usually the more practical choice during a review — it keeps everything in one place.

One thing to bear in mind — the cell you click determines what the drill reports on. On a report pivoted by period, clicking in the wrong column will drill into the wrong period's data. If the results look unexpected, check you have selected a cell in the correct column before trying again.

Drill 3

Drilling is read-only — it will never change any underlying data. Use it freely to investigate anything that needs a closer look.

Step 10 — Saving the Workbook

Go to File → Save As and choose your preferred location — a local drive, a network share, or OneDrive.

If saving to OneDrive, disable Auto Save first. Auto Save is the toggle in the top-left corner of Excel. When Auto Save is active and multiple people have the workbook open simultaneously, changes can overwrite each other in ways that cause problems for FastClose.

Autosave

Save the file as a standard .xlsx workbook.

Step 11 — Reopening the Workbook

Close the workbook and reopen it to see what happens.

FastClose automatically detects the linked reports and reloads the Add-In. Report layouts, filter settings, worksheet options, and linked filter groups are all restored exactly as you left them.

What does not happen automatically is a data refresh. The figures you see are from the last time you clicked Run. To update everything in one go, click Run All Reports from the FastClose ribbon.

Run All Reports

If the underlying report design has been updated in the FastClose Designer since you last opened the workbook — a new column added, a calculation changed — click Reload on the relevant tab to pull in the updated structure. Bear in mind that Reload may overwrite formatting or formulas applied directly in the worksheet, so use it deliberately rather than routinely.

What We Have Built

You now have a four-tab management reporting workbook that:

In the next session we will look at returning FastClose data as an Excel Table, which opens up a new set of possibilities for building formulas and charts on top of the data in your workbook.

Exercises

Now try the downloadable exercises below:

07.01 Building a Management Reporting Workbook - Exercises.pdf

Your feedback helps us do better, to fill in a short survey on this tutorial, click here.

Move on to 07.02 Building An Executive Summary with Excel Tables

Powered By