PURPOSE OF MODEL
User-friendly Excel model for the preparation of a 1-year daily cash flow forecast to manage / pre-empt liquidity requirements within a new or existing business.
The model allows the user to project cash flow movements and balances across 5 bank/cash operating accounts, individually or on a total basis, with functionality to model recurring receipts and payments, settlement of aged debtors and creditors and receipts/payments from forecast purchases and sales in an automated manner.
The model follows best practice financial modelling principles and includes instructions, checks and input validations.
The model is generic and not industry-specific. The key outputs include:
- Projected cash flow breakdown of balances and movements presented on a daily and monthly basis.
- Dashboard which includes the following:
- Summarised projected cash flow movement and balances
- Chart showing net cash movement by month by category
- Line Graph showing cash balances on total basis and by account
Assumption Inputs include:
- Name of business
- First projection date
- Bank / Cash account details including Name, Account number, latest balance and minimum thresholds
- Public holidays
- Recurring receipt / payment details
- Debtor and creditor details including credit terms aged balances and forecast purchases and sales.
The model contains, 11 tabs split into input ('i_'), calculation ('c_'), output ('o_’) and system tabs. The tabs to be populated by the user is the input tabs ('i_Assumptions'). The calculation tab uses the user-defined inputs to calculate and produce the projection outputs which are presented in 'o_Total CF’ and ‘o_Dashboard’
System tabs include:
- A 'Front Sheet' containing a disclaimer, instructions and contents;
- A Checks dashboard containing a summary of checks by tab.
Other key features of this model include the following:
- The model follows best practice financial modelling guidelines and includes instructions, checks and input validations to help ensure input fields are populated accurately;
- The model allows for a 12-month rolling projection on a daily basis and summarised on a monthly basis;
- The model is not password protected and can be modified as required following download;
- The model allows for the following number of categories for each line item (these can be easily expanded if required):
- Bank/Cash accounts – 5 accounts
- Recurring receipts and payments – 20 items on monthly, quarterly or yearly recurring basis.
- Debtor categories – 25 categories;
- Creditor categories – 25 categories;
- The model allows the user to model 3 separate revenue streams on a Price x Volume basis;
- Costs are split into: variable and other costs for better driver-based forecasting;
- Business Name, currency, starting projection period are fully customisable;
- Debtor, Creditor, recurring receipts and payments descriptions are fully customisable;
- The model includes a checks dashboard which summarises all the checks included in the various tabs making it easier to identify any errors.
MODIFICATIONS & SUPPORT
If you require any be-spoke modifications or support, we are more than happy to assist. Please send us a message below or contact us on firstname.lastname@example.org
Daily Financial Liquidity Financial Projection Excel Models
1 Populated Excel Model and 1 Unpopulated Excel Model