Doing Projections in SurvivalWare – Part 3: Debt Service and Investment

Two key components of  your cash flow are 1) what happens to your debt (new borrowings, repayments, interest) and 2) spending money on stuff that doesn’t show up on the income statement right away  (Investment / Capital Expenditures).

 

This is part 3 of a 5 part series on how to create a cash flow projection using SurvivalWare and the generic financial model that comes with it.

 

  1. Getting familiar with the Forecast Tool
  2. Sales and Expenses
  3. Debt Service and Investment
  4. Working Capital
  5. Other Cash Flow

 

Debt Service

 

There is where you put assumptions about new borrowings, principal repayments, and interest payments.

 

You may not separate the interest expense out on your books by category of debt.  I’ve seen a lot of companies with one interest expense account on the Income Statement, but then maybe 5 or 6 loan accounts on the Balance Sheet.  If this is the case, the model won’t calculate a good historical interest rate for you.  You’ll have to estimate what it is for each category of debt; or just calculate one rate overall and use that same rate for all categories.

 

NOTE:
 
 

 

 

Borrowings are entered as POSITIVE NUMBERS.

Principal repayments are entered as NEGATIVE NUMBERS.

Interest is entered as an ANNUAL INTEREST RATE for each category of debt.

 

 

Example – Credit Card Debt

 

If you plan to pay off $5,000 in credit card debt in July, 2008: click on that cell, enter -5000.

Entering credit card repayment

After you hit ENTER, the value is accepted by the grid, and the model updates the calculated values such as “(Princ Repay) – Total”, “Credit Card Debt – Balance”,  and “Total Debt.”

Line items affected by Credit Card repayment

Scroll down a little more to enter the “Interest Rate” for your credit card debt.  Typing an asterisk (*) before the number indicates you want to use the same number for all remaining months.

Entering Interest Rate assumptions

The Interest Rate is entered as an annual percentage rate.  You should use a whole number (e.g. 23.99 not 0.2399 to indicate 23.99%).  The model divides by 12, and applies this monthly rate to the prior month’s ending balance to compute Interest Expense for each month.

Result of Interest Rate applied to last month\'s balance

 

Investment  /  Capital Expenditures

 

This is where you enter planned capital expenditures such as Equipment or Vehicles.  You also need to enter the depreciation roll-off for your existing fixed assets.

 

Here is an example of a $20,000 equipment purchase in July 2008.  We type the number “20,000” in the “5 Yr Life” category.  The model calculates the “Cash Flow from Investments” as a negative number (a cash outflow), and shows the impact on Depreciation and Net Fixed Assets as you scroll down.

Entering Capital Expenditures

If you are leasing the equipment, you would skip this section and just enter the lease payments in one of the Expense categories.  (There are 5 “Other Expense” line items at the send of the expense section for this purpose).

 

If you are borrowing some or all of the money, you would enter the purchase price in this section in the month of purchase, and then enter the amount borrowed as a positive number in the Debt Service section.

 

Doing Projections in SurvivalWare – Part 2: Sales and Expenses

The first step in putting together a complete financial projection is to forecast Sales and Expenses.

 

This is part 2 of a 5 part series on how to create a cash flow projection using SurvivalWare and the generic financial model that comes with it.

 

  1. Getting familiar with the Forecast Tool
  2. Sales and Expenses
  3. Debt Service and Investment
  4. Working Capital
  5. Other Cash Flow

 

 

You can forecast Sales by product line, or just Sales in total – whichever you feel more comfortable with. 

 

For other line items, sometimes you will be forecasting rates or percentages instead of dollar amounts.  Gross margin is an example of this, as is Payroll tax %.  You’ll find that the rate has been calculated for you in the history time periods, so you can see what it has been before deciding what to use going forward.

 

Sales

 

If you’ve got two full years of history, and your sales are the least bit seasonal,  I would start with a seasonal forecast.  You have the option of forecasting each of 10 product lines separately, or entering one overall sales forecast.  Unless you have good records on the gross margins of each of the separate product lines, I would just forecast the total.

 

Here is some actual sales history from a lawn mowing business based in the Northeast U.S.  As you might expect, it is highly seasonal.

Two years of sales history

This is what it takes to do a seasonal forecast using the Forecast Tool:

Selecting the \" width=

And this is what the seasonal forecast looks like:

 

Graph of Seasonal forecast for Sales

Expenses

 

Some overhead items such as rent or salaries you’ll know the number and want to enter it by hand.  Others you’ll want to use the forecast tool to project a percentage increase over last year, or just use the year to date average.

 

Here’s a way to say “use the same values as last year.”  Mark a bunch of expense lines, and click on the Forecast Tool icon.  Then click “Last Year + X%” and leave X at 0%.

 

Use the same values as last year

 

 There are four special line items in the list of operating expenses that are calculated based on percentages:

 

  • Benefits
  • Commissions
  • Payroll Taxes
  • Royalties

 

Benefits

 

Sometimes benefits are fixed (e.g. health insurance), and sometimes they are better estimated as a percentage of total salaries and wages – e.g. 401k contributions.  If you enter a percentage on the Benefits % line, the percentage takes precedence.  The percentage is applied to Total Wages and Salaries, which is the sum of Salaries (Officers and Other), Commissions, and Bonuses. 

 

You can override that by entering zero for the percentage, and keying in dollar amounts (or applying the Forecast Tool) directly on the Benefits line.

 

Commissions

 

The percentage is applied to total sales to compute commission dollars.

 

Payroll taxes

 

This is the employer share of FICA in the U.S., and in theory should be 7.65% of total salaries and wages (the sum of Salaries, Commissions, and Bonuses).  Toward the end of the year, this percentage may come down if you have employees surpass the Social Security limit of $100,000 in earnings for 2008.

 

Royalties

 

This is for franchise businesses who must pay a percentage of sales to a franchisor.  The percentage is applied to Total Sales.  If you don’t pay royalties, just leave the percentage at zero.

 

The result of all this is a projection of Operating Income, which is where most people stop, but which is just one of four major components of  cash flow.

 

Doing Projections in SurvivalWare – Part 1: the Forecast Tool

This is a 5 part series of articles covering the fundamentals of doing financial projections using SurvivalWare and the generic financial model that comes with it.

 

  1. Getting familiar with the Forecast Tool
  2. Sales and Expenses
  3. Debt Service and Investment
  4. Working Capital
  5. Other Cash Flow

Part 1 – getting familiar with the Forecast Tool

 

Intro

 

The Forecast Tool is designed to help you verbalize your assumptions about a projected line item, and convert the assumptions to hard numbers.

 

Definition of “Projections”

 

I’d like to define a projection as your best guess about what is going to happen given a number of explicit assumptions.

 

You could do a sales projection and stop there.  Your explicit assumption might be “I expect sales to 10% above the year ago value for the next several months.”  Or “I’d like sales to continue growing as they have recently, and exhibit the same seasonality.”  SurvivalWare accommodates either.

 

The role of the “model”

 

Individual line items

 

The model that comes with SurvivalWare combines the latest “actuals” with your projections to calculate the best possible estimate for combinations of months such as the current quarter, and the projection for the current year. (e.g., the current quarter could include one month of actuals and two months of projections).

 

You can either type in your projection, one number at a time, or use the Forecast Tool.  The Forecast Tool helps you verbalize your assumptions and convert them to hard numbers.

 

The complete picture

 

The model also takes dozens of individual projections and assumptions of what will happen to Sales, Expenses, and key drivers such as Collection Period and Inventory turnover – and combines these into an overall projection of what happens month by month to profits, the balance sheet, and cash flow.  Some refer to this as an integrated financial statement projection because the model captures the interplay between income statement, balance sheet, and cash flow.  The end result, is that you have a projection of how much cash you’ll have on hand month by month based on your detailed assumptions about sales and expense levels, planned capital expenditures, etc.

 

Forecasting a single line item

 

Here for example is a projection of Organic Visits to the SurvivalWare website.  Organic visits occur when someone uses a search engine to find our website (as opposed to a referral, a paid click, or direct entry of our URL).  We’ve been using Google Analytics since February 2007 to track traffic to the website, so we have 15 months of data.  This is not enough history to do a seasonal forecast, and I am not sure it is seasonal anyway.

 

Looking at the trend is easy as 1-2-3:

 

Looking at the Trend

 

Graph of Total Organic Visits

Total Organic visits cannot be forecast directly because it is a calculated row.  We can tell it is calculated because it has a yellow background, and we cannot type a number in one of the yellow cells.  What we can do is forecast the three rows that make it up all at once.

 

So we mark the three rows –  Google, Yahoo, and Other – and click the Forecast Icon.

 

Forecast Icon

 

10% growth vs. last year

 

Forecasting 10% growth vs. Last Year

 

 

Forecast of Organic visits to the Website

Simple trend

 

The simple trend uses the least squares regression technique to fit a straight line to the historical data, and extends that line into the future.  Just click the Forecast Icon again, select “Simple Trend” and click the Forecast button.  Here’s what you get:

 

Simple Trend

Looking at Years

 

You can switch the view from months to years at any time by clicking on the “Years” radio button in the upper right part of the screen.

 

Switching to Years from Months

 

This is what our 21 month forecast looks like when summed into years.

 

Looking at the Year Trend

Making a total leap of faith

 

Here’s what happens if we then use the forecast tool to take our 21 month forecast based on a total of 15 months of history and extend it out another 4 years.  Just click on the Forecast Icon again, and hit the Forecast Button to compute the “Simple Trend” for the years view.

 

Porjecting out 4 more years