Incurred Expense, Accrual and Prepayment Excel Calculator

Description
PURPOSE OF TOOL

User-friendly Excel tool to calculate incurred expenses together with expense accruals, prepayments and payable balances for each month-end from a list of actual and expected expense items for a particular financial year. This tool helps to automate the expense accounting calculations on an accruals basis and intended as a supporting working file for the General Ledger expense postings.
The tool follows good practice financial modelling principles and includes instructions, line item explanations, checks and input validations.


KEY OUTPUTS

The tool is generic and not industry-specific. The key outputs include:
- Year to date (‘YTD’) incurred expense amounts and cash flow impacts for the selected month-end;
- Expense payable, accrual, prepayment and sales tax recoverable balances for the selected month-end;
- Chart showing the development of incurred expenses and cash flow impacts on a cumulative basis over the year;
- Chart showing the payable, accrual and prepayment balances expected across the financial year.


KEY INPUTS

Inputs are split into setup inputs and expense item inputs. All inputs include user-friendly line item explanations and input validations to help users understand what the input is for and populate correctly.
Setup Inputs:
- Name of business
- Currency
- Financial Year
- Latest month-end
- Expense categories (up to 10 naming categories)

Expense Inputs (for each actual or expected expense item with a current maximum of 250 expense items):
- Drop down to select whether expense item is actual or estimated
- Invoice number (if applicable)
- Expense category classification
- Expense description
- Expense supplier
- Incurred expense start date
- Incurred expense end date
- Amount excluding sales tax
- Sales tax amount (if applicable)
- Drop down to select whether sales tax is recoverable or not
- Payment date (if applicable)
- Payment reference (if applicable)


TOOL STRUCTURE

The tool contains, 5 tabs split into input ('i_'), output ('o_’) and system tabs. The tabs to be populated by the user are the input tabs ('i_Setup' and ‘i_Expense Inputs’). The output (‘o_Results’) tab uses the user-defined inputs to calculate and produce and present the calculation outputs and charts (see ‘KEY OUTPUTS’).

System tabs include:
- A 'Front Sheet' containing a disclaimer and instructions;
- A Checks dashboard containing a summary of checks by tab.


KEY FEATURES

Other key features of this tool include the following:
- The tool follows good practice financial modelling guidelines and includes instructions, line item explanations, checks and input validations;
- The tool calculates incurred expenses and expense balances for 1 financial year and allows for a maximum of 250 expense items per financial year.
- The tool does not have the functionality to allow expenses to be input in multiple currencies
- The tool allows expense items to be classified into 1 of 10 expense categories that can be set by the user.
- The tool uses ‘data tables’ to generate projected incurred expenses and expense balances for months other than the current month-end to allow the use to
- The tool allows for a maximum of 250 expense items per financial year (this can be extended if required).
- Business Name, currency, financial year and expense categories are fully customisable
- The tool includes instructions, line item explanations, checks and input validations to help ensure input fields are populated accurately;
- The tool includes a checks dashboard which summarises all the checks included in the various tabs making it easier to identify any errors.


MODIFICATIONS

If you require any be-spoke modifications to the tool, we are more than happy to assist with this. Please send us a message through the Eloquens site or contact us on: [email protected]


ABOUT PROJECTIFY

We are a small team of financial modelling professionals with experience working in Big 4 Business Modelling teams and strong experience supporting businesses with their financial planning and decision support needs. Our aim is to provide robust and easy-to-use tools that follow best practice financial modelling guidelines and assist individuals and businesses with common financial planning and analysis processes.
We are keen to make sure our customers are fully satisfied with the tools / models they purchase and will be more than happy to assist with any questions or support required following or in advance of purchase.

We are also always keen to receive feedback so please do let us know what you think of our products/offering by sending us a message or submitting a review.