Budgeting

You are required to prepare cash budget for the two months (June and July). The task requires you to use excel in formulating the cash budget by performing all the required steps.

Question

As the management accountant of Wax Pty Ltd, you are asked by your manager to prepare the Cash budget for the next two months (June & July). You have been presented with the following information.

The cash balance at the end of May was $25881.

Sales for March, April and May were 4500, 3450 and 3170 units respectively at $30 per unit

The business estimates that their sales for the next two months will be as follows, with all sales per unit as the last month.

June 3100 units

July3450 units

Based on the previous experience of the business, sales are expected to be 35% cash in the month of sale.

Credit sales are expected to be collected 45% in the month of sale, 20% one month after, with the balance being collected two months after the sale.

Dividend payable in July $3,500

Income tax payable in June $850

The April and May inventory purchases were $40,500 and $40,900 respectively. All purchases of inventory are made on credit and are paid one month after the purchase. The following purchases are expected.

June $40,000

July $41,000

It is expected that the following monthly operating expenses will be incurred:

Salaries $8,500

Depreciation $1,500

Advertising $2,400

Electricity $2,050

All relevant operating expenses will be paid in cash in the month incurred

There will also be additional motor vehicle expenses of $1,500 to be paid in July

The company is obligated to make a debt repayment in June $ 6000

The business intends to renovate its showroom at a cost of $350,000. A deposit of 25% will be required in June, with 60% of the balance to be paid the following month, with the remainder two months later.

Required:

1.Prepare a schedule of Accounts receivables to calculate estimated cash collected from accounts receivables for the months of June and July

2.Prepare a cash budget for the months of June and July

3.Comment on the budgeted cash position for Wax Pty Ltd at the end of July and suggest three steps management could take to address the situation.