Can you give me the step by step answers on how to compute this: An excel spreadsheet would be amazing...
Problem
The treasurer of Bradly Lawn Equipment Company instructs you to prepare a monthly cash budget for the next three months. You are presented with the following budget information:
August September October
Sales $310,000 $390,000 $366,000
Manufacturing costs 190,000 240,000 215,000
Operating expenses 56,000 71,000 65,000
Capital expenditures 24,000
The company expects to sell about 20% of its merchandise for cash. Of sales on account, 70% are expected to be collected in full in the month following the sale and the remainder the following month. Depreciation, insurance, and property taxes represent $20,000 of the estimated monthly manufacturing costs and $4,000 of the probable monthly operating expenses.¬ The annual insurance premium is paid in July, and the annual property taxes are paid in November. Of the remainder of the manufacturing costs and operating expenses, 80% are expected to be paid in the month in which they are incurred and the balance in the following month.
Current assets as of August 1 comprise cash of $32,000, marketable securities of $40,000, and accounts receivable of $261,800 ($220,000 from July sales and $41,800 from June sales). Current liabilities as of August 1 comprise a $60,000, 12%, 60 day note payable due Septem¬ber 20, $30,000 of accounts payable incurred in July for manufacturing costs, and accrued liabilities of $9,500 incurred in July for operating expenses.
It is expected that $1 500 in dividends will be received in August. An estimated income tax payment of $21,000 will be made in September. Bradly's regular quarterly dividend of $10,000 is expected to be declared in September and paid in October. Management desires to maintain a minimum cash balance of $25,000.
Instructions
1. Prepare a monthly cash budget for August, September, and October.
2. On the basis of the cash budget prepared in (1), what recommendation should be made to the treasurer?