Assumptions Report No. 7 - Year-End Credit Balances

Help Home  Previous  Next

Guidance on Entering Assumptions > Assumptions Report No. 7 - Year-End Credit Balances

Whereas most items in earlier assumption reports are concerned with projecting sales and profit levels, this assumption report is used to specify year-end balances to improve the accuracy of the cashflow projections. These are often more critical to a business’s survival or growth than its profit projections.

 

Accounts receivable (Debtors outstanding)  - days' sales payable at end of each year
Defer receipt of payments from customers into the following year by entering assumptions which reflect the days' credit given to customers. Leave this row blank if all customers pay cash. Otherwise, enter assumptions which reflect the number of days sales unpaid at year-ends.

Typical values would be in the range 0 to 90 days depending on credit terms and inter-year growth rates as discussed below. It is important to include any applicable VAT or similar output taxes when determining the amount of credit given to customers.

If sales (including any related VAT or similar output taxes) for Year 1 were $300,000 according to Assumptions Report No 1 and 60 was entered as the value for Year 1 in Assumption Report No. 7, then $49,320 (= 300,000*60/365) of first-year sales would be paid for during the second year. Thus, $250,680 (= 300,000-49,320) would automatically show up as received in the Cashflow Projections during Year 1 and the remaining $49,320 would appear in the balance sheet as accounts receivable for Year 1. This amount would be shown as received from customers in the Cashflow Projections for Year 2.

This approach works fine when inter-year sales are steady and when the value of credit given in the first month of any year is much the same as that for the last month of the year. When year-on-year sales are growing rapidly this approach understates year-end credit and, consequently, the days' credit should be increased to compensate. Here is an example showing how this can be done:

Assume that a business gives an average of 60 days' credit on sales.
Projected sales are $300,000 for Year 1 and $500,000 for Year 2.  On this basis, the likely monthly sales at the end of Year 1 would be approximately $33,333. This was calculated by averaging sales for the two years and then dividing by 12 months to get the average monthly sales as follows:

(300,000+500,000)/2/12 =  33,333

On this basis, 60 days' credit would equate to $65,753 (= 33,333*12*60/365). Based on Year 1 sales, this credit would be equivalent to 80 (= 65,753/300,000*365) days' sales as compared with only 60 days' sales if there had been no growth in inter-year sales.

 

Accounts payable (Creditors outstanding) for material/packaging & goods - days' purchases payable at end of each year
Defer payments for purchases of materials/packaging and goods for resale into the following years by entering values for each year which reflect the days' credit received from suppliers. Leave this row blank if no credit is secured. Otherwise, enter assumptions which reflect the number of days' purchases unpaid at year-ends.

Typical values would be in the range 0 to 90 days depending on credit terms and inter-year growth rates as discussed below. It is important to include any applicable VAT or similar output taxes when determining the amount of credit taken from suppliers.

If purchases (including any related VAT or similar input taxes) for Year 1 were $300,000 according to Assumptions Report No 2 and 60 was entered as the value for Year 1 in Assumption Report No. 7, then $49,320 (= 300,000*60/365) of first-year purchases would be paid for during the second year. Thus, $250,680 (= 300,000-49,320) would automatically show up as paid out in the Cashflow Projections during Year 1 and the remaining $49,320 would appear in the balance sheet as accounts payable for Year 1. This amount would be shown as paid to suppliers in the Cashflow Projections for Year 2.

This approach works fine when inter-year purchases are steady and when the value of credit outstanding in the first month of any year is much the same as that for the last month of the year. When year-on-year purchases are growing rapidly this approach understates year-end credit and, consequently, the days' credit should be increased to compensate. Here is an example showing how this can be done:

Assume that a business gets an average of 60 days' credit on purchases.
Projected purchases are $300,000 for Year 1 and $500,000 for Year 2.  On this basis, the likely monthly purchases at the end of Year 1 would be approximately $33,333. This was calculated by averaging purchases for the two years and then dividing by 12 months to get the average monthly purchases as follows:

(300,000+500,000)/2/12 =  33,333

On this basis, 60 days' credit would equate to $65,753 (= 33,333*12*60/365). Based on Year 1 purchases, this credit would be equivalent to 80 (= 65,753/300,000*365) days' purchases as compared with only 60 days' purchases if there had been no growth in inter-year purchases.

 

Payroll taxes & benefits outstanding at year- end as % total taxes/benefits for each year
In most situations, payroll taxes and benefits for the last month of a year may be paid during the first month of the following year. Enter values to reflect this delayed payment. Typical values would be in the range 0 to 3 depending on the importance of these taxes, the payment interval and inter-year growth rates as explained in the following example:

Lets say that payroll taxes and benefits represent 25% of total payroll, taxes and benefits and that the taxes and benefits are paid a month in arrears. On this basis, the value to be entered would be 2.08 (i.e. 25% divided by 12 months). As in the case of receivables and payables, this value might need to be increased slightly if inter-year payrolls are growing very rapidly.

 

Capital expenditure outstanding at year-end as % total capital expenditure for each year
Enter values to reflect the proportions of capital expenditure payments for each year that are outstanding at year-ends. Typical values could be in the range 0 to 25 depending on the timing of this expenditure and credit terms attaching.

 

Federal/state tax payable (recoverable) at year-end as % total tax for each year
Enter 100 if all federal/state taxes payable (as calculated within Income Statement) are due to be paid after year-ends. Reduce this value to take account of any advance tax payments made within years.

 

Dividends payable at year-end as % dividends declared for each year
Enter 100 if all dividends declared (in Assumptions Report No. 5 ) are due to be paid after year-ends. Reduce this value to take account of any interim dividend payments made inside years.

 

Net input taxes payable to State (recoverable) at year-end as % total net input taxes for that year
These input taxes refer to GST, VAT etc. which apply in Canada, EU etc. No input taxes operate in the US and consequently this row can be ignored (i.e. set to zeros) by US-based businesses.  Where these taxes apply, enter values which reflect the percentages of taxes for each year which are paid after year-ends. Depending on payment intervals, these values entered could range from 8 (based on a payment interval of one month i.e. 1/12 equals 8%) to, say, 25 (for a payment interval of three months i.e. 3/12 equals 25%).

See Also:

Assumptions Report No. 1 - Sales Forecasts

Assumptions Report No. 2 - Materials/Goods, Other Direct Costs & Purchases

Assumptions Report No. 3 - Overhead Expenses

Assumptions Report No. 4 - Fixed Assets

Assumptions Report No. 5 - Funding, Interest Rates & Related Items

Assumptions Report No. 6 - Sales & Related Taxes

Opening Balance Sheet

Prior-Year Income Statement

Detailed Guidance on Generating Projections

Guidance on Entering Assumptions

Changing Exl-Plan



Online:  Support   Purchase