Using the SBDC Spreadsheet Workbooks

The NPC SBDC Financial Statement Spreadsheet Program is a tool for a financial analyst or client to prepare financial projections for use in business plans and financial planning.

This program is designed to provide accurate and authoritative information in regard to the subject matter covered.  It is provided with the understanding that the publisher and author is not engaged in rendering legal, accounting or other professional service.  If legal advise or other expert assistance is required, the services of a competent professional person should be sought.  There is no guaranty or warrantee, implied or otherwise, regarding the accuracy and final product of the spreads produced using the program.

Download the program by going to http://www.npcsbdc.com, selecting file downloads, and following directions from there.

Please direct any comments, questions, or suggestions regarding this book to:  Northland Pioneer College, Small Business Development Center, P.O. Box 610, Holbrook, AZ  86025  (520) 532-6170.

Basic Structure of the Program

The program is an Excel spreadsheet, designed as a workbook, to enter key financial variables and edit projections “on the go”.  You see the product of your efforts as you enter data, and can make changes without printing the whole spreadsheet.  Although there are nineteen basic worksheets as part of the program, the ultimate number of pages to be printed out would vary from three to six.  A copy of the “Sample Company” spreads are shown later in this Module.

Why do projections?

The financial portion of a business plan contains any historical financial statements for a business, along with your estimates of what the company will do for the future.  These estimates are referred to as projections.  For most business plans it is advised that you include five years in your projections.  While this is a long time to project, especially if you are just getting started and forecasting one year seems difficult, it is really important that you have some idea of what your business will look like in five years.  If the outlook is not that good it allows you to make changes now which will solve the problem.  Or perhaps you need to look at a larger operation before you can start drawing a salary.  Whatever the scenario, it’s good to know where you will be in five years.

How to get started

The process we employ here is by no means the only method to work on projections, just one that is logical and builds upon the work you’ve done as you go.  This is one task that a computer can really help with.  You can utilize a spreadsheet to put down your numbers, and then when you make a change the computer can automatically update your full report.   Any future changes are made easily, and require just a few keystrokes and a reprinting to accomplish.

The key sections

The startup balance sheet

Starting with your startup balance sheet, you should put together your required assets, liabilities, and net worth for your new company.  The net worth is the difference between the assets and liabilities.  Starting with the assets, consider what level of cash you should have in the business.  You want to be able to handle any normal operating costs, at least for a month or two, from cash.  To run out of cash can cause you problems before you have really gotten started in business.  Inventory, especially for retailers and manufacturers, is an important consideration.   You can often determine your required (and desired) amount of inventory by doing some basic calculations, described on the next page.

For a retailer, inventory is your lifeblood.  If you don’t have inventory you don’t have the ability to generate sales or income.  A basic calculation I use with this situation is shown below which estimates your required starting inventory.

1.  Estimate annual expenses

 Rent                      $ 12,000

 Insurance                   3,000

 Utilities                       2,400

 Labor                        24,000

 Repairs & Maintenance  2,000

 Owners Salary            24,000

 TOTAL                    $ 67,400

2.  Estimate your gross margin percentage

 Per $1.00 of retail sales:      $ 1.00

  Cost of inventory               $   .70        70%

  Gross profit margin            $   .30        30%

3.  Calculate the sales required to meet expenses:

   -Take total expenses ($67,400) divide by gross margin percent (30%)

 -Equals sales required      $224,667

4.  Calculate the inventory required for sales:

 -Take sales required to meet expenses (#3) ($224,667)

 -Multiply times cost of inventory percentage (70%)

 -Equals inventory required for sales    $157,267

5.  Adjust inventory based on times inventory turns

 -Take inventory required for sales (#4) ($157,267)

 -Divide by time inventory turns (2 times)

 -Equals inventory required for startup   $ 78,633

This calculation is often an eye-opener for the small retail store that plans to open with only $15,000 in inventory.  You can see from the above example it’s difficult to make a large living in retailing with only $15,000, unless the inventory turns over 10 or 12 times per year.

For a manufacturer the calculation can usually be done using their product manufacturing cost estimates, and using the materials cost percentage times the projected sales for year one.

Accounts receivable can be a consideration for those businesses who will have to sell on credit.  One of the best ways to project these is to look at them based on a percentage of sales.  Bankers who analyze your Receivables normally like to see Receivables with a turnover of less than 90 days.

Fixed assets are also a major consideration for most small businesses.  A fixed asset is any asset that will last more than one year.  Examples include real estate, equipment, vehicles, breeding livestock, etc.  In Module 3 you had the opportunity to put down initial cost estimates on your worksheet, which should go here.

Liabilities and net worth

The debt and net worth will total the same amount as your total assets.  The relationship between debt and equity can be important in starting operations.  In general, most lenders want to see your equity exceed their loans.  Thus, you will want to show as much equity as you can to entice lenders.  Contributions made personally to a proprietorship can be reflected as equity, thus tools or other equipment can be shown as assets and will also reflect as equity here.

If the business is a startup, and proposing a loan in your plan, you will want to include the loan as a liability in your startup balance sheet.  If you are looking at a term loan, say to be repaid over five years, then it helps to print out a loan amortization schedule, too.  Then you can allocate your payments of principal for the years to current portion of term debt (a short term liability) and the long term portion to long term liabilities under Notes Payable.

Income statement projections:  years 1 through 5

Start with annual revenues, looking at number of units or desired production goals to begin.  If you don’t yet have an idea of what sales should be at this stage, don’t despair.  You can use the Robert Morris Associates (RMA) studies to estimate what you should be doing in sales for a firm in your industry with the same asset size.  You can do this easily taking the RMA industry standard and multiplying the ‘sales divided by total assets’ ratio for your industry by your total assets.  Then take this number and divide by the number of units you will have to sell or produce to see if it is reasonable.

For years two through five for sales and other expenses, often you will look at a percentage increase based on your overall growth plans.  If inflation is running at three percent, you will want to use a growth rate above this to show ‘real’ growth.  Another technique is to look at production figures in terms of unit sales and actual expenses.  If your growth plans are extremely aggressive (in excess of 50 percent during a year) it may be preferable to use this later technique.  As you are working on your projection be sure to scribble down notes on your assumptions, as these will be important when you start on your business plan write-up.  After you have finished projecting your income statement you will need to look at the balance sheet.  Here’s where the computer program makes the job easier. The program will make your debt reductions, asset and liability changes based on growth and historical percentages, and show a borrowing need or cash surplus based on the situation.  You can then make adjustments based on what you see the results are.  Doing this with pencil and paper will require a large eraser and lots of time!

The cash flow statement

It is important in operating a business that you have a handle on cash flow.  You can be both profitable and broke if you don’t pay attention to your cash.  The easiest way to project cash flow is to take your income statement projections and make adjustments.  Some of the obvious adjustments are loan payments and depreciation and amortization.  The principal portion of any loan payment is not shown as an expense and needs to be deducted from net profit as an adjustment.  Depreciation and amortization are ‘non-cash’ expenses, and are added to net profit to calculate cash flow.  Other considerations are changes to operating assets and liabilities.  A growth in accounts receivable or inventory will be a user of cash and needs to be a deduction.  A decline in either will need to be shown as an addition.  Accounts payable increases are a source of cash flow and will be shown as an addition, while decreases are a use of cash and shown as a deduction.  The following describes the adjustments which need to be made to a Profit & Loss Statement to convert it to a Cash Flow Statement:

 Net Profit                                           $ _________

 less:   Loan principal payments             $ _________

  Increases in Accounts Receivable         $ _________

  Increases in Inventory                        $ _________

  Decreases in Accounts Payable             $ _________

 plus: Depreciation & Amortization          $ _________

  Decreases in Accounts Receivable         $ _________

  Decreases in Inventory                        $ _________

  Increases in Accounts Payable              $ _________

 Cash Flow                                            $ _________

This is another area where the computer program shines.  It will normally take your inputs and projections of the above variables and automatically calculate cash flow for you.

One last note on cash flow to emphasize the importance.  We have observed retail clients who had failed to consider the impact of growing their inventory.  In one case the client was profitable in their first year, but were forced to go out of business because they failed to plan on the impact of cash flow.  If they had only projected their cash flow they probably would have been aware of this problem, and either raised more capital to make their business a success or not have started up at all.

Monthly projections for year one

Many businesses should look at their projections on a monthly basis for the first year at least.  This can be done either before the first annual projection or after by taking these figures and allocating to the months.  For a startup the monthly financials will take some time to build up and this needs to be considered here.  Rarely will your first year be a whole one. 

Inputting Variables

The entry of variables is done on a month to month basis for year one, quarterly for years two through three, and annually for years four and five.  The worksheets progress left to right in the workbook, and key factors are described for each sheet below:

B Balance Sheet

This is your beginning balance sheet.  For a startup business this is usually the starting point in the projection process, with a determination of the needs for operating and fixed assets, the equity contribution, and debt required if needed.  For an existing firm, this is the latest year end (or interim) financial statement.  Being an Excel worksheet, the user can make changes to column and row headings, and add additional columns or rows at the peril of modifying the formulas to be useless.  Items of note on the B Balance Sheet entry are:

1.  Date needs to be entered over existing date on spreadsheet

2.  Entry of key balance sheet data

3.  Print a loan amortization schedule, if required, when you know there is a term loan amount required.  If a short term borrowing need is determined, this will usually come out when you prepare your monthly cash flow (to follow).  The amortization schedule will assist with determining your CPLTD (current portion long term debt) and LTP Term Debt  (long term portion term debt), not only for year one, but subsequent years.

4.  Depreciation should be estimated for fixed assets on entry section at the bottom of the sheet.

5.  Interest rate on term loan should be entered on section to the right of the data fields.

1st Year Income Statement

 This is your first years projection, with key data entered monthly.

 1.  Change date and months on top of page.

2.  Enter key factors below

3.  Enter projected sales and expenses

1st Year Cash Flow

This is your first years projection, with cash flow shown from balance sheet and income statement projections.  Only changes will need to be shown, for example, cash balance at negative will need to be covered with equity injections or working capital loan.  Excess cash can be shown as excess reduction to debt or excess cash on the balance sheet.

1.  Change date and months, as required.

2.  Enter assumptions on right side of data fields.

3.  Enter loan proceeds or draws to balance cash at bottom of spread.

1st Year Balance Sheet

 This is your balance sheet at the end of year one. 

  1.  Change date, as required.

            2.  Enter CPLTD & LTP Term Debt from Amortization Schedule, if required.

            3.  Change other balance sheet entries as required.

   4.  Balance Assets and Liabilities, and use excess cash on balance sheet to allow for changes from income statement not reflected on balance sheet.  Assets and Liabilities plus Capital must be the same amount.

2nd Year Income Statement

This is your second years projection, with key data entered quarterly.

1.  Change date on top of page as required.

2.  Review and edit data and make changes as required.

2nd Year Cash Flow

This is your second years projection, with cash flow shown from balance sheet and income statement projections.  Only changes will need to be shown, for example, cash balance at negative will need to be covered with equity injections or working capital loan.  Excess cash can be shown as excess reduction to debt or excess cash.

 1.  Change date and months, as required.

2.  Enter assumptions on right side of data fields.

3.  Enter loan proceeds or draws to balance cash at bottom of spread.

 2nd Year Balance Sheet

 This is your balance sheet at the end of year two. 

  1.  Change date, as required.

            2.  Enter CPLTD & LTP Term Debt from Amortization Schedule, if required.

            3.  Change other balance sheet entries as required.

4.  Balance Assets and Liabilities, and use excess cash on balance sheet to allow for changes from income statement not reflected on balance sheet.  Assets and Liabilities plus Capital must be the same amount.

 3rd Year Income Statement

This is your third years projection, with key data entered quarterly.

 1.  Change date on top of page as required.

2.  Review and edit data and make changes as required.

3rd Year Cash Flow

This is your third years projection, with cash flow shown from balance sheet and income statement projections.  Only changes will need to be shown, for example, cash balance at negative will need to be covered with equity injections or working capital loan.  Excess cash can be shown as excess reduction to debt or excess cash.

1.  Change date as required.

2.  Enter assumptions on right side of data fields.

3.  Enter loan proceeds or draws to balance cash at bottom of spread.

3rd Year Balance Sheet

This is your balance sheet at the end of year three. 

1.  Change date, as required.

2.  Enter CPLTD & LTP Term Debt from Amortization Schedule, if required.

3.  Change other balance sheet entries as required.

4.        Balance Assets and Liabilities, and use excess cash on balance sheet to allow for changes from income statement not reflected on balance sheet.  Assets and Liabilities plus Capital must be the same amount.

4th Year Income Statement

This is your fourth years projection, with key data entered annually.

1.  Change date on top of page as required.

2.  Review and edit data and make changes as required.

4th Year Cash Flow

This is your fourth years projection, with cash flow shown from balance sheet and income statement projections.  Only changes will need to be shown, for example, cash balance at negative will need to be covered with equity injections or working capital loan.  Excess cash can be shown as excess reduction to debt or excess cash.

1.  Change date as required.

2.  Enter assumptions on right side of data fields.

3.  Enter loan proceeds or draws to balance cash at bottom of spread.

4th Year Balance Sheet

This is your balance sheet at the end of year four. 

            1.  Change date, as required.

            2.  Enter CPLTD & LTP Term Debt from Amortization Schedule, if required.

            3.  Change other balance sheet entries as required.

   4.  Balance Assets and Liabilities, and use excess cash on balance sheet to allow for changes from income statement not reflected on balance sheet.  Assets and Liabilities plus Capital must be the same amount.

5th Year Income Statement

This is your fifth years projection, with key data entered annually.

 1.  Change date on top of page as required.

2.  Review and edit data and make changes as required.

5th Year Cash Flow

This is your fifth years projection, with cash flow shown from balance sheet and income statement projections.  Only changes will need to be shown, for example, cash balance at negative will need to be covered with equity injections or working capital loan.  Excess cash can be shown as excess reduction to debt or excess cash.

1.  Change date as required.

2.  Enter assumptions on right side of data fields.

3.  Enter loan proceeds or draws to balance cash at bottom of spread.

5th Year Balance Sheet

This is your balance sheet at the end of year five. 

1.  Change date, as required.

2.  Enter CPLTD & LTP Term Debt from Amortization Schedule, if required.

3.        Change other balance sheet entries as required.

4.        Balance Assets and Liabilities, and use excess cash on balance sheet to allow for changes from income statement not reflected on balance sheet.  Assets and Liabilities plus Capital must be the same amount.

Printing Reports

The reports shown above are the basic output of the system.  Printing these reports should be quite simple for your system, and normally will print out correctly on a printer configured for Excel 97 to print on 8.5 x 11 inch paper. 

5 13 CF Consolidated

This is your report of consolidated cash flow  which gathers data from other entry worksheets.  This is one of the primary reports to print out.  Review the data, print out and review again.  A careful study of output will allow corrections before the data is presented to an outsider or investor who will suspect your whole plan if they can’t trust your numbers! 

Changes must be made to entry fields for years that need correction rather than merely changing the fields in this report.

5 12 IS Consolidated

This is your report of consolidated income statement which gathers data from other entry worksheets.  This is one of the primary reports to print out.  Review the data, print out and review again.

Changes must be made to entry fields for years that need correction rather than merely changing the fields in this report.

BS Consolidated

This is your report of consolidated balance sheet which gathers data from other entry worksheets.  This is one of the primary reports to print out.  Review the data, print out and review again.

Changes must be made to entry fields for years that need correction rather than merely changing the fields in this report.

Other  reports as required

Other reports that one might wish to print out include the following:

1.        First year balance sheet, which includes a ratio analysis section at the bottom of the page.

2.  Second or third year income statement and cash flow show quarterly analysis, and may be helpful in some situations, particularly if there is a working capital requirement or seasonality to the business. 

3.   Any other entry screen the user might find valuable.

5 15 Break-even analysis

Another financial spreadsheet you should consider is the Break-even Analysis.  This is a look at what sales level the business will not show either a profit or loss, but break- even.  This allows you to monitor your activity and know based on this analysis, what the sales per day, week or month are, and whether you are making a profit or loss.  To calculate your break-even use the following equation:

 Fixed expenses divided by (1 - variable cost as a percent of sales) 

For example, you have sales of $100,000, variable costs $60,000, variable cost as a percent of sales of 60% ($60,000 divided by $100,000), fixed costs of $30,000, and a net profit of $10,000.  Your break-even would be calculated as follows:

 Fixed expenses ($30,000) divided by (1 - variable cost as a percent of sales (.60))

or $30,000 divided by .40 = $75,000

From this you can determine that your break-even sales are $6,250 per month ($75,000 divided by 12 months) which will allow you to monitor your sales volume for the month to know if you’re making money or not at a glance.  If you haven’t reached $6,250 for the month you won’t be profitable for that month.  If a daily figure would be better, divide the monthly figure by the number of working days in the month, and so on for weekly or other.  Don’t forget to include your salary requirements in the figures.  If you don’t, then you will not be looking at taking any salary for your efforts

Robert Morris Associates (RMA) Case study analysis

Here you will show your projections beside the standard for your industry.  The RMA studies are averages for your industry, and helpful in identifying strengths and weaknesses to your projections.  Using the RMA studies report you can show your percentages spread next to the RMA standard which allows easy comparison.  This is called ‘common sizing’.   The printout described earlier has a section to manually input RMA figures to compare to your company projection.

5 17 Financial Ratio Analysis

This final report is valuable to bankers and other investors who study financial statements for a living.  As you become more familiar with ratios you can use them to operate and monitor your business more effectively.  The most important ratios deal with the following areas:

Liquidity ratios

These ratios measure your companies ability to meet your obligations.  They look at the relationship between current assets (sources of cash) and their ability to cover current liabilities (uses of cash).  The turnover ratios measure your ability to convert these current assets into cash or current liabilities as a use of cash.

Current Ratio:  Current assets divided by current liabilities.  Normally expressed as a whole number, a ratio of 1.5 to 3.0 is good.  Small businesses that start with no debts will show an infinite ratio, which is not applicable.

Acid Test (Quick) Ratio:  Cash, accounts receivable and other liquid assets divided by current liabilities.   Also expressed as a number, a ratio of .85 to 2.0 is normal.

Accounts Receivable Turnover Ratio:  Accounts receivable divided by sales times the number of days in the period.  This is expressed in days, and a ratio of less than 90 days is usually acceptable.

Inventory Turnover Ratio:  Inventory divided by Cost of Sales times the number of days in the period.  This ratio can vary by industry greatly, a ratio of 120 days would be good for some retailers, not so good for others.

Accounts Payable Turnover:  Accounts Payable divided by Cost of Sales times the number of days in the period.  A ratio of less than 90 days usually indicates you’re doing an adequate job of paying your bills.

Solvency Ratios

These ratios look at your companies net worth in relation to liabilities and revenues.  These ratios are often referred to as leverage ratios, and when a banker tells you that you’re too highly leveraged they are referring to this relationship.

Debt to Worth Ratio:  Total liabilities divided by net worth.  Expressed as a percentage, this ratio compares debt to equity.  A ratio of less than 100% is usually good.  A bank will start getting concerned when your ratio exceeds 300%.

Debt to Assets:  Total liabilities divided by total assets.  Expressed as a percentage, this ratio looks at the percentage of your assets that are financed.  Less than 100% is preferred, with 50% being a good ratio for most businesses.

Profitability Ratios

Return on Equity Ratio:  Net profit before tax divided by net worth.  Expressed as a percentage, this ratio measures your return on your net investment (after debts).  It should normally exceed 20% for small businesses, where a higher risk dictates a higher return.

Return on Assets Ratio:  Net profit before tax divided by total assets.  Expressed as a percentage, this ratio looks at your overall return on investment for lenders and equity.  The ratio should normally exceed 10% for small businesses.

5 16 Writing up your assumptions

Your financial plan portion of your business plan will usually lead with the summarization of what your projections are all about.  This involves putting your financial assumptions down in writing and explaining what this means to your success.  Use a lot of figures to support what you conclude, and refer to the financial spreads which will be attached.  If it helps, separate the assumptions and show them as a table for the various years projected.  The key here is to give the reader enough background to know you didn’t just pull the figures out of the sky, and to share the ‘scientific’ method you employed.  Try to stress the positive and show how you plan to overcome any negatives in the financial area.