Financial Forecasting in Microsoft Excel

Introduction

Creating Projections

Cash flow is the constantly changing stream that is the source of all the financial (and much of the non-financial) data being collected. Before we create a projection, let’s look at how cash flows through your organization. Regardless of the economic environment, you can predict short-term and long-term cash flow.

The Bucket Theory

Most business owners focus all their attention on generating profits. This is a mistake. When you broaden your focus to also include cash flow, you’re on the way to building a company with consistent value—and in the long run, that’s what really pays off.

Figure I.1 shows you how a business breathes. Instead of breathing air, it breathes money. Money comes in, and money goes out. Money (credit) comes from various sources such as sales from products and services (your normal operations), loans, owner contributions, and interest income, among other things.

The flow of cash (the water) makes its way into a general trough. Along the way, money goes out to support operations and expenses. Until the money starts coming in faster than it is going out and you create a waterline that is high enough, there is no cash available to pay you. You must monitor your cash at every point. The only time that you can really take money out of your business without killing it is when the money is coming in faster than it’s going out.

Cash flow reflects financial needs for operational requirements and reserves for the unexpected. The cash forecast is a plan of cash receipts (money coming in) and cash expenditures (money going out) for a specific period of time, usually in monthly increments. There are three different types of cash flow:

Figure I.1 The bucket theory

Negative—Money goes out of your pocket faster than you can put it in your pocket.

Breakeven—Money goes out of your pocket at the same rate as it enters your pocket.

Positive—Money goes into your pocket faster than it goes out, leaving money for you!

The objective of a business is to create long-term, positive cash flow through operations.

Why Do I Need a Cash Projection?

Cash flow projections are essential for the following reasons:

To assess project feasibility;

To fund company cash needs, giving the company advance warning in which to plan and develop executive sensible borrowing programs;

To determine financial feasibility of various programs or projects before commitments are made;

To obtain funds before they’re needed so they’re available when needed;

To identify problems before they become major. By comparing deviations of forecasted levels of cash with actual results, management is offered an opportunity to revise or react to unforeseen or uncontrollable developments;

To open up opportunities by being in a better position to take advantage of cash discounts, make small-term investments, or take advantage of other opportunities to use liquid funds more profitably;

To facilitate organizational alignment. Effective forecasting calls for an organized effort, cooperation of all non-financial executives, time, and energy.

Sources of information for cash projections are:

Sales schedule

Construction schedule

Land development cost plan

Overhead expense plan

Steps in Creating a Forecast

There are essentially eight steps in creating a forecast, and we will cover each step as we go through the book.

Step1.Establish your objectives.

Step2.Determine your cash inflows from sales.

Step3.Schedule your costs of production.

Step4.Budget your overhead costs.

Step5.Determine other sources of funds.

Step6.Determine what expenses you’ll need to pay that aren’t on your income statement.

Step7.Determine your equity needs and the amount you will pay your investors.

Step8.Do a postmortem on each project.

Step 1. Establish Your Objectives

This is usually profit oriented, but can include other objectives, such as conservation of capital, limitations on risk, and other sundry criteria.

Once you have looked at your historical results, determine a course of action. Define your goals (both financial and non-financial). The goals of an organization can be many and varied. They may include operational goals, marketing goals, sales goals, personnel goals, social goals, etc. On the broader scope, goals are usually profit oriented. However, depending upon the state of your industry and the current economic cycle, you might have other objectives, such as conservation of capital, limitations on risk, liquidity, stability of operations over the years, investment programs, future liquidations, or sale of the company.

Once established, these primary goals can then be broken down into sub-goals. For instance, suppose the enterprise has decided to increase its liquidity. Then the accounting department (to choose one example) can determine its sub-goals in terms of accounting, collections, payments and budgeting. The firm could define goals in terms of current ratio, working capital ratios, or labor to sales rations. Similarly, the marketing department could establish new pricing goals, stop pricing based on competitors pricing (who doesn’t make money either), stop underpricing because of not knowing cost of sales, or quit offering too many products.

The production department might establish goals to control overhead costs, check invoice pricing versus salesmen’s quotes, control waste, or control the material mix within its product. They could better react to external influences such as suppliers, availability of items and economic conditions, and be aware of unusual or unanticipated costs. Finally, they can watch the supply of inventory and expenditures for capital items.

Management must determine adequate staffing levels and retain adequate resources for dividends, bonuses, and salaries. To achieve the organizational goals, these divisional sub-goals must permeate and be coordinated throughout the whole enterprise.

1

Creating a Simple Spreadsheet

Where Do I Start?

As we move forward in our journey of creating projections, it’s apparent that everyone has different skill levels and abilities. So where do we start? We start at the very beginning; the basic skills. Some of what we cover may be redundant, but I’ve always found that there are always basic concepts to learn. I constantly review my basic books and am always amazed to discover new ways of making programming even easier. These solutions have always been in front of me, but it wasn’t until further review of basic concepts that I discovered them.

To create a simple spreadsheet in Microsoft® Excel, you enter text, numbers, and formulas. And that is what this chapter is about—the basics!

Parts of an Excel Spreadsheet

Active cell is the cell with the bold black outline.

Formula bar. Located above the work area (the cells) of the worksheet. It displays the contents of the active cell. It can also be used for entering or editing data and formulas.

Name box. Displays the active cell’s address or the name of a cell. You can also enter a cell’s (or range of cells) name.

The columns are named by letters

The rows are named by numbers.

The tabs at the bottom of a worksheet tell you the worksheet’s name.

Tools are also grouped by tabs at the top of the worksheet (e.g., File, Home, Insert).

Within a tab are commands grouped by function (e.g., Clipboard).

On the ribbon there are the actual working commands such as Cut, Copy, and Paste.

The Quick Access Toolbar contains shortcuts to common tasks such as: Print, Copy, Paste, Save, etc. They are on the File tab inline-image Quick Access