Group Project Cash Budget Pearl Inc produces two products, Shell and Oyster The following information has been supplied for the purposes of preparing a cash budget Sales Shell Oyster Projected sales for the 1 st half of 2022 (in units) 36,000 48,000 Sales price (per unit) $9 50 $6 75 Variable Costs Materials (per unit) $4 15 $2 85 Labor (per unit) $3 20 $1 65 Fixed Overhead Costs $ Production overheads 21,000 per quarter, paid monthly Rent 24,000 per year, paid monthly Other 2,500 per month, paid monthly (where relevant) Notes Sales and production are projected to occur evenly over the 6 month period 80 of Pearls customers pay for their purchases immediately i e when a sale occurs, and a 2 5 cash discount is given to these customers The remainder are on credit terms of one month Bad debts of 5 are anticipated on credit sales only Credit terms with the suppliers of materials are 2 months , all other expenses, where payable, are paid in the month incurred unless indicated otherwise above Accounts receivable at 31 December 2021 amounted to $30,000 This amount does NOT include any potential bad debts Accounts payable at 31 December 2021 amounted to $100,000 all of which related to materials 65 of this balance is payable in January and the remainder in February 2022 The company is expecting to receive a tax refund of $11,500 in February 2022 The figure detailed above for Other overhead costs includes $500 per month in respect of depreciation on equipment The bank account balance at the start of the year i e 2022, was overdrawn by $10,000 The company would like to replace a piece of machinery in February the new machinery would cost $20,000 The depreciation figure will NOT be impacted by this purchase Required Using excel prepare a cash budget for Pearl Inc for each of the first three months of 2022 which details inflows, outflows and the expected bank balance at each month end

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jul 22, 2024

Group Project - Cash Budget

Pearl Inc. produces two products, Shell and Oyster. The following information has been supplied for the purposes of preparing a cash budget:-


Sales	Shell		Oyster
Projected sales *for the 1^st half of 2022* (in units)	36,000		48,000
Sales price (per unit)	$9.50		$6.75
Variable Costs
Materials (per unit)	$4.15		$2.85
Labor (per unit)		$3.20		$1.65

Fixed Overhead Costs $

Production overheads 21,000 per quarter, paid monthly

Rent 24,000 per year, paid monthly

Other 2,500 per month, paid monthly (where relevant)

Notes

Sales and production are projected to occur evenly over the 6-month period.
80% of Pearls customers pay for their purchases immediately i.e. when a sale occurs, and a 2.5% cash discount is given to these customers. The remainder are on credit terms of one month.
Bad debts of 5% are anticipated on credit sales only.
Credit terms with the suppliers of materials are 2 months, all other expenses, where payable, are paid in the month incurred unless indicated otherwise above.
Accounts receivable at 31 December 2021 amounted to $30,000. This amount does NOT include any potential bad debts.
Accounts payable at 31 December 2021 amounted to $100,000 all of which related to materials 65% of this balance is payable in January and the remainder in February 2022.
The company is expecting to receive a tax refund of $11,500 in February 2022.
The figure detailed above for Other overhead costs includes $500 per month in respect of depreciation on equipment.
The bank account balance at the start of the year i.e. 2022, was overdrawn by $10,000.
The company would like to replace a piece of machinery in February the new machinery would cost $20,000. The depreciation figure will NOT be impacted by this purchase.

Required:

Using excel prepare a cash budget for Pearl Inc. for each of the first three months of 2022 which details inflows, outflows and the expected bank balance at each month end.