Developing The Forecast
WITHOUT GETTING INTO THE TOOLS that you might use to prepare the forecast, let's spend some time on the development of the forecast and the assumptions to be used.
**WARNING**
I am going to present here a very detailed description on how to develop a financial forecast. The most important thing to remember in your financial forecasting is to keep it simple! After you begin utilizing these techniques, if you want to become more sophisticated in the process you can review this section and adapt more comprehensive methods.
Why the warning? I risk losing you here with the detailed descriptions that follow. It's a calculated risk I'm willing to take. You must understand the concept's
You can certainly perform all of the following calculations yourself using a spreadsheet program, or you can contact our office for further information on Up Your Cash Flow XT2, our patented software package that will complete all of this work for you it's easy to use.
LET'S BEGIN
You should start with the development of the P&L. As you know, there is a logical flow to the P&L. The top is the sales, below that is the cost of goods sold. Next would be a series of expense departments with the final department being the general and administrative expenses. In future sections of this book I will be discussing some methods for determining the assumptions for sales and expenses.
In the mean time the second step in the process should be the development of the cash flow forecast, along with the cash flow assumptions. Basically, there will be two primary assumptions to be used regarding cash flow.
The first step in developing cash flow is the manner in which sales will be collected. My preference is to forecast a slow collection process.
My preference regarding expenses is to assume all expenses will be paid immediately. This may not be very likely, but this is the most conservative method. The forecast produced will be almost a worst-case scenario for cash payout assumptions. Remember, we can always adjust assumptions to be more realistic at a later date.
SALES COLLECTIONS ASSUMPTION
There are four basic sales collection methods that can be used in fore-casting sales collections.
Method 1: Collections as a percent of sales.
This method is simple.
Let us assume, for example, that your total sales are going to be $1,870,000 for the year, and then further assume that said total will be 80 percent collected during the year. This gives you total cash collections of $1,496,000 ($1,870,000 x .80). Go back to prior years to see what each month's relationship of collections is to the year's total. Then, apply that percentage to the total annual collections you expect to receive.
Method 2: Collections in a consistent pattern over several months.After several years in business, a relationship is determined between the sending of accounts receivable invoices vs. the terms of the invoices. (For example, an invoice with a thirty day payment requirement will generally be paid in the month following the sale; if there is a ten day discount offered, many of the invoices will get paid within the month of the sale.) In either case, management must assume responsibility for the pattern that is used.
Let us assume that $129,000 of your sales will be made in the month of January. History indicates (or we can just make an arbitrary assumption) that those sales will be collected, let's say, 45 percent in the month of the sale, 40 percent in the first month following the sale, and 15 percent in the second month following the sale. We can then forecast our cash collections using this method for each month throughout the year.
Method 3: Collections determined by forecasting accounts receivable using daily sales.
This is the most accurate method for determining sales collections. The collections are determined not by making a collection assumption but developing the accounts receivable balance at the end of each month using a day's sales in accounts receivable calculation.
Using the accounts receivable balance to calculate the collections:
Sales / 360 = average daily sales.
Accounts receivable / daily sales (the result of sales / 360) = number of days sales in accounts receivable.
To calculate the accounts receivable at the end of each month, do the following: Assume thirty days in each of the months preceding the A/R balance date. For example, using the month of January with forty-five days worth of sales in accounts receivable, the calculation is all of January's sales (thirty days) plus the prior December sales divided by 30. This represents one day's sales for each. Then, multiply each day's sales by 15 (45 days less 30 days = 15 days needed). The total of the 30 + 15 = 45 day's worth of sales. This number is the accounts receivable at the end of each January.
Now we need to take the accounts receivable as calculated to develop the collections for the month. The formula is: take the beginning accounts receivable for the forecast (this should be the accounts receivable in the opening balance sheet), add forecasted sales less the accounts receivable (as calculated), and your end-of-the-month result is the month's collections.
Beginning accounts receivable = $250,000, plus forecasted sales for the month = $750,000, less calculated ending accounts receivable = $375,000. Collections = $625,000 ($250,000 + $750,000 - $375,000).
Method 4: Determine collections using an arbitrary collection assumption.
Want to make life less complicated? Use this method. Just reach into your entrepreneurial instincts! You have the answer as to what you may want to use for each month's collections. Easy? Yes. Accurate? No. Not at all. Pass on it.
EXPENSE PAYMENT ASSUMPTIONS
There are generally two basic methods for forecasting the payment of expenses:
Method 1. Payment in a consistent pattern over several months. Each month is a percentage of the expense to be paid. Never exceed 100 percent.
After several years in business, I discovered there is a pattern between the time when invoices are received vs. when they are paid. For example, an invoice with a thirty day payment requirement will generally be paid in the month following the month the expense is incurred. In this scenario, an expense is incurred in, let's say, January, and will not be paid until March. This means the expense will have a payment term of 0 percent in the month incurred (January), 0 percent in the first month following the month incurred (February), and 100 percent in the second month following the month incurred (March). Management will have to determine what the pattern is that should be used.
Method 2. Determine payments using an arbitrary payment assumption.
This is the less complicated method. Just reach into your entrepreneurial instincts to determine what month you anticipate the expense to be paid. Easy? Yes. Accurate? Could be.
There are a number of ways to forecast the payment assumption for an expense, but the most widely used is Method 1: The consistent pattern.
THE RIG-A-MAROLE
Now that I have re-read what I've written in the collection and payment methods used in cash flow forecasting, it seems so complex that most readers will conclude they will not bother with the rigamarole.
Clearly, the development of a cash flow forecast can be very time-consuming and complicated. This is the reason why the CFO I previously discussed didn't develop the cash flow forecast, just the P&L. Even though it was necessary to determine whether to proceed with the merger, he opted not to do it. It's difficult and time consuming. Discouraged? Don't be.
Make life easy; you can do it yourself by developing the formulas and model building using Excel, which is an excellent but complex spread-sheet program, or you can hire your outside accounting firm to do it, and they will tackle the formulas and model building for a nice fee.
OR, BEST OF ALL
take a look at the software I developed. Its only function is to prepare a financial forecast. All the methods mentioned in this section are menu-driven in my software program. There is no need to create formulas or build models. It is all done for you. It even integrates the P&L, cash flow, and balance sheets.
I developed the software in 1989, because I was preaching to the business community the need for and importance of forecasting, or predicting, the financial roadblocks that lie ahead and the importance of having a handle on how much cash you will need and when you will need it.
TO THIS DAY I DON'T KNOW HOW TO DO IT
My primary motivation for developing my forecasting tool was that I did not know how to use a spread sheet program. I took one look at the size of the manuals that came with spreadsheet software, and I said to myself, I will never have the time or patience to learn how to formulate and build an appropriate model. Neither will my clients. It would a painful experience. So I hired a programmer, and we started our journey to develop a software tool that was easy to use and cost-effective. After about a year, I became the programmer. It turned out to be great therapy for me, and after all these years, it has become the most comprehensive forecasting software in the marketplace today.
ENOUGH SAID; LET'S MOVE ON
Now that we have spent some time on the appropriate assumption methods needed for cash collections and cash-out flow (paying bills), let's move on to developing the profit and loss. The P&L forecast is the main forecast, with the cash flow and balance sheets being developed from the information on the P&L.
WITHOUT GETTING INTO THE TOOLS that you might use to prepare the forecast, let's spend some time on the development of the forecast and the assumptions to be used.
**WARNING**
I am going to present here a very detailed description on how to develop a financial forecast. The most important thing to remember in your financial forecasting is to keep it simple! After you begin utilizing these techniques, if you want to become more sophisticated in the process you can review this section and adapt more comprehensive methods.
Why the warning? I risk losing you here with the detailed descriptions that follow. It's a calculated risk I'm willing to take. You must understand the concept's
You can certainly perform all of the following calculations yourself using a spreadsheet program, or you can contact our office for further information on Up Your Cash Flow XT2, our patented software package that will complete all of this work for you it's easy to use.
LET'S BEGIN
You should start with the development of the P&L. As you know, there is a logical flow to the P&L. The top is the sales, below that is the cost of goods sold. Next would be a series of expense departments with the final department being the general and administrative expenses. In future sections of this book I will be discussing some methods for determining the assumptions for sales and expenses.
In the mean time the second step in the process should be the development of the cash flow forecast, along with the cash flow assumptions. Basically, there will be two primary assumptions to be used regarding cash flow.
The first step in developing cash flow is the manner in which sales will be collected. My preference is to forecast a slow collection process.
My preference regarding expenses is to assume all expenses will be paid immediately. This may not be very likely, but this is the most conservative method. The forecast produced will be almost a worst-case scenario for cash payout assumptions. Remember, we can always adjust assumptions to be more realistic at a later date.
SALES COLLECTIONS ASSUMPTION
There are four basic sales collection methods that can be used in fore-casting sales collections.
Method 1: Collections as a percent of sales.
This method is simple.
Let us assume, for example, that your total sales are going to be $1,870,000 for the year, and then further assume that said total will be 80 percent collected during the year. This gives you total cash collections of $1,496,000 ($1,870,000 x .80). Go back to prior years to see what each month's relationship of collections is to the year's total. Then, apply that percentage to the total annual collections you expect to receive.
Method 2: Collections in a consistent pattern over several months.After several years in business, a relationship is determined between the sending of accounts receivable invoices vs. the terms of the invoices. (For example, an invoice with a thirty day payment requirement will generally be paid in the month following the sale; if there is a ten day discount offered, many of the invoices will get paid within the month of the sale.) In either case, management must assume responsibility for the pattern that is used.
Let us assume that $129,000 of your sales will be made in the month of January. History indicates (or we can just make an arbitrary assumption) that those sales will be collected, let's say, 45 percent in the month of the sale, 40 percent in the first month following the sale, and 15 percent in the second month following the sale. We can then forecast our cash collections using this method for each month throughout the year.
Method 3: Collections determined by forecasting accounts receivable using daily sales.
This is the most accurate method for determining sales collections. The collections are determined not by making a collection assumption but developing the accounts receivable balance at the end of each month using a day's sales in accounts receivable calculation.
Using the accounts receivable balance to calculate the collections:
Sales / 360 = average daily sales.
Accounts receivable / daily sales (the result of sales / 360) = number of days sales in accounts receivable.
To calculate the accounts receivable at the end of each month, do the following: Assume thirty days in each of the months preceding the A/R balance date. For example, using the month of January with forty-five days worth of sales in accounts receivable, the calculation is all of January's sales (thirty days) plus the prior December sales divided by 30. This represents one day's sales for each. Then, multiply each day's sales by 15 (45 days less 30 days = 15 days needed). The total of the 30 + 15 = 45 day's worth of sales. This number is the accounts receivable at the end of each January.
Now we need to take the accounts receivable as calculated to develop the collections for the month. The formula is: take the beginning accounts receivable for the forecast (this should be the accounts receivable in the opening balance sheet), add forecasted sales less the accounts receivable (as calculated), and your end-of-the-month result is the month's collections.
Beginning accounts receivable = $250,000, plus forecasted sales for the month = $750,000, less calculated ending accounts receivable = $375,000. Collections = $625,000 ($250,000 + $750,000 - $375,000).
Method 4: Determine collections using an arbitrary collection assumption.
Want to make life less complicated? Use this method. Just reach into your entrepreneurial instincts! You have the answer as to what you may want to use for each month's collections. Easy? Yes. Accurate? No. Not at all. Pass on it.
EXPENSE PAYMENT ASSUMPTIONS
There are generally two basic methods for forecasting the payment of expenses:
Method 1. Payment in a consistent pattern over several months. Each month is a percentage of the expense to be paid. Never exceed 100 percent.
After several years in business, I discovered there is a pattern between the time when invoices are received vs. when they are paid. For example, an invoice with a thirty day payment requirement will generally be paid in the month following the month the expense is incurred. In this scenario, an expense is incurred in, let's say, January, and will not be paid until March. This means the expense will have a payment term of 0 percent in the month incurred (January), 0 percent in the first month following the month incurred (February), and 100 percent in the second month following the month incurred (March). Management will have to determine what the pattern is that should be used.
Method 2. Determine payments using an arbitrary payment assumption.
This is the less complicated method. Just reach into your entrepreneurial instincts to determine what month you anticipate the expense to be paid. Easy? Yes. Accurate? Could be.
There are a number of ways to forecast the payment assumption for an expense, but the most widely used is Method 1: The consistent pattern.
THE RIG-A-MAROLE
Now that I have re-read what I've written in the collection and payment methods used in cash flow forecasting, it seems so complex that most readers will conclude they will not bother with the rigamarole.
Clearly, the development of a cash flow forecast can be very time-consuming and complicated. This is the reason why the CFO I previously discussed didn't develop the cash flow forecast, just the P&L. Even though it was necessary to determine whether to proceed with the merger, he opted not to do it. It's difficult and time consuming. Discouraged? Don't be.
Make life easy; you can do it yourself by developing the formulas and model building using Excel, which is an excellent but complex spread-sheet program, or you can hire your outside accounting firm to do it, and they will tackle the formulas and model building for a nice fee.
OR, BEST OF ALL
take a look at the software I developed. Its only function is to prepare a financial forecast. All the methods mentioned in this section are menu-driven in my software program. There is no need to create formulas or build models. It is all done for you. It even integrates the P&L, cash flow, and balance sheets.
I developed the software in 1989, because I was preaching to the business community the need for and importance of forecasting, or predicting, the financial roadblocks that lie ahead and the importance of having a handle on how much cash you will need and when you will need it.
TO THIS DAY I DON'T KNOW HOW TO DO IT
My primary motivation for developing my forecasting tool was that I did not know how to use a spread sheet program. I took one look at the size of the manuals that came with spreadsheet software, and I said to myself, I will never have the time or patience to learn how to formulate and build an appropriate model. Neither will my clients. It would a painful experience. So I hired a programmer, and we started our journey to develop a software tool that was easy to use and cost-effective. After about a year, I became the programmer. It turned out to be great therapy for me, and after all these years, it has become the most comprehensive forecasting software in the marketplace today.
ENOUGH SAID; LET'S MOVE ON
Now that we have spent some time on the appropriate assumption methods needed for cash collections and cash-out flow (paying bills), let's move on to developing the profit and loss. The P&L forecast is the main forecast, with the cash flow and balance sheets being developed from the information on the P&L.