M & M plc Case Study

The following budget data relates to the firm of M & M plc for the period July to December Year 2.
September, October, November, December: July, August
5,600 units sold 6,000 6,400 6,100 6,200 6,300
Units produced: 6,000, 6,500, 6,200, 6,300, 5,900, 6,100
There is also the following details available:
1. The balance of cash and cash equivalents on September 1 is anticipated to be £9,500.
2. The unit’s retail selling price is £50.
80% of all credit sales are paid for one month after the sale, and the remaining credit sales are paid for two months after the sale, but 5% of the amounts due from these customers are anticipated to be bad debts. Credit sales account for 75% of all sales; these are sold to trade customers who receive a 10% discount on the retail selling price provided they pay within one month.
3. All sales must be accompanied by a £2 per unit payment for sales expenses.
4. The following are the unit costs:
Material costs are £25 per unit, due in the month before to production; labor costs are £11 per unit, due in the month of production; and labor bonuses are paid at the rate of £3 per unit for units produced in excess of 6,300.
The month following the output will be used to pay for this.
5. Monthly Fixed Overheads, excluding Depreciation of $1,000, are £4,000.
6. A new piece of equipment will be purchased and paid for with £120,000 in September. In September, a loan of £80,000 will be obtained from the bank to aid in financing this. Starting in October, the loan will be returned in 10 equal monthly installments, with 5% interest added to each payment.
(a) Create the cash budget for the second year from September to November.
(a) Describe two benefits of creating a cash budget on a spreadsheet for a management accountant.