You are an accountant at Hostell Homewares Inc. The controller wants you to prepare a monthly cash budget for the next three months, based on the following budget information. September October November
Sales $300,000 $350,000 $325,000
Manufacturing costs $175,000 $200,000 $210,000
Selling and administrative expenses $50,000 $50,000 $55,000
Capital expenditures $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 receivable $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: Using formulas/functions wherever available, complete the following on the Input tab…
Prepare a monthly cash budget and supporting schedules for September, October, and November.
On the basis of the cash budget prepared in Part 1, what feedback would you give the controller?