FIN1212 Task 4. Instruction Use excel spreadsheet to solve the task the below. You then need to: 1. Upload your excel spreadsheet in the submission dropbox (5 marks) 2. Input the answers to the 20 questions listed at the end of task in quiz link. ( An excel template guide for question 1 and 4 has been provided for you) TASK William College Ltd provides day courses for businesses. During 2022, the courses were offered at 80 each and the variable cost was 50. The profit for the year was 300,000, after allowing fixed costs of 240,000. In 2023 , it is anticipated that the variable cost per course will increase by 4, due to an increase in lecturer's remuneration. Fixed costs are also expected to increase by 20,000. The management has heard that its main competitor, Kate College Ltd, is likely to increase the price of its courses in 2023. As demand for these business courses is always very high, the management believes that 2023 would be a good time to increase the price of William College Ltd's course fee too. The following proposals are being considered: - (i) Increase the price of course fee to 90 and demand is expected to increase by 5%. (ii) Increase the price of course fee by 6 and demand is anticipated to increase by 20%. 1. Prepare a contribution margin statement for each proposal. Contribution margin statement sample Sales Less Variable costs Contribution Less Fixed Costs Profit (Hint: Calculate the number of courses offered in 2022 using the Target profit formula.) (Number of courses offered in 2022= (Total Fixed costs+ profit)/Contribution per unit). 2. Calculate the break-even point in units for each proposal. 3. Using proposal (ii), how many courses the college would need to book to earn a profit of 450,000 ? 4. Prepare the cash budget for Will llege from July to December 2023 using the following information. (Number of courses offered in 2022= (Total Fixed costs + profit)/Contribution per unit). 2. Calculate the break-even point in units for each proposal. 3. Using proposal (ii), how many courses the college would need to book to earn a profit of 450,000 ? 4. Prepare the cash budget for Williams college from July to December 2023 using the following information. a. The college expects to run 1,850-day courses per month at 80 per course. Income will be received in the month the course is offered. b. In addition to the day courses offered to businesses, the college also runs Excel training for individuals every 6 weeks. This training will hold in July, August, October and November. The training costs 280 per individual and it is expected that each training will have 200 participants. The income will be received in the month the training starts. c. The variable cost for the day courses will be 54 per course and it will be paid monthly. d. The cost for the excel training will 15,000 per training. 60% will be at the start of the training and 40% in the following month. e. A fixed cost of 22,000 will be paid monthly f.The monthly depreciation will be 1,667 g. The college intends to buy new laptops and projectors estimated at 45,000 in November. h. The Director of the college will be paid a bonus of 15,000 in December. i.The college currently has a loan of 200,000 at an interest rate of 6% per annum. Interest is paid quarterly and will be due in September and December. j.The opening cash balance as at 1 July 2023 is expected to be 50,000 Questions to be answered on the online quiz