Reference no: EM132662362

You are required to make 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.

Assignment 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.

1. The cash balance at the end of May was $25xxx (the xxx will be the last 3 digits of your ID number)
2. Sales for March, April and May were 4500, 3450 and 3170 units respectively at $30 per unit
3. 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
July 3450 units

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

1. 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
2. Dividend payable in July $3,500
3. Income tax payable in June $850
4. 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

1. There will also be additional motor vehicle expenses of $1,500 to be paid in July
2. The company is obligated to make a debt repayment in June $ 6000
3. 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:

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

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

Problem 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.