Cash Budget

You are an accountant at Hostell Homewares Inc.The controller wants you to prepare monthly cash budget for the next three months, based on the following budget information.

Sales

September $300,000

October $350,000

November $350,000

Manufacturing costs

September $175,000

October $200,000

November $210,000

Selling and administrative expenses

September $50,000

October $50,000

November $55,000

Capital expenditures

November $200,000

The company expects to sell about 10% of its merchandise for cash.Of sales on account, 65% are expected to be collected in the month following the sale and the remainder the following month (second month following the sale).Depreciation, insurance and property tax expense represent $50,000 of the estimated monthly manufacturing costs. The annual insurance premium is paid in January, and the annual property taxes are paid in December.Of the remainder of the manufacturing costs, 75% are expected to be paid in the month in which they are incurred and the balance in the following month.

Additionally, the following account balances as of September 1....

Current Assets

Cash $50,000

Marketable securities $75,000

Accounts receivables $327,000

*July portion of A/R $77,000

*August portion of A/R $250,000

Current liabilities

Accounts payable $50,000

Other items of note....

Sales on account for:

July $220,000

August $250,000

Selling and administrative costs are paid in the period incurred.

Estimated income tax payment to be made in October $60,000

Regular quarterly dividend declared in October $30,000

Regular quarterly dividend paid in November $30,000

Lastly, Scott's management desires a minimum monthly cash balance of $50,000.

Instructions:Usingformulas/functions wherever available In Excel, complete the following

1. Prepare the monthly cash budget and supporting schedules for September, October, and November in Excel