GUILLERMO FURNITURE

Guillermo Furniture Store Analysis

Cash Flow Sensitivity Analysis

Guillermo Furniture Store Analysis

October 12, 2011

% Change in receipts and disbursements

5.0%

Expected

Pessimistic

Optimistic

Beginning Cash Balance

 

$20,000

$20,000

$20,000

Cash Inflows (Income):

 

 

 

 

 

Accounts Receivable Collections

30,000

28,500

31,500

 

Loan Proceeds

20,000

19,000

21,000

 

Sales & Receipts

10,000

9,500

10,500

 

Other:

 

 

 

 

Total Cash Inflows

$60,000

$57,000

$63,000

Available Cash Balance

$80,000

$77,000

$83,000

Cash Outflows (Expenses):

 

 

 

 

Advertising

$15,000

$14,250

$15,750

 

Bank Service Charges

1,000

950

1,050

 

Contingencies

3,000

2,850

3,150

 

Credit Card Fees

4,000

3,800

4,200

 

Professional Fees

 

 

 

 

Utilities & Telephone

1,000

950

1,050

 

Other:

Subtotal

$24,000

$22,800

$25,200

Other Cash Out Flows:

 

 

 

 

Capital Purchases

$100,000

$95,000

$105,000 Building Construction

 

 

 

 

Decorating

 

 

 

 

Fixtures & Equipment

 

 

 

 

Loan Principal

 

 

 

 

Owner's Draw

$1,000

$950

$1,050

 

Other:

 

 

 

 

 

 

 

 

 

Subtotal

$101,000

$95,950

$106,050 Total Cash Outflows

$125,000

$118,750

$131,250

Ending Cash Balance

 

($45,000)

($41,750)

($48,250)



The sensitivity analysis shows that the expected budget for Guillermo Furniture is to have 20,000 $ cash at the beginning of the year. The inflows are allocated in the different heads, and shows that from which sources the cash is coming in the organization. After this, it also contains the different heads which contains the outflows for the particular year for Guillermo Furniture.

The optimistic approach suggests that the inflows of cash are more then compare to the pessimistic approach, just like the inflows the outflows in pessimistic approach are more, and the optimist approach contains less out flows than the expected budget.

Net present value

Guillermo Furniture Store Analysis

Project 1

Year

0

1

2

3

4

5

Incremental Earnings Forecast

Sales of G. Furniture

$0

$23,244,750

$23,500,000

$23,500,000

$23,500,000

$0

Cost of Goods Sold

$0

$9,500,000

$9,500,000

$9,500,000

$9,500,000

$0

Gross Profit

$0

$12,918,250

$14,000,000

$14,000,000

$14,000,000

$0

Selling, General, and Admin.

$0

$3,000,000

$3,000,000

$3,000,000

$3,000,000

$0

Research and Development

$16,500,000

$0

$0

$0

$0

$0

Depreciation

$0

$1,500,000

$1,500,000

$1,500,000

$1,500,000

$1,500,000

EBIT

($16,500,000)

$8,418,250

$9,500,000

$9,467,900

$9,500,000

($1,500,000)

Income Tax

($6,600,000)

$3,367,300

$3,837,527

$3,787,160

$4,766,386

($600,000)

Unlevered Net Income

($9,900,000)

$5,050,950

$5,662,473

$5,680,740

$4,733,614

($900,000)

Free Cash Flow

Plus: Depreciation

$0

$1,500,000

$1,725,000

$1,500,000

$1,500,000

$1,500,000

Less: Capital Expenditures

($7,500,000)

$0

$0

$0

$0

$0

Less: Increase in ...