Originally published: 16/08/2024 11:22
Publication number: ELQ-54413-1
View all versions & Certificate
Publication number: ELQ-54413-1
View all versions & Certificate
3-Statement Modeling - Sales on Account and Inventory Forecasting
A great tool for building startup financial projections that have receivables and inventory payables.
accounts receivableaccounts payablesales on accountpurchases on accountinventory modelingstatement modelincome statementbalance sheetcash flow statementfinancial statements
Description
I built this Excel spreadsheet tool to make it easier for financial projections that required correct formulas and logic for forecasting cash flows that involved selling goods on account (have accounts receivable) and purchasing inventory on account (having accounts payable). Additionally, there is a way in the sheet to account for when revenue is recognized (and possibly having unearned or deferred revenue) as well as prepaid expenses or cost of goods sold recognition.
All the logic exists on a single tab, making it user-friendly in terms of porting to your own financial models and spreadsheets.
Building a financial model that includes sales on account (receivables) and purchases on account (payables) within a three-statement financial model involves several layers of complexity and nuance. Here’s a breakdown of the key considerations and steps involved:
1. Recognition of Sales and Purchases
Sales on Account: When goods or services are sold but payment is deferred, these sales are recorded as accounts receivable. This means recognizing revenue at the time of sale, not when cash is received, adhering to the accrual accounting principle.
Purchases on Account: Similarly, when goods or services are purchased but payment is delayed, these are recorded as accounts payable. This entails recognizing expenses when they are incurred, regardless of when the payment is made.
2. Integration with Financial Statements
Income Statement: Sales and purchases impact the income statement at the time of transaction recognition through revenue and expense accounts.
Balance Sheet: The corresponding receivables and payables are recorded on the balance sheet. Receivables increase with sales on account and decrease when cash is received. Conversely, payables increase with purchases on account and decrease upon payment. For inventory, the inventory account goes up when purchases are made and it goes down when cost of goods sold are recognized. Also, the net income effect of the recognized revenue / expenses will flow to the 'equity' section of the balance sheet.
Cash Flow Statement: The cash flow statement is adjusted under the operations section. The changes in accounts receivable and payable from one period to the next are factored into net income to adjust for revenues and expenses that have not yet resulted in cash transactions.
3. Timing and Cash Flows
Operational Cash Flow: Managing the timing differences between when sales are made or expenses are incurred and when cash is actually exchanged is crucial. This affects the company’s liquidity analysis and cash flow planning.
Working Capital Management: Effective management of receivables (collection period) and payables (payment period) directly impacts the company's working capital and cash conversion cycle.
4. Assumptions and Scenarios
Credit Terms: Assumptions about payment terms, such as net 30 or net 60 days, need to be clearly defined for both receivables and payables. This model makes it easy to have any terms you may need to analyze. Simply define the % of cash collected in each month. This pattern will apply to all sales entered. The same goes for when revenue is recognized relative to the sale month. Month 1 is considered the sale month in this case. Any terms are configurable.
Collection and Payment Patterns: Modeling likely patterns in collections (including potential delinquencies) and payments (taking advantage of credit terms or early payment discounts) is essential.
Bad Debt Provision: There should be an assumption for bad debts where a portion of receivables may not be collectible. In this model, you would simply never enter the full 100% of cash collections and that would accrue in accounts receivable.
5. Sensitivity Analysis
Impact on Liquidity Ratios: Changes in the speed of collections or payments can significantly affect liquidity ratios and the overall financial health assessment.
Scenario Planning: Including different scenarios for collection efficiency and payment terms helps in understanding potential impacts under various economic conditions.
6. Automation and Checks
Automated Calculations: Ensuring that changes in assumptions automatically update across all financial statements is vital for maintaining consistency and accuracy.
Balancing and Error Checking: Regular checks to ensure that all entries balance and that the financial statements reflect an accurate picture of the business's financial status.
By carefully modeling these elements, you can create a robust three-statement financial model that provides valuable insights into a company's operations, financial health, and cash flow dynamics.
This template is also included in two bundles:
I built this Excel spreadsheet tool to make it easier for financial projections that required correct formulas and logic for forecasting cash flows that involved selling goods on account (have accounts receivable) and purchasing inventory on account (having accounts payable). Additionally, there is a way in the sheet to account for when revenue is recognized (and possibly having unearned or deferred revenue) as well as prepaid expenses or cost of goods sold recognition.
All the logic exists on a single tab, making it user-friendly in terms of porting to your own financial models and spreadsheets.
Building a financial model that includes sales on account (receivables) and purchases on account (payables) within a three-statement financial model involves several layers of complexity and nuance. Here’s a breakdown of the key considerations and steps involved:
1. Recognition of Sales and Purchases
Sales on Account: When goods or services are sold but payment is deferred, these sales are recorded as accounts receivable. This means recognizing revenue at the time of sale, not when cash is received, adhering to the accrual accounting principle.
Purchases on Account: Similarly, when goods or services are purchased but payment is delayed, these are recorded as accounts payable. This entails recognizing expenses when they are incurred, regardless of when the payment is made.
2. Integration with Financial Statements
Income Statement: Sales and purchases impact the income statement at the time of transaction recognition through revenue and expense accounts.
Balance Sheet: The corresponding receivables and payables are recorded on the balance sheet. Receivables increase with sales on account and decrease when cash is received. Conversely, payables increase with purchases on account and decrease upon payment. For inventory, the inventory account goes up when purchases are made and it goes down when cost of goods sold are recognized. Also, the net income effect of the recognized revenue / expenses will flow to the 'equity' section of the balance sheet.
Cash Flow Statement: The cash flow statement is adjusted under the operations section. The changes in accounts receivable and payable from one period to the next are factored into net income to adjust for revenues and expenses that have not yet resulted in cash transactions.
3. Timing and Cash Flows
Operational Cash Flow: Managing the timing differences between when sales are made or expenses are incurred and when cash is actually exchanged is crucial. This affects the company’s liquidity analysis and cash flow planning.
Working Capital Management: Effective management of receivables (collection period) and payables (payment period) directly impacts the company's working capital and cash conversion cycle.
4. Assumptions and Scenarios
Credit Terms: Assumptions about payment terms, such as net 30 or net 60 days, need to be clearly defined for both receivables and payables. This model makes it easy to have any terms you may need to analyze. Simply define the % of cash collected in each month. This pattern will apply to all sales entered. The same goes for when revenue is recognized relative to the sale month. Month 1 is considered the sale month in this case. Any terms are configurable.
Collection and Payment Patterns: Modeling likely patterns in collections (including potential delinquencies) and payments (taking advantage of credit terms or early payment discounts) is essential.
Bad Debt Provision: There should be an assumption for bad debts where a portion of receivables may not be collectible. In this model, you would simply never enter the full 100% of cash collections and that would accrue in accounts receivable.
5. Sensitivity Analysis
Impact on Liquidity Ratios: Changes in the speed of collections or payments can significantly affect liquidity ratios and the overall financial health assessment.
Scenario Planning: Including different scenarios for collection efficiency and payment terms helps in understanding potential impacts under various economic conditions.
6. Automation and Checks
Automated Calculations: Ensuring that changes in assumptions automatically update across all financial statements is vital for maintaining consistency and accuracy.
Balancing and Error Checking: Regular checks to ensure that all entries balance and that the financial statements reflect an accurate picture of the business's financial status.
By carefully modeling these elements, you can create a robust three-statement financial model that provides valuable insights into a company's operations, financial health, and cash flow dynamics.
This template is also included in two bundles:
This Best Practice includes
1 Excel model and 1 Tutorial Video
Further information
Produce all aspects of the income statement, balance sheet, and cash flow statement in regards to sales on account and purchases on account.
If your business has receivables / payables and you need to forecast cash flows.
