Forecast template spreadsheet

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. 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 monthly cost of sales in order to calculate the appropriate inventory balance. This calculation is based on the actual number of days in each month if the inventory days assumption is greater than the number of days in the appropriate month.

Example: If you enter an inventory days assumption of 60 days and the month is April, the entire cost of sales value for April will be included in the inventory balance because April only has 30 days. After including the 30 days in April, there is a difference of 30 days between the 60 days assumption and the 30 days in April.

The March cost of sales balance will therefore be used, divided by the 31 days in March and multiplied by the 30 remaining days. The inventory balance at the end of April will therefore consist of the cost of sales total for April and an equivalent of 30 days of the 31 day cost of sales of March.

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 more than 2 months. 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 monthly 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 monthly turnover in order to calculate the appropriate trade receivables balance.

This calculation is based on the actual number of days in each month if the debtors days assumption is greater than the number of days in the appropriate month.

Example: If you enter a debtors days assumption of 60 days and the month is April, the entire turnover value for April will be included in the trade receivables balance because April only has 30 days. The March turnover balance will therefore be used, divided by the 31 days in March and multiplied by the 30 remaining days. The trade receivables balance at the end of April will therefore consist of the turnover total for April and an equivalent of 30 days of the 31 day turnover of March. Note: The above calculation principle is applied regardless of the number of days which are entered in the debtors days assumption on the Assumptions sheet even if the value of the debtors days assumption requires the inclusion of more than 2 months.

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 monthly 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 monthly 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 monthly period are then included in the appropriate lines on the balance sheet. If the appropriate monthly 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 monthly cost of sales, operating expenses and staff costs on the income statement are added together in order to determine a monthly 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 on the balance sheet are based on the actual number of days in each month if the creditors days assumption is greater than the days in the appropriate month.

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 more than 2 months. 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 monthly 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.

The sales tax assumptions that need to be specified on the Assumptions sheet also include the frequency of sales tax payments in months and the calendar month of the first payment period. You can therefore calculate sales tax based on any period frequency from one to twelve months. Example: If your business is subject to sales tax payments of every two months and the first payment is due in February, a frequency of 2 needs to be specified and the first payment month should be set to 2 for February.

Similarly, if your business is subject to sales tax payments of every 6 months with payments due in March and August, the frequency should be set to 6 and the first payment month should be set to 3. If your business is subject to monthly sales tax payment periods, the frequency should be 1 and the first payment month should also be 1. The Current or Subsequent setting in the Sales Tax section on the Assumptions sheet determines how the calculated sales tax amounts of the current period are handled.

If you select the Current option, the sales tax amounts of the current period will be included in the calculation of the payment amount which is due in the particular month and the sales tax liability at the end of the payment month will be nil.

If you select the Subsequent setting, the sales tax amount of the current period is not included in the calculation of the payment amount and the sales tax liability at the end of the appropriate payment month will always include at least one month. Note: The Subsequent setting is usually the appropriate setting to use for sales tax purposes. The Current settings is more applicable to tax types which are subject to provisional tax. Example: If you set a payment frequency of 1 month, first payment month of 1 and select the Current option, the sales tax liability on the balance sheet will always be nil because the current month's sales tax will be included in the sales tax payment.

If you have the same period settings and select the Subsequent option, the sales tax liability on the balance sheet will always include the current month's sales tax because the payment amount will be based on the previous month's sales tax.

Add the opportunity name, sales phase, sales agent, region, and sales category. Then, add the forecasted amount and probability for each opportunity. Based on the values you enter, the weighted forecast will auto-calculate with pre-built formulas and display a visual of sales projections on the Forecast Totals and Forecast Graph tabs. This lead-driven forecasting template enables you to project the value of each lead on a monthly basis, based on historical data e.

When you customize the Deal Stage key, the deal stages use formulas to automatically update accordingly. Add contact information, key dates, and the deal value for each lead. Then, the weighted forecast value will auto-calculate according to the closure probability you assign to each stage in the key. This sales forecast template is designed to project future revenue for an e-commerce business over a five-year time period.

Enter the marketing budget at the top of the template. Then, enter the number of organic visits, conversion rate, average order value, and other revenue. Once you enter those values, the paid and organic visits, sales, and total revenue will auto-calculate with built-in formulas. This customizable retail sales forecasting template projects the total annual revenue for a five-year time span.

Enter the estimated daily footfall, percentage of customers who enter the store and make a purchase, average sale value, and other sources of revenue. Once you enter those values, the total number of customers, sales, and revenue will calculate with pre-built formulas.

This sales forecasting template projects the annual revenue of a hotel over a five-year time span. Enter the total number of rooms and the number of operating days in a given year, the occupancy rate and average daily room rate, and the food and beverage percentage, if applicable.

The projected room occupancy and total revenue will calculate automatically with built-in formulas. At the top, enter the number of rooms available, the number of days open by season, average room rates, and other revenue. Occupancy rates, available nights, and total projected revenue will calculate with pre-built formulas.

Performing a sales forecast , or estimating future sales, is a valuable tool you can use to predict the short and long-term performance of your company. Empower your people to go above and beyond with a flexible platform designed to match the needs of your team — and adapt as those needs change. The Smartsheet platform makes it easy to plan, capture, manage, and report on work from anywhere, helping your team be more effective and get more done.

Report on key metrics and get real-time visibility into work as it happens with roll-up reports, dashboards, and automated workflows built to keep your team connected and informed. Try Smartsheet for free, today. Get a Free Smartsheet Demo. In This Article. Basic Sales Forecast Sample Template.

If so, how would the spreadsheet be edited? Try re-downloading the spreadsheet. If you still have problems send us in the spreadsheet and we will see if we can fix it. Maybe the download file needs to be replaced. It appears that this version of the template requires excel as in the version evolutionary options are not available. When I click the calculate button, I get an pop up window that states Show Trial Solution — The maximum number of subproblems was reached; Continue anyway?

Hi Angela. This tool uses a feature in Excel called Solver, which will run through many options for the future values until an optimal solution is found that fits well for a number of the original values.

Hello there, I was VERY interested in using your forecasting template … alas, we have Excel installed, and the Solver seems to be incompatible with your macro. It should work with the latest version of Excel. You need to have the add-in packs in Excel enabled. How come it outputs a different forecast every time i run it with the same historical data?

I ran it 5 times and I got although very close different data. This is because it is a simulator. It generates a random arrival of calls, emails and web chats — just as happens in real life. Sometimes the calls will be bunched together and sometimes they will be spaced out. If you run it a few times you will get a better idea of the average and peak number of staff needed.

I am interested in using this, but want to make it a weekly version. Is there a weekly version available? If, not what needs to be changed to make it so?

Also, In the Calcs tab I did not see a note in the video if any of this data needs to change? Does it? If so what needs to be changed? Where does the Factors data come from? We are working on an online version that should be able to take weekly inputs. I am getting the same compile error. What is the work around for it or is there any other similar excel sheets available. Hi I am receiving the below error when trying to run the forcaster. Complie error cant find project or libary.

Is it necessary to change the data on the Calcs? Hi just had the compile error that people have been mentioning. Monthly Forecasting Excel Spreadsheet Template. Related Articles. Installing Excel Add-ins for the Forecasting Template. A Guide to Call Centre Forecasting. Click here to download the Forecasting template To get the calculator to work you must enable macros in Excel and have both Analysis Toolpak and Solver installed in Excel as Add-ins.

Using the Monthly Forecasting Template Open the spreadsheet and enable macros or Enable Content if you are prompted to do so.

If you select the current data and press the delete key, you may get the following message This is fine, click OK and the chart will refresh and show just one point in the centre. Using this forecast for staffing forecasts The forecasting spreadsheet just works out the monthly predicted call volumes. Terms and Conditions Use of the Forecasting Spreadsheet is subject to our standard terms and conditions.