Intangible assets balances are calculated in much the same way by adding the purchases of intangible assets as per the cash flow statement and deducting the amortization charges which need to be entered on the income statement. The calculation of the investments balances on the balance sheet is a bit simpler in that only the purchases of new investments as per the cash flow statement is added to the previous month's balance and there is no depreciation or amortization on investments.
The inventory balances on the balance sheet are calculated based on the inventory days assumption which is specified on the Assumptions sheet.
The number of days that are entered here is applied to the weekly cost of sales in order to calculate the appropriate inventory balance. This calculation is based on the number of days in a week and the difference between the days in the assumption and full week days. Example: If you enter an inventory days assumption of 30 days, the entire cost of sales value for four weeks will be included in the inventory balance. After including the four weeks, there is a difference of 2 days between the 30 days assumption and the total days in four weeks.
The week 5 cost of sales balance will therefore be used, divided by 7 days and multiplied by the 2 remaining days. Note: The above calculation principle is applied regardless of the number of days which are entered as the inventory days assumption on the Assumptions sheet even if the value of the inventory days assumption requires the inclusion of multiple weeks. This method of calculation is the most accurate way of projecting inventory balances even for businesses where there is significant sales volatility.
Note: If your business does not carry inventory, you can simply enter a nil value in the inventory days assumption on the Assumptions sheet. The inventory line on the balance sheet will then also contain nil values. If you want to include variable weekly inventory days, you can do so by changing the inventory days assumption in the Workings section of the balance sheet which has been included below the section with the ratios.
Simply replace the formula which links the inventory days assumption to the value on the Assumptions sheet by overwriting it with the appropriate inventory days value. The trade receivables balances on the balance sheet are calculated based on the debtors days assumption which is specified on the Assumptions sheet. The debtors days number can be determined based on the average trading terms which has been negotiated with customers. The debtors days is applied to the weekly turnover in order to calculate the appropriate trade receivables balance.
Example: If you enter a debtors days assumption of 30 days, the entire turnover value for four weeks will be included in the inventory balance. The week 5 turnover balance will therefore be used, divided by 7 days and multiplied by the 2 remaining days. Note: The above calculation principle is applied regardless of the number of days which are entered as the debtors days assumption on the Assumptions sheet even if the value of the debtors days assumption requires the inclusion of multiple weeks.
This method of calculation is the most accurate way of projecting trade receivable balances even for businesses where there is significant sales volatility. Where sales tax is applicable, the appropriate sales tax value relating to weekly turnover will be added to the trade receivables balance.
Sales tax codes are defined on the Assumptions sheet and the codes in column A next to the turnover amounts on the income statement are used to determine the appropriate rate of sales tax to be used. The trade receivables calculation will also only include lines that are coded with a sales tax rate code in the first two characters and a "C1" at the end of the code. The C1 part of the code refers to credit sales while the inclusion of a C0 code at the end refers to cash sales.
Cash sales do not need to be included in the trade receivables calculation and turnover lines with C0 or no code in column A are therefore ignored when calculating trade receivable balances. Example: If the standard rate sales tax code is V1 and the appropriate turnover line needs to be included in the calculation of trade receivables, the code V1C1 needs to be added in column A of the appropriate turnover line on the income statement. Example: If you do not want a particular turnover line to be included in the trade receivables calculation, you can include any sales tax rate followed by C0 in order to exclude the line in the trade receivables calculations.
For example, a turnover line with a code of V1C0 would not form part of the trade receivables calculations. Note: If your business has no trade receivables, you can simply enter a nil value in the debtors days assumption on the Assumptions sheet. The trade receivables line on the balance sheet will then also contain nil values. If you want to include variable weekly debtors days, you can do so by changing the debtors days assumption in the Workings section of the balance sheet which has been included below the section with the ratios.
Simply replace the formula which links the debtors days assumption to the value on the Assumptions sheet by overwriting it with the appropriate debtors days value. If you therefore want to increase or decrease these balances, you need to add the amount of the increase or decrease to the line with a matching description on the cash flow statement under the changes in operating assets section. If you therefore want to increase or decrease these balances, you need to add the amount of the increase or decrease to the line with a matching description on the cash flow statement.
Note: The shareholders contribution line on the cash flow statement can be found under the cash flow from financing activities and the reserves line on the cash flow statement under the non-cash adjustments. The retained earnings balances on the balance sheet are linked to the retained earnings for the year which is calculated on the income statement. Loans with the same repayment terms can be grouped together in the appropriate line item. There is no difference between the treatment of loans 1 to 3 and leases.
If you do not have finance leases and have loans with 4 different sets of repayment terms, you can use the Leases sheet and rename the appropriate line items accordingly. Note: The loan repayment period in years is limited to a maximum period of 30 years. If you want to include a loan repayment period which exceeds this period, you need to change the data validation settings in the appropriate input cell by selecting the data validation feature from the Data tab on the Excel ribbon and editing the maximum value of 30 which has been set in the loan repayment period cells.
Each of the loan repayment terms can be specified in the Loan Terms section on the Assumptions sheet. The loan terms include the annual interest rate, loan repayment period in years and a selection field which can be used to indicate interest-only loans. These loan repayment terms are then included at the top of the appropriate loan amortization sheet on the Loans1 to Loans3 and Leases sheets. Note: A set of loan terms can be specified as interest-only by selecting the "Yes" option from the interest-only drop-down list in the appropriate loan terms on the Assumptions sheet.
If this selection is made, the loan will be interest only and not include any loan repayments. All the calculations on the amortization sheets are fully automated. The loan terms are taken from the Assumptions sheet and the opening balances in the first row of the amortization table are based on the opening balances that are entered in the balance sheet opening balances section of the Assumptions sheet. The loan repayments, interest charged and capital repayments are calculated based on the outstanding balances at the beginning of each period.
Additional loans can be added to the appropriate amortization table by entering the appropriate values in the proceeds from loans section on the cash flow statement under the cash flow from financing activities section. The outstanding loan or lease balances at the end of each weekly period are then included in the appropriate lines on the balance sheet.
If the appropriate weekly closing balance is negative, the balance is included as a bank overdraft and if it is positive, it is included as cash under current assets on the balance sheet. The trade payables balances on the balance sheet are calculated based on the creditors days assumption which is specified on the Assumptions sheet. The number of days that are included here can be determined based on the average trading terms which has been negotiated with suppliers.
The weekly cost of sales, operating expenses and staff costs on the income statement are added together in order to determine a weekly value on which the trade payables calculations should be based. Expenses and costs which are paid on a cash basis can be excluded from the trade payables calculation by entering a code which ends in C0 in column A on the income statement.
The codes in column A start with the appropriate two character sales tax code and end with the two character payables code. Example: The expense codes in column A for all line items that need to be included in the trade payables calculation and which need to be subject to sales tax at a standard rate should be V1C1.
If the expense item is settled on a cash basis and also subject to the standard sales tax rate, the code in column A should be V1C0 which will then result in the item not being included in the trade payables calculation.
For standard sales tax, the code will therefore be V1C1. Like the calculation of inventory and trade receivables balances, the trade payables balances are based on a number of full weeks with total days of less than the creditors days and part of the previous week's days.
Note: The above calculation principle is applied regardless of the number of days which are entered as the creditors days assumption on the Assumptions sheet even if the value of the creditors days assumption requires the inclusion of multiple weeks. This method of calculation is the most accurate way of projecting trade payables balances even for businesses where there is significant sales or expense volatility.
The trade payables calculation will also only include lines that are coded with a sales tax rate code in the first two characters and a "C1" at the end of the code. The C1 part of the code refers to purchases on credit while the inclusion of a C0 code at the end refers to cash purchases. Example: If the standard rate sales tax code is V1 and the appropriate cost of sales or expense line needs to be included in the calculation of trade payables, the code V1C1 needs to be added in column A of the appropriate line on the income statement.
Example: If you do not want a particular cost of sales or expense line to be included in the trade payables calculation, you can include any sales tax rate followed by C0 in order to exclude the line in the trade payables calculations.
For example, an expense or cost of sales line item with a code of V1C0 in column A on the income statement would not form part of the trade payables calculations. Note: If your business has no trade payables, you can simply enter a nil value in the creditors days assumption on the Assumptions sheet. The trade payables line on the balance sheet will then also contain nil values.
If you want to include variable weekly creditors days, you can do so by changing the creditors days assumption in the Workings section of the balance sheet which has been included below the section with the ratios.
Simply replace the formula which links the creditors days assumption to the value on the Assumptions sheet by overwriting it with the appropriate creditors days value. The template accommodates the inclusion of sales tax in all relevant calculations based on four default sales tax calculation codes and any sales tax period. All income statement and cash flow statement items need to be entered exclusive of any sales tax that may be applicable and the trade receivables and trade payables balances on the balance sheet will be calculated inclusive of sales tax.
The net sales tax liability is included in the Sales Tax line on the balance sheet. Where there is no sales tax input which reduces the sales tax liability, the codes in column A on the income statement can simply be changed to contain a sales tax code in the first two characters of the code which has a zero percentage.
Only the sales tax codes that are included next to the turnover lines will then be included in sales tax calculations as required by some general sales tax calculations. The appropriate sales tax percentages can be entered in the Sales Tax section of the Assumptions sheet.
The template provides for 4 default sales tax codes, each with its own sales tax percentage. The sales tax codes are numbered from V1 to V4. The income statement contains codes in column A which affects the calculations of sales tax and trade receivables or trade payables. The first two characters of these codes determine which sales tax percentage is used in the sales tax calculations.
If an income statement item needs to be excluded from sales tax calculations, you should use a sales tax code with a zero percentage on the Assumptions sheet. Note: Each line on the income statement can therefore only be linked to one sales tax percentage. If more than one sales tax percentage needs to be applied to the same income statement item, you need to split the income statement amount into two lines and enter the appropriate sales tax codes in column A for each of the lines.
Note: If you are preparing cash flow projections for a business which is not subject to sales tax, simply enter zero percentages for all four sales tax codes. Through experience, management can better determine factors that contribute toward outperformance, performance in line with expectations, or underperformance. But in the case of negative variance, the actual performance was underwhelming and came in below management expectations, similar to a public company missing an earnings per share EPS target.
Once the monthly cash flow forecast and the variance analysis is complete, the recommended next step is to aggregate the monthly data into an annualized section. Companies can then assess the current year from a high level, as well as create multi-year projections with the compiled data sets — a long-term process that starts with monthly financial models.
Coming up with the operating assumptions, which is the most time-consuming portion of the analysis, will not be part of our exercise. The concept of revenues and cash receipts is similar, but revenues are recorded on the income statement under accrual accounting reporting standards while cash receipts are based on cash-based accounting. Note that even for small businesses, this sort of tax treatment is a simplification and is NOT meant to reflect reality by any means i.
Conversely, the cash disbursements were also understated — but in the case of expenses — higher values have a negative impact on cash flow and reduce profitability.
Regarding the cash outflows, the higher disbursements directly connected to higher revenue generation i. Fixed costs such as office rent and utility bills were held constant, as well as income taxes, since the applicable tax rate is known and can be estimated upfront as new sales figures come in. We're sending the requested files to your email now. If you don't receive the email, be sure to check your spam folder before requesting the files again.
The business name and the start date for the template reporting periods need to be entered at the top of the Assumptions sheet. The business name is included as a heading on all the sheets and the 36 month reporting period which is included in the template is determined based on the start date that is specified. This date is used as the first month and 35 subsequent months are added to form the 3 year projection period.
All rows without yellow highlighting are automatically calculated as detailed in these instructions. The exception is the balance sheet for actual results where all line items without light blue highlighting in column A are subject to user input in all columns aside from the first column which contains the balance sheet opening balances.
Also note that even the user input rows contain annual totals that contain formulas which should not be replaced with values. All monthly income statement projections need to be entered exclusive of any sales tax that may be applicable. Monthly turnover values need to be entered on the Forecast sheet for all 36 months excluding the annual totals in the columns with dark blue column headings. The projected monthly gross profit percentages also need to be entered on this sheet and are used in order to calculate the gross profit values.
The monthly cost of sales projections are calculated by simply deducting the gross profit values from the monthly turnover values.
The template includes two default lines in each of these sections - one for a typical product based item and one for a typical service based item. The template can therefore be used for both service and trade based businesses.
You can also hide the cost of sales and gross profit sections if you do not want to include them in your cash flow projections. Note: You can insert as many additional line items as required by inserting the required number of items in each section and then entering the appropriate values where user input is required or copying the formulas from one of the existing lines.
We recommend inserting additional line items between the two existing default line items. Note: If you insert additional line items on the Forecast sheet, you also need to insert these additional line items on the Actual sheet and on the Report sheet in exactly the same row positions.
Note: The codes in column A are used in the sales tax and trade receivables calculations. The first two characters represent the sales tax code and the last two characters represent the payment status.
Monthly projections of other income should be entered in this row. Note that other income may consist of items like interest or dividends received and this line item is therefore not included in trade receivables and sales tax calculations. If you want to include other income in the trade receivables or sales tax calculations, you need to add the income to the Turnover section as an additional line item.
All the monthly operating expense projections need to be entered in the operating expenses section of the income statement. The template contains 22 default operating expense line items but you can add as many additional items as required or delete the line items that you do not need.
When adding additional line items, remember to copy the formulas in the total columns from one of the existing line items. Note: If you insert additional expense items on the Forecast sheet, you also need to insert these additional expense items on the Actual sheet and on the Report sheet in exactly the same row positions.
Also, if you change the description of operating expenses, you also need to make the same changes on the other two sheet. We recommend copying the descriptions from the Forecast sheet and pasting the descriptions on the other two sheets once you have inserted new rows for new expense items. Note: The codes in column A are used in the sales tax and trade payables calculations. All the monthly staff cost projections need to be entered in the staff costs section of the income statement.
The template contains 2 default staff cost line items but you can add as many additional items as required or delete the line items that you do not need. Note: If you insert additional staff cost items on the Forecast sheet, you also need to insert these additional staff cost items on the Actual sheet and on the Report sheet in exactly the same row positions.
Also, if you change the description of staff cost line items, you also need to make the same changes on the other two sheet. We recommend copying the descriptions from the Forecast sheet and pasting the descriptions on the other two sheets once you have inserted new rows for new staff cost items. Note: Staff costs have been included in a separate section on the income statement in order to be able to calculate payroll accruals.
If you do not need to include payroll accruals in your cash flow projections, we recommend entering nil values and hiding these rows. If you delete the section, some of the payroll accrual formulas may result in errors and you therefore may need to delete them as well. Monthly projections for depreciation and amortization need to be calculated independently of the template and included in this section. We unfortunately cannot include default depreciation or amortization calculations because some businesses may have very different asset bases than others with existing assets which may already have been depreciated over a number of years.
Any calculation which is based on a percentage of the balance sheet asset value may therefore not be accurate. We also realize that some users may want to include depreciation and amortization as part of their operating expenses.
We have therefore provided for this in that the depreciation and amortization calculations on the cash flow statement are based on the default code which is included in column A.
All interest paid calculations are automated and based on the amortization tables on the Loans1 to Loans3 and Leases sheets.
Opening loan balances are based on the balance sheet opening balances section on the Assumptions sheet and additional loan amounts can be entered in the proceeds from loans section of the cash flow statement and will then automatically be included in the appropriate amortization table.
You do not need to use all four loan amortization sheets - if you only need to include loans based on one set of repayment terms, you can delete the other loan amortization sheets, delete the other interest paid rows on the income statement, delete the other proceeds from loans rows on the cash flow statement, delete the other repayment of loans rows on the cash flow statement and delete the other loan balances from the balance sheet.
The template provides for four sets of loan repayment terms - the same amortization table can basically be used for all loans with the same repayment terms by adding additional loan amounts as proceeds to the cash flow statement in order to add new loans to the appropriate amortization table.
If you need to add more than four sets of loan repayment terms, you will need to copy one of the amortization sheets, change it to reflect the appropriate loan terms and then change the formulas in the amortization table to be based on the correct loan repayment terms at the top of the sheet. This means that you need to add another set of repayment terms to the Assumptions sheet and link the fields at the top of the new amortization table to the appropriate cells on the Assumptions sheet.
If there is an opening balance for the required additional loan terms, you need to include a new code in the balance sheet opening balances section on the Assumptions sheet and base the opening balance calculation in the first period of the amortization schedule on this code.
You also need to add new rows to the interest paid section on the income statement, the loan proceeds section on the cash flow statement, the loan repayment section on the cash flow statement and the loan balances section on the balance sheet. The appropriate formulas can be copied from one of the existing items and the sheet reference in the copied formula can then just be replaced by the sheet name of the new amortization table that you've added.
The taxation line item on the income statement is automatically calculated based on the profit before tax and the income tax assumptions which are specified on the Assumptions sheet. This will result in no income tax being calculated. If you do want to include income tax calculations, the appropriate income tax percentage needs to be entered in the Income Tax section on the Assumptions sheet.
You can also enter a value for an assessed loss as a positive value which may have been carried over from a previous tax year which would result in income tax only being calculated after profits exceed the value of the assessed loss.
You also need to specify the payment frequency in months and the first calendar month in which a payment needs to be included. The template automatically provides for income tax based on what is due and includes the income statement amount and a provision for taxation on the balance sheet.
The payment frequency and month of payment assumptions are then used to determine when the income tax liability will be settled which will result in the appropriate cash outflow being recorded on the cash flow statement and the provision for taxation being reduced.
The template can accommodate income tax calculations based on current and subsequent month payments. If you select the Current option, the income tax payment amount will be calculated based on all amounts that have accrued up to and including the month of payment.
If you select the Subsequent option, the income tax payment amount will only be calculated based on all amounts which have accrued up to the previous month end. Example: If you select the Current option in the Income Tax section of the Assumptions sheet, all income tax amounts up to and including the current month will be included in the income tax payment amount. This means that the provision for taxation at the end of the particular month will be nil. The Current setting is therefore usually appropriate for provisional taxpayers.
Example: If you select the Subsequent option, all amounts up to and including the previous month end will be included in the income tax payment amount. The provision for taxation balance on the balance sheet will therefore not be nil at the end of the month of payment and include the current month's income tax charge. The template also includes automated dividends calculations.
If you do not want to include any dividends in your cash flow projections, you can simply specify a dividend percentage of zero percent. If you want to include dividend calculations, you need to specify a dividend percentage which will be applied to the profit for the period in order to calculate the dividend value. You also need to specify the frequency in months of dividend payments and the first payment month. The frequency of dividends determines when the dividends are included on the income statement and the first month of payment determines when the dividend payment is included on the cash flow statement only has an effect if the dividend payment option is Subsequent.
You can also specify whether the dividend is paid in the month of calculation Cash option , the month after calculation Next option or in a subsequent month. When you elect the subsequent month option, the payment of the dividend will be included based on the relative position of the first month of payment in relation to the year-end period which is determined based on the template start date at the top of the Assumptions sheet.
Example: If you want to include a dividend in the last month of each financial year, select a payment frequency of 12 months and month 12 as the first payment month.
Then select the Cash option in order to include both the dividend on the income statement and the payment in the last month of the year. Example: If you want to include a dividend in the last month of each financial year but delay payment to the first month of the next financial year, select a payment frequency of 12 months and month 12 as the first payment month. Then select the Next option in order to include the dividend on the income statement in the last month of the financial year and the payment in the first month of the next financial year.
A dividend payable amount will then automatically be included on the balance sheet at year-end. All the calculations on the forecast balance sheet are automated and no user input is therefore required. If you need to compile cash flow projections for an existing business, you will need to include the opening balance sheet balances at the start of the cash flow projection period. The opening balances that are entered here are included in the first column on the balance sheet.
You can use the trial balance as at the end of the period immediately before the start of the cash flow projection period for this purpose. The opening balances should also balance to a total of nil as with any accounting system trial balance. If you enter balances and the total of all balances is not nil, the entire opening balances section on the Assumptions sheet will be highlighted in orange.
You then need to fix the imbalance by adjusting the opening balances so that the total comes to a total of nil. The orange highlighting will then be removed automatically. Also note that the cash flow projection balance sheet cannot balance if the opening balances do not balance.
Note: If you are preparing a cash flow projection for a new business, you can include zero balances for all the balance sheet items in the opening balances section.
The daily cash report template is a useful tool that gives a professional look to the report. Those cashiers who want to save their time can use the template before preparing a cash report.
This template for MS Excel is readymade and easy to use. File: Excel. Cash report worksheet The cash reports are one of the most important documents that are used by the cashier in any business.
0コメント