Please answer all the blanks in the spreadsheet, thanks!

Question 2 (40 marks) Refer to Figure 1. Write the Excel formula for each cell marked with ?? in column C. and label each formula clearly with cell reference position. CCC Chocolate Manufacturing Company has been profitable and its financial condition is good. However, CCC's management would like to increase its cash flow and profit in future by considering to outsource some cleaning up tasks (such as various machine disinfection and cleaning jobs) to XXX Consulting Company. If outsourcing is done, CCC would cut the number of cleaning staff from 30 to 15, and this would cut CCC's salary and employee benefits expenses, but CCC would need to pay XXX a service fee. If the outsourcing fee is less than the laid-off workers' salaries and benefits, CCC's profit will increase. The outsourcing fee can be paid in one of two ways: Flat fee CCC would pay a fixed fee per year to XXX, % of sales CCC would pay XXX a certain % of CCC's revenue sales each year. - If CCC does not outsource, the outsourcing fee is zero. Now it is the end of 2019's financial year, CCC's management is making a cash flow and net profit forecast for next year. CCC applies for bank loans each year if cash is insufficient to continue with the business. You are required to make a what-if analysis in Microsoft Excel (see Figure 1) to help CCC management consider the merits of different outsourcing alternatives: Given possible economic and outsourcing scenarios, what will be CCC's net profit next year, and what will the cash on hand and bank debt be at the end of next year? Different possible economic and outsourcing scenarios are entered in cells C14 and C15:- Economic outlook (C14) has two values: O for optimistic, P for pessimistic, Outsource method (C15) has 3 values: F for flat fee, P for % of sales, N for no outsourcing. + You are required to write Excel formulas in cells C18 to C51 (figure 1) for this what-if analysis forecast. The forecast is based on 2019's values, such as average number of chocolates sold per day, number of employees, selling price per chocolate, cost of goods sold per chocolate (cells B19 to B22), cash on hand at the end of year 2019, and debt owed at the end of year 2019 (cells B45 and B51).- Debt Owed (cell A47 to C51) is described below: Debt owed at the beginning of year (cell C48) equals to the debt owed at the end of previous year.' Amounts borrowed from the bank (cell C49) and payments of debt to bank (cell C50) have been calculated previously in cash flow statement, and they can be copied here. The amount of debt owed at the end of year (cell C51) equals to the debt owed at the beginning of year plus any borrowings and less any repayments of debt. B NET CASH POSITION (NCP) BEFORE BORROWINGS AND REPAYMENTS OF DEBT(BEG 42 CASH + NET INCOME) 43 ADD: BORROWINGS FROM BANK 44 LESS: REPAYMENTS TO BANK 45 EQUALS: END OF YEAR CASH ON HAND 47 DEBT OWED 48 OWED TO BANK AT BEGINNING OF YEAR 49 ADD: BORROWINGS FROM BANK 50 LESS: REPAYMENTS TO BANK EQUALS: OWED TO BANK AT END OF YEAR NA NA NA 10000 2019 NA NA NA 0 ?? [1 mark] ?? [2 marks] ?? [6 marks] ?? [1 marks] 2020 ?? [0.5 mark] ?? [0.5 mark] ?? [0.5 mark] ?? [1 marks] Figure 1: (NA stands for Not Applicable) Question 2 (40 marks) Refer to Figure 1. Write the Excel formula for each cell marked with ?? in column C. and label each formula clearly with cell reference position. CCC Chocolate Manufacturing Company has been profitable and its financial condition is good. However, CCC's management would like to increase its cash flow and profit in future by considering to outsource some cleaning up tasks (such as various machine disinfection and cleaning jobs) to XXX Consulting Company. If outsourcing is done, CCC would cut the number of cleaning staff from 30 to 15, and this would cut CCC's salary and employee benefits expenses, but CCC would need to pay XXX a service fee. If the outsourcing fee is less than the laid-off workers' salaries and benefits, CCC's profit will increase. The outsourcing fee can be paid in one of two ways: Flat fee CCC would pay a fixed fee per year to XXX, % of sales CCC would pay XXX a certain % of CCC's revenue sales each year. - If CCC does not outsource, the outsourcing fee is zero. Now it is the end of 2019's financial year, CCC's management is making a cash flow and net profit forecast for next year. CCC applies for bank loans each year if cash is insufficient to continue with the business. You are required to make a what-if analysis in Microsoft Excel (see Figure 1) to help CCC management consider the merits of different outsourcing alternatives: Given possible economic and outsourcing scenarios, what will be CCC's net profit next year, and what will the cash on hand and bank debt be at the end of next year? Different possible economic and outsourcing scenarios are entered in cells C14 and C15:- Economic outlook (C14) has two values: O for optimistic, P for pessimistic, Outsource method (C15) has 3 values: F for flat fee, P for % of sales, N for no outsourcing. + You are required to write Excel formulas in cells C18 to C51 (figure 1) for this what-if analysis forecast. The forecast is based on 2019's values, such as average number of chocolates sold per day, number of employees, selling price per chocolate, cost of goods sold per chocolate (cells B19 to B22), cash on hand at the end of year 2019, and debt owed at the end of year 2019 (cells B45 and B51).- Debt Owed (cell A47 to C51) is described below: Debt owed at the beginning of year (cell C48) equals to the debt owed at the end of previous year.' Amounts borrowed from the bank (cell C49) and payments of debt to bank (cell C50) have been calculated previously in cash flow statement, and they can be copied here. The amount of debt owed at the end of year (cell C51) equals to the debt owed at the beginning of year plus any borrowings and less any repayments of debt. B NET CASH POSITION (NCP) BEFORE BORROWINGS AND REPAYMENTS OF DEBT(BEG 42 CASH + NET INCOME) 43 ADD: BORROWINGS FROM BANK 44 LESS: REPAYMENTS TO BANK 45 EQUALS: END OF YEAR CASH ON HAND 47 DEBT OWED 48 OWED TO BANK AT BEGINNING OF YEAR 49 ADD: BORROWINGS FROM BANK 50 LESS: REPAYMENTS TO BANK EQUALS: OWED TO BANK AT END OF YEAR NA NA NA 10000 2019 NA NA NA 0 ?? [1 mark] ?? [2 marks] ?? [6 marks] ?? [1 marks] 2020 ?? [0.5 mark] ?? [0.5 mark] ?? [0.5 mark] ?? [1 marks] Figure 1: (NA stands for Not Applicable)