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 a 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.
2. I need to prepare a budgeted income statement for the 2^nd half of 2024, based on the information provided and assuming that no inventory is held.
3. Detail in a memo
   • any issues that may be relevant to the company with respect to the budgets you have prepared and
   • how much money the company is expected to receive for each $1,000 of sales it makes - based on the information provided in note 2. above.
4. On a separate workbook within Excel I nee to prepare the cash budget for Pearl for each of the 3 months based on the information provided above but on the basis that there is a 10% increase in sales per month instead of 20%.

Please include all workings on your spreadsheet and use formula, where appropriate. If one figure on the spreadsheet were to change e.g., 80% re customers paying immediately to 75%, the expectation is that everything else will change in your spreadsheet such as credit sales, bad debt amount, cash received etc. The requirement at d) above tests this.