Prepaid Expenses - Automated

Overview

Prepaid Expenses – Automated is a premium Excel-based tool that fully automates the monthly amortization of prepaid expenses. Designed for finance professionals who want accurate journal-ready entries without relying on formulas or manual adjustments, this template uses Power Query and pivot tables to generate clean, auditable results based on your inputs.

This tool is ideal for controllers, accountants, FP&A teams, and auditors who need to streamline month-end workflows, reduce risk of errors, and maintain full visibility into their prepaid balances.

This tool supports a wide range of use cases:

  • Accountants can streamline monthly close by automating prepaid expense amortization and journal entry generation.
  • Auditors can independently substantiate client amortization logic, test account balances, and review detailed amortization schedules.
  • FP&A teams can model future amortization of prepaids to support accurate budgeting, forecasting, and balance sheet planning.

Key Highlights:

  • No formulas required—calculations are handled entirely through Power Query
  • Automatically generates double-entry journal entries (debit and credit)
  • Produces dynamic reports and audit support using built-in pivot tables
  • Fully protected structure: Power Query, VBA, and formulas are locked
  • Includes dropdowns for cleaner data entry and fewer errors

Once your setup and data entry are complete, a single refresh produces all amortization logic, accounting entries, and reporting outputs—ready to review, upload, or present.

Quick Start Guide

Getting Started

Set Up Your File

Before entering any data, complete the setup tabs to ensure downstream automation and reporting functions correctly.

  • Company Tab: Enter your Company ID and Company Name. There is an unlimited number of Company's that can be entered.
  • Chart of Accounts (COA): Include only the prepaid and expense accounts relevant to prepaid amortization.
  • Dimension Tab (optional): Use this for departments, cost centers, or business units. If you don’t need segmentation, we recommend entering a default:
    ID: 0000
    Name: Default

All values entered in these tabs are pulled into dropdowns and used to power relationships and slicers.

Input Your Data

On the Prepaid Expenses tab, enter a row for each prepaid item:

  • Vendor: Supplier or payee
  • Description: Description of the service or prepaid
  • Amount: Total prepaid amount
  • Addition Date: When the prepaid was booked to the balance sheet. This can be after the amortization start date.
  • Amortization Start / End Dates: The date range over which the expense will be recognized
  • Prepaid Account: Asset account (e.g., 1300)
  • Amortization Expense Account: Account to charge monthly (e.g., 6010)
  • Company / Dimension: Selected from dropdowns for segmentation

Only valid entries with matching dropdown selections will be picked up by the automation engine.

Refresh and Calculate

Once your prepaids are entered:

  1. Go to the Data tab in Excel
  2. Click Refresh All

The tool will:

  • Run Power Query logic to calculate monthly amortization
  • Generate balanced double-entry journal entries
  • Feed pivot tables and reporting tabs

Note: You only need to refresh when changes are made to the Prepaid Expenses tab.

Review Your Reports

The following tabs provide dynamic reports and supporting schedules:

  • Journal Entry: Debits and credits for each monthly amortization line
  • Reconciliation: Pivot table showing ending prepaid balances by company, account, and dimension
  • Auditor Report: Full amortization schedule by vendor, with line-level support.
  • GL Detail: Clean, expandable ledger detail with dates, accounts, and amounts.

Use slicers or pivot filters to drill down by company, account, month, vendor, or dimension.

How It Works

The engine applies straight-line amortization across the selected period. The total amount is divided evenly across months (with rounding adjustment in the final month).

Key logic elements:

  • If the prepaid addition date occurs after the amortization start date, the tool automatically adjusts and begins amortization from the later date and catches up amortization missed in the first month.
  • The system assumes monthly amortization—not daily proration—making it perfect for financial reporting and upload-ready journal entries.
  • If needed, rounding logic ensures the total amortized matches the original prepaid amount exactly.

No formulas. No risk of overwrite. Clean automation with audit traceability.

Best Practices

  • Use a default dimension if not tracking detailed segments

    Recommended: 0000 - Default
  • Limit your COA list to relevant prepaid and expense accounts

    This keeps dropdowns clean and reduces the chance of misclassification
  • Refresh after any updates

    Any time you add or edit entries, use Data > Refresh All

Support and Troublshooting

If something isn’t working as expected:

  • Check your dates:
    Amortization End Date must be after both the Addition Date and Amortization Start Date

    Using an invalid date range (e.g., start after end) will break the query
  • Refresh Power Query:
    Go to Data > Refresh All

    If this fails, double-check that all drop-downs are populated and dates are valid
  • Journal not populating?
    Check for blank or invalid values in the input table

Still need help?
📧 Email us at support@automatedaf.ai and include a screenshot of your issue.