Please use excel and show formulas. This is for studying in depth. Thank you :)

You will need to create a new production schedule for 2022 using level production the sales forecast given below. Thrusters has a beginning inventory of 5 units and would like an ending inventory of 9 units. Forecast sales in boards are given below. Your schedules and statements should link to the assumptions. I should be able to change values in your assumptions and have the schedules and statements update. The balance sheet for the beginning of the year is given below: Additional Information - The surfboards sell for $1,000 each. 20% of the sales are cash sales and the remainder are accounts receivable paid the following month. - The cost per board is $600 these costs are all paid the month following production - Fixed costs are $144,000 paid in equal amounts each month. You may use these as selling and admin costs in your template - Taxes are 20\% you may assume the amount calculated on your income statement is paid in April - The corporation pays a 25\% dividend in May - The company would like to maintain a minimum cash balance of $5,000 and will borrow on its line of credit when necessary and repay the line of credit as soon as they are able Required 1. Complete the income statement and calculate the amount to be transferred to retained earnings. 2. Complete the schedules of collections and payments 3. Complete the cash budget and proforma balance sheet. Bonus - use a formula to calculate the required borrowing and repayments. ASSUMPTION PRODUCTION SCHEDULE AND INVENTORY INCOME STATEMENT COLLECTIONS PAYMENTS CASH BUDGETS BALANCE SHEET You will need to create a new production schedule for 2022 using level production the sales forecast given below. Thrusters has a beginning inventory of 5 units and would like an ending inventory of 9 units. Forecast sales in boards are given below. Your schedules and statements should link to the assumptions. I should be able to change values in your assumptions and have the schedules and statements update. The balance sheet for the beginning of the year is given below: Additional Information - The surfboards sell for $1,000 each. 20% of the sales are cash sales and the remainder are accounts receivable paid the following month. - The cost per board is $600 these costs are all paid the month following production - Fixed costs are $144,000 paid in equal amounts each month. You may use these as selling and admin costs in your template - Taxes are 20\% you may assume the amount calculated on your income statement is paid in April - The corporation pays a 25\% dividend in May - The company would like to maintain a minimum cash balance of $5,000 and will borrow on its line of credit when necessary and repay the line of credit as soon as they are able Required 1. Complete the income statement and calculate the amount to be transferred to retained earnings. 2. Complete the schedules of collections and payments 3. Complete the cash budget and proforma balance sheet. Bonus - use a formula to calculate the required borrowing and repayments. ASSUMPTION PRODUCTION SCHEDULE AND INVENTORY INCOME STATEMENT COLLECTIONS PAYMENTS CASH BUDGETS BALANCE SHEET