How to Create a Cash Flow Statement from Scratch

PREPARE A CASH FLOW STATEMENT:

Assume, Joan Duffy plans to buy the franchise rights to sell shoes. Her company will be called "The Direct Shoe Company" and all sales will be made through the mail (direct mail). She has conducted extensive market research and decided the business venture will be profitable. Joan has forecasted her company's expenses and revenues for a three year period. Also assume, today's date is April 30, 200X and Joan feels that the company will be up and running on July 1, 200X. The company's year end will be December 31 of each year.

Recently, Joan's aunt passed away and left her with $500,000. Unfortunately, Joan will not have access to the money until January 1, 200Y. Joan has $6,280 in savings and plans to invest all of it into the company. She feels, however, her savings will not be enough to start the business and keep it running until such time as she can receive the inheritance. She was told by a banker that a monthly forecasted cash-flow statement will predict how much money she will need from the date she plans to start the business (July 1, 200X) until the day before she receives the inheritance (December 31, 200X).

Joan had never prepared a cash-flow statement before. She asked the banker, "What information do I need to develop a monthly forecasted cash flow statement from July 1, 200X to December 31, 200X?" The banker replied with the following list of items;

Total Forecasted Sales in units or in dollars during the period (from July 1 - December 31, 200X);

Forecasted Monthly Sales Percentage - what percentage of total sales is forecasted for each month;

The above two items are not needed if you estimate the number of unit sales per month; (IE 900 units are expected to sell in July, 1150 units are expected to sell in August, and so on...)

  • The Selling Price per unit (if selling only one product) or the weighted average selling price (if selling multiple products):

  • A forecast of when customers will pay you - Will customers pay you with cash/check immediately following the sale or will they be given credit terms of 20,30,60, 90 or 120 days?
  • A forecast depicting when you plan to purchase products from your suppliers - this includes the number of products you plan to purchase each month and the credit terms your suppliers provide;
  • The beginning amount of cash in your businesses' bank account.

 

After a great deal of research, Joan finally collected the above information and organized it in the following manner.

CASH INFLOW ASSUMPTIONS: JULY AUG. SEPT. OCT. NOV. DEC. TOTALS
Forecasted Sales (in units) for period 5,000 5,000 5,000 5,000 5,000 5,000
Monthly Sales Percentages 18% 23% 24% 13% 10% 12% 100%
Units Sales per Month 900 1150 1200 650 500 600 5000 units
Selling Price Per Unit Sold $21 $21 $21 $21 $21 $21
Customer Collections in month one 80% 80% 80% 80% 80% 80%
Customer Collections in month two 20% 20% 20% 20% 20% 20%

CASH OUTFLOW ASSUMPTIONS: JULY AUG. SEPT. OCT. NOV. DEC. TOTALS
Purchase Price per Unit Bought $ 5 $ 5 $ 5 $ 5 $ 5 $ 5
Purchases Each Month (in units) 1935 1195 705 515 590 924 5864 units
Purchase Schedule 100% of July's sales in units + 90% of August's sales in units

10% of current month's sales + 90% of the following month's sales.

Credit Terms of Suppliers 30 days to pay for inventory purchased from suppliers.

Notice, Joan has set up seven columns - one for each of the six months and one to total the values for the six months. Lets briefly explain each of the above items so that you understand their importance. We begin with the forecasted sales (in units) for the period.

 

1.    FORECASTED SALES (IN UNITS):
After conducting her market research, Joan felt that she would sell 5,000 pairs of shoes between July 1, 200X and December 31, 200X.

  JULY AUG. SEPT. OCT. NOV. DEC. TOTALS
Forecasted Sales (in units) for period 5,000 5,000 5,000 5,000 5,000 5,000  

Do not be confused by the 5,000 units appearing under each month. This does not mean that Joan is forecasting the sale of 5,000 pairs of shoes each month, rather we have organized in this fashion for presentation purposes only (You'll see why this was done in a moment).

2.    MONTHLY SALES PERCENTAGES:
The monthly sales percentages represent Joan's estimation of when her total forecasted sales will materialize.

  JULY AUG. SEPT. OCT. NOV. DEC. TOTALS
Monthly Sales Percentages 18% 23% 24% 13% 10% 12% 100%

As shown above, Joan feels that 18% of her customers between July and December will purchase shoes in July, 23% of her customers between July and December will purchase shoes in the month of August, 24% will purchase in the month of September and so on. Joan would have arrived at these monthly sales percentage forecasts through personal judgement, common sense, and market research. Notice, the sum of the percentages add to 100%.

3.    UNIT SALES PER MONTH
The unit sales per month represent the number of shoes Joan estimates she will sell each month. To arrive at these values, Joan simply multiplies the total forecasted sales (in units) of 5,000 by each month's sales percentage forecast.

  JULY AUG. SEPT. OCT. NOV. DEC. TOTALS
Units Sales per Month 900 1150 1200 650 500 600 5000 units

As a result, Joan estimates she will sell 900 pairs of shoes in July (5000 x 18%), 1150 pairs of shoes in August (5000 x 23%), 1200 pairs of shoes in September (5000 x 24%), and so on. Notice, the sum of each month's forecasted unit sales add to 5,000 units. This value refers to the total number of shoes Joan feels she will sell between July and December.

 

4.     SELLING PRICE PER UNIT SOLD:
Selling price per unit represents the amount Joan plans to charge for each pair of shoes. If Joan was selling more than one product, each having a different selling price, she would be advised to calculated a weighted average selling price. Moreover the weighted average selling price reduces the total number of selling prices down to one value (IE one selling price).

  JULY AUG. SEPT. OCT. NOV. DEC. TOTALS
Selling Price Per Unit Sold $21 $21 $21 $21 $21 $21  

As you can see, Joan plans to sell each pair of shoes for $21.00 during July 1, 200X and December 31, 200X.

5.     CUSTOMER COLLECTIONS FROM SALES
Customer collections (in month of sale) attempts to estimate when customers will pay Joan for the shoes they buy.

  JULY AUG. SEPT. OCT. NOV. DEC. TOTALS
Customer Collections in month 1 80% 80% 80% 80% 80% 80%  
Customer Collections in month 2 20% 20% 20% 20% 20% 20%  

As shown above, Joan estimates her business will collect 80% of dollar sales in the month the sale is made. The remaining 20% is estimated to be collected in the month following the sale. For example, if Joan has total dollar sales of $18,900 in July, she will collect only $15,120 ($18,900 x 80%) in July and the remaining $3,780 ($18,900 x 20%) will be collected in August ($15,120 + $3,780 = $18,900). If total dollar sales for August are forecasted at $24,150, she would collect 80% of August's sales plus 20% of July's sales; in the month of August. Below illustrates Joan's expected cash collections from customers for each month between July 1 and December 31, 200X.

  JULY AUG. SEPT. OCT. NOV. DEC.
Forecasted Sales per month $18,900 $24,150 $25,200 $13,650 $10,500 $12,600

Customer Collections in month one (80%) $15,120 $19,320 $20,160 $10,920 $ 8,400 $10,080
Customer Collections in month two (20%) $ 0 $ 3,780 $ 4,830 $ 5,040 $ 2,730 $ 2,100

 

A lag in the cash collection from sales is a direct result of a company's credit granting policy. Moreover, many companies have to wait 30 days, 60 days or even 120 days before customers pay them for goods and services. Therefore, companies planning on granting credit must consider their credit term policy when creating a collection schedule. Below provides various types of collection schedules.

 

Customer collections in month 1 100% 75% 50% 30% 10% 0%
Customer collections in month 2 n/a 25% 50% 50% 40% 60%
Customer collections in month 3 n/a n/a n/a 20% 40% 35%
Customer collections in month 4 n/a n/a n/a n/a 10% 5%

TOTALS 100% 100% 100% 100% 100% 100%

 

6.    PURCHASE PRICE PER UNIT:
The purchase price per unit refers to the cost to purchase a product or service.

CASH OUTFLOW ASSUMPTIONS: JULY AUG. SEPT. OCT. NOV. DEC. TOTALS
Purchase Price per Unit Bought $ 5 $ 5 $ 5 $ 5 $ 5 $ 5  

As you can see, the cost to purchase each pair of shoes is forecasted at $5.00. If she was selling more than one type of shoe, or selling multiple products (each having a different product cost), Joan would probably calculate a weighted average product cost. This would reduce all the product costs down to one single value (IE one single product cost).

 

7.    PURCHASING SCHEDULE:
The purchase schedule represents the amount of inventory a company plans to have in any given month. The purchase schedule also relates to when inventory will be ordered or purchased.

  JULY AUG. SEPT. OCT. NOV. DEC. TOTALS
Purchase Schedule - 100% of July's sales in units + 90% of August's sales in units

- 10% of current month's sales + 90% of the following month's sales.

As you can see, the purchase schedule consist of two parts; "100% of July's sales in units + 90% of August's sales in units" AND "10% of current month's sales + 90% of the following month's sales". Part one only applies to the first month, since in July Joan will NOT have any inventory to accommodate her first month's forecasted sales in units. As a result, in July, Joan plans to purchase 100% of her July's forecasted unit sales plus 90% of the following month's (August) forecasted unit sales.

Beginning in August and each month thereafter, Joan will purchase the shoes using the 10% / 90% ratio. For instance, in August Joan will purchase 10% of her August's forecasted unit sales plus 90% of September forecasted unit sales. In September, Joan will purchase 10% of her September's forecasted unit sales and 90% of her October's forecasted unit sales. In October, Joan will purchase 10% of her October's forecasted unit sales and 90% of her November's forecasted unit sales, and so on...

As you might suspect, the purchase schedule will vary from business to business. Moreover, the timing of purchases directly relate to the nature of the business and when sales are expected to materialize. Some mail order businesses, for instance, may order their inventory at the end of any given month, while retailers generally purchase their inventory one, two, even three months in advance. Drop shippers, on the other hand, usually do not have to order or carry any inventory. These entities simply make a sale and send the order to the supplier for fulfillment. Once again, the purchase schedule will depend on the type of industry and the internal ordering policies of your company.

 

8.    PURCHASES MADE EACH MONTH
Purchases made each month represents the number of units or products a business will order each month. The monthly forecasted sales (see # 3) and the purchase schedule (see # 7) are used to calculate the dollar amount of purchases a business will make each month. Furthermore, using Joan's forecasted unit sales per month (shown above), along with her purchase schedule (also shown above), we can predict the number of units she must purchase each month. Below illustrates the number of shoes Joan plans to purchase each month.

  JULY AUG. SEPT. OCT. NOV. DEC. TOTALS
Purchases Each Month (in units) 1935 1195 705 515 590 924 5864 units

Lets look at how these monthly purchases were calculated. Assume that Joan's forecasted sales in units for JANUARY 200Y is 960 pairs of shoes.

  JULY AUG. SEPT. OCT. NOV. DEC. TOTAL
Forecasted sales in units 900 1150 1200 650 500 600 5,000

100% of current month's sales 900 n/a n/a n/a n/a n/a 900
10% of current month's sales n/a 115 120 65 50 60 410
90% of following month's sales 1035 1080 585 450 540 864 4,554

Total purchases each month 1935 1195 705 515 590 924 5,864

As you can see, Joan will purchase 1935 pairs of shoes from her supplier in July, 1195 pairs in August, 705 pairs in September, 515 pairs in October, 590 pairs in November, and 924 pairs of shoes in December 200X. The total number of shoes she plans to purchase between July 1, 200X and December 31, 200X is 5,864 pairs. Since Joan's forecasted total sales in units during this period is only 5,000, the remaining 864 units will be considered her ending inventory (5,864 - 5,000 = 864 pairs of shoes).

Notice, unit purchases in July is calculated at 100% of July's forecasted sales (900 units) plus 90% of August's forecasted sales (1150 x 90% = 1035 units). Purchases for the remaining months are calculated at 10% of the current month's forecasted sales plus 90% of following month's forecasted sales ( IE the 10% / 90% purchase ratio).

Also notice the 864 units appearing under December's "90% of following month's sales" category. This value was calculated using the assumption that forecasted sales in units for January, 200Y would be 360 pair of shoes (90% of 960 units equal 824 pairs of shoes that must be purchased).

Now that we know the number of shoes Joan is required to purchase, we can determine how much the purchases will cost. Recall from above, each pair of shoes cost $5.00.

  JULY AUG. SEPT. OCT. NOV. DEC. TOTAL
Purchases Each Month (in units) 1935 1195 705 515 590 924 5,864
Cost for each pair of shoes $5.00 $5.00 $5.00 $5.00 $5.00 $5.00

Total Cost of Purchases $9,675 $5,975 $3,525 $2,575 $2,950 $4,620  

 

9.    CREDIT TERMS OF SUPPLIERS
Credit terms of suppliers refer to the number of days (if any) suppliers will allow a business to pay for goods or services. Many suppliers provide credit terms of 30 days, 60 days, 90 days, or 120 days. Other suppliers will not provide credit to businesses, especially new business ventures. Below summarizes Joan's credit terms from suppliers.

  JULY AUG. SEPT. OCT. NOV. DEC. TOTALS
Credit Terms of Suppliers 30 days to pay for inventory purchased from suppliers.

As you can see, Joan's suppliers will allow her 30 days to pay for the shoes. In other words, if Joan orders 1935 units on July 1, she will not have to actually pay for them until August 1. In addition, if Joan orders 1195 pairs of shoes on August 1, she will not have to pay for them until September 1, and so on... Below illustrates the months in which Joan's purchases will be paid.

  JULY AUG. SEPT. OCT. NOV. DEC.
Total Payment of Purchases $0.00 $9,675 $5,975 $3,525 $2,575 $2,950

 

In the previous pages, we have established the foundation of Joan's Cash Flow Statement. Below summaries her assumptions and calculations.

CASH INFLOW ASSUMPTIONS: JULY AUG. SEPT. OCT. NOV. DEC. TOTALS
Forecasted Sales (in units) for period 5,000 5,000 5,000 5,000 5,000 5,000
Monthly Sales Percentages 18% 23% 24% 13% 10% 12% 100%
Units Sales per Month 900 1150 1200 650 500 600 5000 units
Selling Price Per Unit Sold $21 $21 $21 $21 $21 $21
Customer Collections in month one 80% 80% 80% 80% 80% 80%
Customer Collections in month two 20% 20% 20% 20% 20% 20%

CASH OUTFLOW ASSUMPTIONS: JULY AUG. SEPT. OCT. NOV. DEC. TOTALS
Purchase Price per Unit Bought $ 5 $ 5 $ 5 $ 5 $ 5 $ 5
Purchases Each Month (in units) 1935 1195 705 515 590 924 5864 units
Purchase Schedule 100% of July's sales in units + 90% of August's sales in units

10% of current month's sales + 90% of the following month's sales.

Credit Terms of Suppliers 30 days to pay for inventory purchased from suppliers.

 

Joan's final task is fivefold. Furthermore she must....

A.  -  Determine her tax obligation on the income she expects to make:
We will assume that Joan will pay her taxes in April of each year business year. Therefore, in our example, Joan does not have to consider the implication of taxes, since the forecasted period is between July and December of 200X. Below illustrates the month in which cash is required for Income Taxes.

  JULY AUG. SEPT. OCT. NOV. DEC.
For Income Taxes $ 0 $ 0 $ 0 $ 0 $ 0 $ 0

 

B.   -  Determine the amount she will pay the company's shareholders:
(IE through the issuance of dividends). We will assume that Joan's company is a sole-proprietorship. This means that the company has no shareholders and therefore no dividends can be declared. Note: sole-proprietorship can not declare dividends, rather the owners withdraw cash form the company.


C.  -  Determine the amount she will withdraw and when the drawings are anticipated:

Lets assume, Joan expects to draw $1,000 each month from the business between July 1, 200X and December 31, 200X.

  JULY AUG. SEPT. OCT. NOV. DEC
For Withdraws $1,000 $1,000 $1,000 $1,000 $1,000 $1,000

 

 

 

 

 

D.  -  Determine her Capital Asset purchases:

Capital Assets include items such as buildings, land, computer equipment, office equipment, office furniture, production equipment, and any other asset that is not expensed (IE has a useful life of more than one business year). Joan will need to estimate how much these assets will cost and when she will be required to pay for such purchases. Lets assume, she plans to purchase a computer, a desk, a laser printer, and a photocopier in July from HRS Office Equipment Inc for $9,500. Also assume, HRS Office Equipment will NOT offer her credit terms. Therefore, if Joan buys these items in July she must pay for them in July. Below illustrates the months in which cash is required for Joan's planned Capital Asset Purchases.

 

  JULY AUG. SEPT. OCT. NOV. DEC.
For Equipment Purchased $9,500 $ 0 $ 0 $ 0 $ 0 $ 0

 

E. -  Develop a monthly breakdown of her forecasted expenses:
In other words, she must estimate the amount of cash each expense will require for each month. Remember depreciation expense is NEVER used in a cash flow statement; it's considered a non-cash expense. After a great deal of thought, calculating and quote gathering, Joan developed the following chart to illustrate when each of her expenses will require cash (IE payment).

CASH REQUIRED FOR: JULY AUG. SEPT. OCT. NOV. DEC.
For Promotions $9,000 $1,003 $6,133 $8,533 $4,663 $4,003
For One Time Franchise Fee $6,500 $ 0 $ 0 $ 0 $ 0 $ 0
For Office Salaries Expense $1,300 $1,300 $1,300 $1,300 $1,300 $1,300
For Employer Costs (11% of Salary) $ 143 $ 143 $ 143 $ 143 $ 143 $ 143
For Office Supplies $1,200 $ 0 $ 0 $ 0 $ 0 $ 0
For Business Cards & Correspondence $ 325 $ 0 $ 0 $ 0 $ 0 $ 0
For Telephone $ 200 $ 100 $ 130 $ 120 $ 100 $ 100
For Business Registration & Fees $ 283 $ 0 $ 0 $ 0 $ 0 $ 0
For Message Center $ 850 $ 440 $ 700 $ 800 $ 850 $ 700
For Toll Free Services $ 590 $ 460 $ 550 $ 610 $ 690 $ 600
For Credit Card Service $ 650 $ 420 $ 900 $ 870 $ 850 $ 700
For Bank Charges $ 30 $ 30 $ 30 $ 30 $ 30 $ 30
For Miscellaneous $ 200 $ 200 $ 200 $ 200 $ 200 $ 200

 

Now Joan has enough information to prepare a forecasted cash flow statement for her business during the periods between July 1, 200X and December 31, 200X. The forecasted cash flow statement will indicate whether or not Joan requires financing and, if so, when she will need it. Below illustrates Joan's Forecasted Cash Flow Statement for the months of July through to the end of December.

THE DIRECT SHOE COMPANY
FORECASTED CASH FLOW STATEMENT
FOR THE MONTH JULY 1 THROUGH DECEMBER 31, 200X


JULY AUG. SEPT. OCT. NOV. DEC. TOTALS
CASH INFLOWS:
Forecasted Sales Per Month $18,900 $24,150 $25,200 $13,650 $10,500 $12,600 $105,000
From Customers in Current Month (80%) $15,120 $19,320 $20,160 $10,920 $ 8,400 $10,080 $ 84,000
From Customers from First Month (20%)
$ 3,780 $ 4,830 $ 5,040 $ 2,730 $ 2,100 $ 18,480
From Short-term Loan from Family $ 2,500 $ 0 $ 0 $ 0 $ 0 $ 0 $ 2,500
Cash at Beginning of Month $ 6,280 $ 6,810 $14,820 $22,430 $20,940 $19,350 $ 86,350

TOTAL CASH INFLOWS (A) $23,900 $29,910 $39,810 $38,390 $32,070 $31,530

CASH OUTFLOWS:
For Finished Product Purchases $ 0 $9,675 $5,975 $3,525 $2,575 $2,950 $24,700
For Promotions $9,000 $1,003 $6,133 $8,533 $4,663 $4,003 $33,335
For One Time Franchise Fee $6,500 $ 0 $ 0 $ 0 $ 0 $ 0 $ 6,500
For Office Salaries Expense $1,300 $1,300 $1,300 $1,300 $1,300 $1,300 $ 7,800
For Employer Costs (11% of Salary) $ 143 $ 143 $ 143 $ 143 $ 143 $ 143 $ 858
For Office Supplies $1,200 $ 0 $ 0 $ 0 $ 0 $ 0 $ 1,200
For Business Cards & Correspondence $ 325 $ 0 $ 0 $ 0 $ 0 $ 0 $ 250
For Telephone $ 200 $ 100 $ 130 $ 120 $ 100 $ 100 $ 750
For Business Registration & Fees $ 283 $ 0 $ 0 $ 0 $ 0 $ 0 $ 283
For Message Center $ 850 $ 440 $ 700 $ 800 $ 850 $ 700 $ 4,340
For Toll Free Services $ 590 $ 460 $ 550 $ 610 $ 690 $ 600 $ 3,500
For Credit Card Service $ 650 $ 420 $ 900 $ 870 $ 850 $ 700 $ 4,390
For Bank Charges $ 30 $ 30 $ 30 $ 30 $ 30 $ 30 $ 180
For Miscellaneous $ 200 $ 200 $ 200 $ 200 $ 200 $ 200 $ 1,200
For Income Taxes $ 0 $ 0 $ 0 $ 0 $ 0 $ 0 $ 0
For Withdraws $1,000 $1,000 $1,000 $1,000 $1,000 $1,000 $ 6,000
For Equipment Purchased $9,500 $ 0 $ 0 $ 0 $ 0 $ 0 $ 9,500

TOTAL CASH OUTFLOWS (B) $31,771 $14,771 $17,061 $17,131 $12,401 $11,726

CASH EXCESS (Deficit) (A-B) ($7,871) $15,139 $22,749 $21,259 $19,669 $19,804

FINANCING REQUIRED:
Bank Loan 5 year, 10% $15,000 $ 0 $ 0 $ 0 $ 0 $ 0 $15,000
Interest & Principal Payment on loan $ (319) $ (319) $ (319) $ (319) $ (319) $ (319) $ 1,914

NET EFFECTS FINANCING (C) $14,681 ($319) ($319) ($319) ($319) ($319)

ENDING CASH BALANCE (A-B+C) $6,810 $14,820 $22,430 $20,940 $19,350 $19,485
 
Note: numbers are rounded!!!

As you can see, the company will have a cash deficiency of $7,871 in July. This means, in July, more cash will be leaving the company than entering the company. As a result, Joan will not have enough money to pay her July bills. The only option is to obtain financing.

The forecasted cash flow statement assumes Joan will apply for and receive a $15,000 bank loan. This loan will provide the necessary financing for her business. She also assumes that her interest and principal payments on the loan amounts to $319 per month.

 

FINANCING REQUIRED: JULY AUG. SEPT. OCT. NOV. DEC. TOTALS
Bank Loan 5 year, 10% $15,000 $ 0 $ 0 $ 0 $ 0 $ 0 $15,000
Interest & Principal Payment on loan $ (319) $ (319) $ (319) $ (319) $ (319) $ (319) $ 1,914

NET EFFECTS FINANCING (C) $14,681 ($319) ($319) ($319) ($319) ($319)  

 

 

As a result, the $15,000 loan will act as a cash inflow and the monthly loan payment of $319 will act as a cash outflow. Furthermore, in July the loan payment is subtracted from the loan amount to arrive at Joan's Net Effect of Financing ($14,681). Since no further financing is required after July, the Net Effect of Financing each month will be a negative $319. This amount will act as a cash outflow, thus reducing the company's monthly cash.

Below summarizes Joan's monthly ending cash balances.

 

  JULY AUG. SEPT. OCT. NOV. DEC.
ENDING CASH BALANCE (A-B+C) $6,810 $14,820 $22,430 $20,940 $19,350 $19,485

 

 

Since each month's forecasted ending cash balance shows a positive amount, Joan will not require any additional financing above the $15,000 bank loan. Please note, the ending cash balance in any month becomes the beginning cash balance for the following month. For example, July's ending cash balance becomes August's beginning cash balance and August's ending cash balance becomes September's beginning cash balance and so on... (See beginning cash balances under Joan's cash inflows section).

 

Categories: Financial