Cash Budgeting
and Pro Forma Statements

GLOSSARY

Cash Budget:  A document (or electronic spreadsheet) that shows the timing of cash flows into and out of the company.

Pro Forma StatementsA projected income statement (for the next several months) and balance sheet (at the end of the forecasted time period).


The Cash Budget

One of the major functions of corporate finance is to anticipate the need for funds in the company.  Although there are several methods for forecasting these needs, the cash budget is the most accurate (and most commonly used) method available.

Cash flows through a company like water flows through a pipe.  (See the cash flow pipeline.)  A cash budget essentially maps out these cash flows and indicates the period in which they will occur.  

Cash budgeting, at its core, is a very simple procedure that simulates cash flowing through a company.  If we can accurate estimate (1) the amounts and (2) the timing of these cash flows, then we can also estimate whether the company will experience a shortage of cash (or an excess of cash) during any given month.

The primary cash inflows for a company consist of (1) cash sales and (2) collection of accounts receivable.  An accurate sales forecast is a necessity if we are to construct an accurate cash budget.  If we then know the terms that we sell on (2/10, net 30, for example) and the past payment experience of our customers, we can estimate the amount and timing of these collections.

The primary cash outflows are generally (1) payments on purchases, (2) labor costs, and (3) capital purchases (i.e., fixed assets).  Other large payments may include rent, taxes, and These and other cash outflows must be estimated as to the amount and timing of the payments.

Pro Forma Statements

It is also useful to know what the company's income statement will look like for the forecasted period and what the balance sheet will look like at the end of the forecasted period.  If either of these two statements show weaknesses, we can plan now to take steps that will prevent these deficiencies from occurring.

Fortunately, cash budgets and pro forma statements are easily programmed using electronic spreadsheets like Microsoft Excel.  This is especially handy since cash budgets involve a large number of relatively simple calculations.

Spreadsheets

Cash budgets are usually placed on an electronic spreadsheet, where data may be entered and updated frequently.  The two spreadsheets below are examples of spreadsheets for 3-month and 12-month planning periods.

Cash Budget Spreadsheet -   3 months

Cash Budget Spreadsheet - 12 months

An Illustration

Let's consider a sample cash budgeting problem, with its solution.  An explanation of how the numbers are determined may be seen by clicking on the appropriate links in the "Solution" section of this page.


A SAMPLE PROBLEM

Glenda Byers has gathered the data below needed for the preparation of a cash budget for the months of October, November, and December.

SALES:  (Actual sales for August and September, forecasted sales for October - February)

August

$ 45,000

September

54,000

October

65,000

November

75,000

December

93,000

January

71,000

February

55,000

Based on the past history and current credit terms offered by her firm, Glenda has estimated the following payment schedule for sales:

   25% of total sales will be cash sales;

   60% of total sales will be collected in the month following the sale;

   15% of total sales will be collected in the second month following the sale .

(Note:  Notice that since the three above collections total 100%, no bad debts on accounts receivable are anticipated.  If we anticipated a 2% bad debt rate, we could change the 15% number above to 13%.  We would then be anticipating that we would collect only 98% of the total sales.)

PURCHASES

The company’s markup on inventory is two-thirds of the cost (e.g. a $40 profit on an item which cost $60.)  In other words, the cost of the merchandise is 60% of the final selling price.  The firm purchases enough inventory to cover sales one month in the future.  Based on current credit terms, Glenda expects the following repayment schedule: 

   12% of purchases are paid for in cash;

   78% are paid one month following the purchase, and

   10% are paid for two months after the purchase.

EXPENSES (per month, unless otherwise noted): 

Mortgage Payment

$ 2,300

Wages and Salaries

3,200

Lease Payments

750

Capital purchases (to be purchased in October)

7,600

Property Taxes (to be paid in December)

11,800

Other Expenses (%)

10% of sales

FURTHER ASSUMPTIONS

  1. Cash:
  2. Loan Information:
  3. Fixed Assets: The firm depreciates all capital equipment purchases over a five-year period using the straight line method.
  4. Minimum inventory: The firm keeps $30,000 of safety stock on hand at all times. 

 

HISTORICAL INCOME STATEMENT
FOR THE PAST TWELVE MONTHS

(October 1st of last year through September 30th of this year) 

Sales

 

$648,000

- Cost of Materials

 

388,800

Gross Profit

 

259,200

Less Expenses:

   

Mortgage Payment

27,600

 

Wages and Salaries

38,400

 

Lease Payments

9,000

 

Interest

200

 

Depreciation

1,500

 

Property Taxes

47,200

 

Other Expenses

  64,800

 

    Total Expenses

 

188,700

Net Income

 

70,500

 (Ignore any income taxes for this problem.)

BALANCE SHEET
As Of September 30th

Cash

 

3,000

   Notes Payable

2,000

Accounts Receivable

 

47,250

   Accounts Payable

37,560

Inventory

 

69,000

   Long Term Debt

0

Fixed Assets, gross

42,000

 

   Common Stock

23,000

 - Acc. Depreciation

14,700

 

   Retained Earnings

  83,990

   

  27,300

   

Total Assets

 

146,550

   Total Liab. & Cap.

146,550


SOLUTION

(Click here for a detailed explanation for how the numbers in the cash budget are determined.)

CASH BUDGET
for October, November, and December

 

AUG.

SEPT.

OCT.

NOV.

DEC.

JAN.

Sales

45,000

54,000

65,000

75,000

93,000

71,000

Cash Sales (25%)

11,250

13,500

16,250

18,750

23,250

17,750

Collections:

           

  One month lag (60%)

 

27,000

32,400

39,000

45,000

55,800

    Two month lag (15%)

   

  6,750

  8,100

  9,750

11,250

TOTAL CASH RECEIPTS

   

55,400

65,850

78,000

84,800

             

CASH DISBURSEMENTS:

           

Purchases (shown for convenience only)

32,400

39,000

45,000

55,800

42,600

 

Payments:

           

 Cash Purchases (12%)

3,888

4,680

5,400

6,696

5,112

 

   Lagged one month (78%)

 

25,272

30,420

35,100

43,524

 

   Lagged two months (10%)

   

3,240

3,900

4,500

 

Mortgage Payment

   

2,300

2,300

2,300

 

Wages and Salaries

   

3,200

3,200

3,200

 

Lease Payments

   

750

750

750

 

Interest

   

20

60

0

 

Capital purchases

   

7,600

0

0

 

Property Taxes

   

0

0

11,800

 

Other Expenses

   

  6,500

  7,500

  9,300

 

TOTAL CASH DISBURSEMENTS

 

59,430

59,506

80,486

 
             

FINANCIAL TRANSACTIONS SECTION:

         

Total Cash Receipts

   

55,400

65,850

78,000

 

Less Disbursements

   

59,430

59,506

80,486

 

Net Cash Flow

   

(4,030)

6,344

(2,486)

 

Add Beginning Cash

   

  3,000

 3,000

  3,314

 

Ending Cash Balance

   

(1,030)

9,344

828

 

Less Minimum Cash Balance

   

  3,000

3,000

3,000

 

Required Total Financing

   

4,030

0

2,172

 

Excess Cash Balance

   

0

6,344

0

 

Borrowing Needed This Month

   

4,030

0

2,172

 

Repayments on Loan This Month

   

0

6,030

0

 

Total Loan Balance

   

6,030

0

2,172

 

Ending Cash (After Borrowing)

   

3,000

3,314

3,000

 

PRO FORMA INCOME STATEMENT
October 1st - December 31st

(Click here for a detailed explanation for how the numbers in the pro forma statements are determined.)

Sales

 

$233,000

- Cost of Materials

 

139,800

     

Gross Profit

 

93,200

Less Expenses:

   

Mortgage Payment

6,900

 

Wages and Salaries

9,600

 

Lease Payments

2,250

 

Interest

80

 

Depreciation

737

 

Property Taxes

11,800

 

Other Expenses

23,300

 

 Total Expenses

 

 54,667

Net Income

 

$38,533

 

PRO FORMA BALANCE SHEET
December 31
st

Cash

 

3,000

 Notes Payable

2,172

Accounts Receivable

 

81,000

 Accounts Payable

43,068

Inventory

 

72,600

 Long Term Debt

0

Fixed Assets, gross

49,600

 

 Common Stock

23,000

 - Acc. Depreciation

15,437

 

 Retained Earnings

 122,523

   

  34,163

   

Total Assets

 

$190,763

 Tot. Liab. & Cap.

$190,763

Sample Problems with solutions (in .pdf format)

Delmar, Inc. Merchandise Unlimited, Inc.

Reilly, Inc.

Tees, Inc.