Bay State University was recently bequeathed a company (Pearl Inc) by an alumnus on condition that profits from the company be used for student scholarships. The terms of the bequest prohibit the university from selling the company. You have been asked to prepare some budgets for the company based on the following information:

Pearl Inc. produces two products, Shelland Oyster.

Sales	Shell	Oyster
Projected sales for July 2024 (in units)	6,000	8,000
Sales price (per unit)	$11.50	$7.75
Variable Costs - product
Materials (per unit)	$4.15	$2.85
Labor (per unit)	$3.20	$1.65

Variable Costs - Selling and Admin

Commission $0.75 $0.50

Fixed Overhead Costs - Product $

Production overheads 21,000 per quarter, paid monthly

Rent - factory 24,000 per year, paid monthly

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

Fixed Costs - Selling and Admin $

Sales salaries 15,000 per quarter, paid monthly

Rent - office 18,000 per year, paid 1^st month of each quarter

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

Notes

1. Sales and production are projected to increase by 20% per month.
2. 80% of Pearl's 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.
3. Bad debts of 5% are anticipated on credit sales only.
4. 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.
5. Accounts receivable at 30 June 2024 will amount to $30,000. This is the gross figure and does NOT include any potential bad debts.
6. Accounts payable at 30 June 2024 will amount to $100,000 all of which relates to materials - 45% of this balance is payable in July and the remainder in Aug 2024.
7. The company is expecting to receive a tax refund of $11,500 in July 2024.
8. The figure detailed above for 'Other' fixed overhead costs and 'Other' fixed selling and admin costs include $500 and $350 per month for depreciation on equipment, respectively.
9. The bank account balance at 30 June 2024 will be $10,000 -overdrawn.
10. Interest at a rate of 9.5% per year is charged by the bank on overdrafts and is payable monthly. The amount payable in any month is based on the balance at the end of the prior month.
11. The company will need to replace a piece of machinery in August - the new machinery will cost $40,000. The depreciation figure will NOT be impacted by this purchase. If the equipment is not replaced, additional repairs of $5,400 will be required.

Required:

1. Using excel, I need to prepare the cash budget for Pearl Inc. for each of the months of July, Aug and Sept 2024 which details inflows, outflows and the expected bank balance at each month end.

***I am really struggling with the cash inflow section with the cash, credit sales, cash discount, bad debt, and prior months account receivable. I need help getting that part set up correctly***