Hello I need your help in completing the following assignment on Excel: It is a budgeting assignment (Accounting)

Master Budget Case Purple Mango Inc Purple Mango Inc. is a toy company that manufactures and sells a single product, the kadoodle. For planning and control purposes they utilize a quarterly master budget, which is usually developed at least six months in advance of the budget year. Their fiscal year end is December 31. Early in 2021, Katrina Kyle, the Purple Mango controller, spent some time putting together a sales forecast for the next budget year (January to December, 2022). In April, however, Katrina decided to follow her dream and sail around the world. She left Purple Mango with minimal notice. Susan Alexander, the president of Purple Mango, desperately needing the budget completed, has approached you, a management accounting student, for help in preparing the budget for the coming fiscal year. Your conversations with Susan and your investigations of the company's records have revealed the following information: 1. Katrina's sales forecast consisted of these few lines: For the year ended Dec. 31, 2021: 125,000 units at $19.00 each For the year ended Dec. 31, 2022: 140,000 units at $19.50 each For the year ended Dec. 31, 2023: 145,000 units at $20.00 each 2. Because the product is a toy, sales are very seasonal, with 65% of sales taking place in the last quarter of their fiscal year (Oct-Dec) as their customers, primarily retail stores, ramp up for the holiday season. 5% of sales take place in their first quarter, 10% in the second quarter, and 20% in their third quarter. 3. Because sales are seasonal, Purple Mango must rent an additional storage facility from August to November to house the additional finished toys on hand. The only related cost is a flat $1,500 per month, payable at the beginning of the month. 4. Sales are on a cash and credit basis, with 85% collected during the quarter of the sale, and 15% the following quarter. 5. From previous experience, management has determined that an ending finished goods inventory equal to 10% of the next quarter's sales is required to meet customer demand. Opening finished goods inventory consists of 700 kadoodles. 6. The primary raw material used is a newly designed plastic. Each kadoodle requires 250 g (.25 kg) of plastic (including a wastage allowance), which the company purchases for $30.00 per kg. Purple Mango finds it necessary to maintain an inventory balance equal to 15% of the following quarter's production needs of plastic as a precaution against stock-outs. Purple Mango pays for 80% of a quarter's purchases in the quarter of purchase and 20% in the following quarter. 7. Much of the manufacturing process is done by hand. Employees are paid an average of $27 per hour, including the employer's portion of employee benefits. All payroll costs are paid in the period in which they are incurred. Each kadoodle spends a total of 15 minutes in production. 8. Due to the company's concentration on a single product, manufacturing overhead is allocated based on volume (i.e. the units produced). The variable overhead manufacturing rate is $1.25 per unit. 9. The fixed manufacturing overhead costs for the entire year are as follows: Supervisor's salary 98,400 Depreciation on equipment 80,000 Insurance 28,000 Other 15,000 $221,400 e The annual insurance premium is paid at the beginning of October each year. e All other \"cash-related\" fixed manufacturing overhead costs are incurred evenly over the year and paid as incurred. e Purple Mango uses the straight line method of depreciation. 10. Selling and administrative expenses are known to be a mixed cost; however, there is a lot of uncertainty about the portion that is fixed. The previous year's experience has provided the following information: Unit Sales Selling and Admin Cost Q1 6,250 16,500 Q2 12,500 29,000 Q3 25,000 54,000 Q4 81,250 166,500 These costs are paid in the quarter in which they occur. 11.During the fiscal year ended Dec. 31, 2022, Purple Mango will be required to make quarterly income tax installment payments of $12,000. Outstanding income taxes from the year ended Dec. 31, 2021 must be paid in April, 2022. Income tax expense is estimated to be 25% of net income. Income taxes for the year ended December 31, 2022, in excess of installment payments, will be paid in April, 2023. 12.Purple Mango is planning to acquire additional manufacturing equipment for $148,300 cash. 60% of this amount is to be paid in March, 2022, the rest, in April, 2022. The manufacturing overhead costs shown above already include the depreciation on this equipment. 13.Purple Mango Inc. has a policy of paying dividends at the end of each quarter. The president tells you that the board of directors is planning on continuing their policy of declaring dividends of $10,000 per quarter. 14.Purple Mango has negotiated with the bank for a line of credit at a rate of 9% per annum (i.e. 2.25% per quarter). Amounts can be borrowed or repaid in increments of $1,000. Amounts are assumed to be borrowed at the beginning of the quarter when needed and repaid at the end of the quarter when cash is available. A minimum cash balance of $20,000 must be maintained. 15. listing of the estimated balances in the company's ledger accounts as of December 31, 2021. Cash $20,345 Accounts receivable 231,563 Inventory-raw materials 8,663 Inventory-finished goods 11,900 Prepaid insurance 14,000 Capital assets (net) 1,050,000 $1,336.471 Accounts payable $ 95,859 Income tax payable 26,200 Capital stock 800,000 Retained earnings 414.411 $1.336.471 Required: Prepare a quarterly master budget for Purple Mango for the year ended December 31, 2022, including the following schedules: Sales Budget & Schedule of Cash Receipts Production Budget Direct Materials Budget & Schedule of Cash Disbursements Direct Labour Budget Manufacturing Overhead Budget selling and Administrative Expense Budget Cash Budget Students are required to prepare a quarterly master budget for the year ended Dec. 31, 2022 based on the information posted. The budget must be prepared in Microsoft Office Excel with the following criteria: 1: 2. J. For ease of formatting, use one worksheet for each schedule Prepare the schedules with the quarters going across the worksheet, not down. You must also have a Total column at the end to show the year's budget results. Prepare a data page detailing the information, to be used as reference other worksheets (optional). Link as many of the numbers as possible using formulas. Very few items on the schedules should be hard coded on the schedule. Every number on the cash budget should be referenced from a supporting page, or a sum on the page. The objective is to be able to perform sensitivity analysis i.e. be able to determine the impact on ending cash and income of a $1 increase in selling price, for example, by entering the change on the data page and having everything else update automatically. Use Excel calculation tools as much as possible. If the headings (e.g. titles or the months) are set up on the first worksheet, they can be copied to all the other worksheets to save time. Proper formatting should be used so the budgets will look professional. Look under the word \"format\" in the toolbar to find the correct way to underline accounting numbers (single and double). Set Excel to record negative numbers within parenthesis e.g. (1,000), not -1,000 Round all numbers to the nearest dollar, with the exception of per unit amounts