Company A in its 6th year of operations. The company is experiencing cash flow issues and would like to utilize a budget to forecast cash flows. You are an outside consultant hired to assist in designing a model that Company A can utilize on an ongoing basis to forecast cash flow.

The CEO of the company, has predicted an increase in sales of two percent per month from December 2010 through March 2011. The CEO believes that after March, sales will remain steady through June 2011. The projected balance sheet for Compnay A as of December 31, 2011 is shown below:

Cash $ 20,000

A/receivable 208,748

Marketable securities 25,000

Inventory 25,750 Buildings & equipment (net of accumulated depreciation) 225,000

Total assets $ 504,498

A/payable $ 101,384

Sales commissions payable 6,885

Bond interest payable 4,800

Property taxes payable 0

Bonds payable (4%- due in 2020)

360,000 Common stock 10,000

Retained earnings 21,429

Total liabilities and stockholders' equity $ 504,498

You will need the following information to prepare a master budget for the first quarter of 2011:

1) Projected sales for November 2011 are $225,000. Credit sales comprise 95% of total sales. Sales collected in the month of sale total 15%, in the month following the month of sale total 70%, and in the second month following the month of sale total 13%. Remaining sales are uncollectible.

2) Cost of goods sold total 55% of sales. Inventory purchases are made on account. Purchases paid for in the month of purchase total 20% and purchases paid for in the month following the month of purchase total 80%. Ending monthly Inventory is maintained at 20% of the following months projected cost of goods.

3) Projected monthly expenses include: Sales salaries $ 60,000 Advertising and promotion 4,000 Administrative salaries 25,000 Depreciation 3,000 Interest on bonds 1,200 Property taxes 1,500 Sales commissions of 3.0 percent of sales are paid in the month following the month of sale.

4) Additional manufacturing equipment would allow the company to increase production. The cost of the new equipment is $150,000. The CEO plans on purchasing the equipment on January 1st and plans on paying for the equipment with cash and by liquidating the companys marketable securities. The company wishes to maintain a minimum cash balance of $15,000 at the end of each month. If necessary, he plans on obtaining a short-term note payable from a local bank. The minimum lending period for such a loan is three months. The current short-term interest rate is 7 percent per year and is expected to remain at this rate through the time the equipment is purchased. The company would like to pay off the loan as quickly as possible. Payments must be made in $1,000 increments.

5) The Company would like to declare and pay dividends of $125,000 on the last day of each quarter.

6) The interest on any short-term borrowing will be paid when the loan is repaid. Interest on the bond payable is paid semiannually on February 28 and August 31 for the preceding six-month period.

7) Property taxes are paid quarterly on March 31, June 30, September 30, and December 31 for the preceding three-month period.

Required: Assist them by building a model to forecast cash flow. Set up your model for the first quarter of 2015. The model should contain the master budget schedules shown below. Round all amounts to the nearest dollar. The model should allow Compnay A to change the assumptions provided above and quickly recalculate the forecasted cash balance at March 31, 2011. The assumptions may be included on a separate worksheet in your Excel file but all of the schedules below must be on one worksheet.

1) Sales budget:

2010 2011

November December January February March 1st Quarter

Total sales

Cash sales

Sales on account

2) Cash receipts budget:

2011

January February March 1st Quarter

Cash sales

Cash collections from credit sales made during current month

Cash collections from credit sales made during preceding month

Cash collections from credit sales made during 2nd preceding month

Total cash receipts

3) Purchases budget:

2010 2011

December January February March 1st Quarter

Budgeted cost of goods sold

Add: Desired ending inventory T

otal goods needed

Less: Expected beginning inventory

Purchases

4) Cash disbursements budget:

2010

January February March 1st Quarter

Inventory purchases:

Cash payments for purchases during the current month

Cash payments for purchases during the preceding month

Total cash payments for inventory purchases

Other expenses:

Sales salaries

Advertising and promotion

Administrative salaries

Interest on bonds

Property taxes

Sales commissions

Total cash payments for other expenses

Total cash disbursements

5) Summary cash budget:

2011

January February March 1st Quarter

Cash receipts (sch 2)

Less: Cash disbursements (sch 4)

Change in cash balance during period due to operations

Sale of marketable securities (1/1/11)

Proceeds from bank loan (1/1/11)

Purchase of equipment Repayment of bank loan (3/31/11)

Interest on bank loan

Payment of dividends

Change in cash balance during the month

Beginning cash balance

Ending cash balance