A detailed profit and loss forecast can help you make sound decisions about operations and investments to make your business grow. Using this tool can help you understand and use the relationships that drive revenue and expenses in your business.
A well-documented forecast will support your gut instincts with the data needed to get a loan or raise capital. You can develop powerful forecasts for your business strategies, using the multiple-layer capabilities of spreadsheet software programs such as Microsoft Excel and the data in your accounting system.
Data Collection
Your forecast is only as good as the information you use to formulate it. Gathering and documenting all sources of data and estimates are the first steps in building an effective, reliable forecast. Keep all of this information in one spreadsheet in your forecast notebook labeled “Assumptions.” This sample summary sheet shows formula examples.
Forecast Summary
Create your summary page by copying your income statement onto your spreadsheet or downloading it from your accounting software. Each column represents a period of time; forecasting is typically broken down into months, but some forecasts are measured in quarters or years.
Rearrange the expense rows to separate your fixed costs, such as rent, loan payments, and insurance, from the variable costs, which change based on various factors. You can add more rows if you want greater detail.
Build formulas for your variable costs that tie revenue to measurable activities, such as sales per mile. If your company is new, use each expense as a percentage of revenue based on the income statement of a public company that is an industry leader. The sample profit and loss forecast summary sheet shows examples.
Feedback
Update the forecast with actual results at the end of each month. Determine why results differ from the forecast, and update formulas and assumptions based on changing business conditions.
Testing
Testing the forecast with different scenarios is critical to learning how well it will hold up over time. This technique is called a sensitivity analysis. Change only one assumption at a time. Changing multiple elements simultaneously obscures which one is having the greatest impact. Raising the vehicle expense rate and reducing the number of sales miles at the same time, for example, will not help you decide whether paying more for gas is a better option than reducing the number of customer contacts.
