ProjectionHub now has Excel templates!
After years of providing custom Excel models for our consulting clients, we’ve learned a thing or two about creating projections in Excel. Many potential business owners need a reliable solution for predicting their bottom line – whether they need projections for lenders, investors, or simply for internal planning.
Check out our available Excel templates here!
Why use an Excel template?
Our Excel templates are easy to use and flexible. They are designed to save you time – no more struggling with a balance sheet that refuses to balance! They output five years of pre-formatted and pre-filled Balance Sheets, Income Statements, and Cash Flows Statements (in both monthly and annual summary formats) based on information you provide on a series of input tabs. Our templates all have a similar, easy to follow structure. Each revenue model has been built using our financial and accounting expertise from years of experience creating financial projections from businesses like yours.
In this blog post, we want to walk you through the process of creating financial projections with our Excel templates.
General Template Structure
When you download your template, you will notice it has an instructions / welcome page, and several blue and green tabs. The blue tabs are used for information input, and the green tabs are the financial statements and other reports the model generates from your inputted data. On each blue tab, there are corresponding light blue cells that direct you to areas where you can change inputs. Do not edit non-blue cells, as this might affect the output.
Assumptions: Investment, Loans, Accounts Receivable, Inventory, and Fixed Assets
The first input tab covers most of the balance sheet items. On this tab, you can define the name of your business, and select a start date. You can also set inventory values, AR terms, loan terms, and investment funds.
Follow the patterns of the example data on the template, ensuring you fill out all the columns in each box. You can overwrite any data in the blue cells as it is there to guide you as you fill out the template.
Revenue
The revenue model will vary for every business. Generally, the models begin by determining the number of customers or clients generated by advertising, and then determining their purchases. Some templates include multiple revenue streams, i.e. restaurant sales and catering. Follow the model from top to bottom, updating the blue cells as you see fit. For many models, the advertising expense at the top can be changed in the first row of the “Input – Other Expenses” tab.
Cost of Goods Sold / Cost of Sales
Some models have cost of goods sold (COGS) calculations built directly into the revenue model on the “Input – Revenue” tab. Others have a separate tab for COGS. The COGS / Cost of Sales tab asks for percentages of revenue to calculate direct materials and direct labor costs. The markup percentage is included for reference. This table is helpful for industries where labor and materials are usually measured as a percent of revenue.
Other Expenses
Our other expenses tab allows you to build start-up expenses into the model by individually breaking out the first 3 months of operation. Expenses after month 3 are determined based on year of operation to save you time inputting data. The expenses automatically increase over time based on your inputs in the first month, using the inflation rate determined on the assumptions tab. You can override the increase by typing over the formulas in the blue cells.
Be sure to enter monthly, not annual expense amounts. If you have estimated your expenses annually, you can have Excel determine the monthly amount for your by using a formula. For example, if your annual insurance quote is $1,000, type “=1000/12” into the cell.
Note income tax percentages are included at the bottom of the other expenses tab. We prefill the income tax percentages with example amounts, but we recommend talking to a tax professional to estimate your effective tax rate.
Salaries and Dividends
The last input tab is salaries and dividends. This tab is used to determine any indirect labor and dividends paid to shareholders. Note that direct labor is a cost of sales and is determined on the COGS / Cost of Sales or Revenue tab. Indirect labor can be administrative assistants, CEOs, sales managers, etc.
Note that there are 19 lines for employees on the salaries table, but many more employees can be entered using the far right column (“# of this particular employee”) and creating categories of employees. For example, if you will hire 2 engineers in month 13 at the same salary, you can enter just one line for engineer and change the far right column to 2, and the model will automatically double the employee expenses for that type of employee.
Dividends are also included on this page. Dividends are draws on the retained earnings of a company and are not technically an expense, although they reduce cash in a period. A company owner or shareholders may choose to compensate themselves with dividends rather than salaries.
Financial Reports and Other Output
When you’ve finished filling out the blue input tabs on your standard template, you can view the green output tabs. The “At a Glance” tab will show you some charts and summary data for your company, including a calculation of the breakeven year:
The remaining green tabs are the financial statements in both monthly and annual formats:
Final Notes
Our templates are deigned to save you time in creating your projected financial statements. We’re available for support at support@projectionhub.com if you have any questions.
Don’t see a template that fits your business, or would rather us do the work for you? Check out our consulting services here! We can customize a model specifically for your business plan.
Leave a Reply