Please explain how you do it on excel as well (Thank you! I will upvote if answer is complete and detailed)

Case Study Question:

Prepare the sales budget, production budget, direct material, labour budget, manufacturing overhead budget, selling and administrative budget for each quarter of the year 2022 and prepare a budgeted income statement for the year 2022. Using the information provided in the picture.

Taylor Family Case Study Anne Taylor is preparing the 2022 budget for one of Second designs yacht. Extensive meetings with members of the sales department and executive management have resulted in the following unit sales projections for 2022. For quarter 1 the projected sales are 1000 yacht and each quarter the number increases by 10% from the previous quarter. They expect to sell each yacht for $100 in quarter one and a 20% increase in each quarter to previous after that. Second designs policy is to have finished goods inventory in a quarter equal to 20% of the next quarters anticipated Sales. Preliminary Sales projections for 2023 are 500 yachts for the first quarter and 600 yachts for the second quarter. Ending finished goods at December 2021 are 300 yachts. Production of each yacht requires 50 pounds of actetic powder and one finishing kit (seat, engine, metal etc). Company policy is that the ending inventory for actetic should be 25% of the amount needed in production for the next quarter. Assume that the ending inventory of actetic in December 2022 is 12000 pounds and for the first quarter in 2023 is 14000 pounds. The finishing kit can be assembled as they are needed based on production. As a result Roberts family does not maintain a significant inventory (opening and closing) of the finishing kits. The actetic powder used in the production of the yacht cost $1.50 per pound and the finishing kit costs $170 each. Production of a single yacht required 3 hours of work by employees. Employees are paid $15 per hour. Selling and administrative are expected to be $45 per unit sold plus $7500 per quarter. Manufacturing overhead is assigned at 150% of labour costs and additional fixed costs of $500 for depreciation and $300 for maintenance costs each quarter. Interest per quarter are expected to be $200 and taxes $400 per quarter. Overheads are measured by direct labour hour. Requirements Prepare the sales budget, production budget, direct material budget, labour budget, manufacturing overhead budget, selling and administrative budget for each quarter for the year 2022 and prepare a Budgeted Income Statement for the year 2022. Your answer to be completed on an Excel sheet using formulas rather than a calculator Your answer (Excel File) must be uploaded on Blackboard by gh December 11.59pm. Any figures can be rounded up if needed