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).- Income & Cash Flow Statements (cell A27 to C45) are described below: Cash on hand at the beginning of year (cell C28) equals to the cash on hand at the end of previous year. The cost of benefits (cell C34) is a function of salaries and the benefits percentage of the year. Pre-Interest expense margin (cell C36) is total revenue minus total costs and fees. Interest expense (cell C37) equals to the interest rate for the year times the amount of debt owed to the bank at the beginning of year. Bank does not want to receive cent in interests.- Pre-Tax profit margin (cell C38) is the profit before considering tax expense. Tax expense (cell C39) is zero if pre-tax profit margin is zero or negative; otherwise apply the tax rate for the year to the pre-tax profit margin. Government does not want to receive cent in taxes. Total revenue from sales (cell C30), costs of goods sold (cell C32), salary costs and outsourcing fees (cell C33) can be calculated from the immediate results in Calculations part.- Net Cash Position (cell C42) is the cash on hand at the beginning of year plus net income of the year before any payments of debt or borrowings from the bank. - If CCC's net cash position is less than the minimum cash required at the start of next business year, CCC needs to borrow enough cash from the bank (cell C43) to meet the minimum cash required at the start of next business year; otherwise there is no need to borrow. If CCC's net cash position is more than the minimum cash required at the start of next business year, and there is outstanding debt, some or all of the outstanding debt can be repaid (cell C44), but not to take CCC below its minimum cash. - Cash at the end of year (cell C45) is the net cash position plus any borrowings and minus any repayments. 2019 NA 2020 ?? [0.5 mark] ?? [1 mark] NA NA NA INCOME STATEMENT AND 27 CASH FLOW STATEMENT 28 BEGINNING OF YEAR CASH ON HAND 30 Total REVENUE from SALES 31 COSTS AND FEES: 32 COST OF GOODS SOLD 33 SALARIES AND OUTSOURCING 34 COST OF BENEFITS 35 TOTAL COSTS AND FEES 36 PRE-INTEREST EXPENSE MARGIN 37 INTEREST EXPENSE 38 PRE-TAX PROFIT MARGIN 39 TAX EXPENSE 40 NET INCOME NA NA NA NA NA NA NA NA ?? [1 mark] ?? [1 mark] ?? [1 mark] ?? [2 marks] ?? [1 mark] ?? [1 mark] ?? [1 mark] ?? [2 marks] ?? [1 mark] 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).- Income & Cash Flow Statements (cell A27 to C45) are described below: Cash on hand at the beginning of year (cell C28) equals to the cash on hand at the end of previous year. The cost of benefits (cell C34) is a function of salaries and the benefits percentage of the year. Pre-Interest expense margin (cell C36) is total revenue minus total costs and fees. Interest expense (cell C37) equals to the interest rate for the year times the amount of debt owed to the bank at the beginning of year. Bank does not want to receive cent in interests.- Pre-Tax profit margin (cell C38) is the profit before considering tax expense. Tax expense (cell C39) is zero if pre-tax profit margin is zero or negative; otherwise apply the tax rate for the year to the pre-tax profit margin. Government does not want to receive cent in taxes. Total revenue from sales (cell C30), costs of goods sold (cell C32), salary costs and outsourcing fees (cell C33) can be calculated from the immediate results in Calculations part.- Net Cash Position (cell C42) is the cash on hand at the beginning of year plus net income of the year before any payments of debt or borrowings from the bank. - If CCC's net cash position is less than the minimum cash required at the start of next business year, CCC needs to borrow enough cash from the bank (cell C43) to meet the minimum cash required at the start of next business year; otherwise there is no need to borrow. If CCC's net cash position is more than the minimum cash required at the start of next business year, and there is outstanding debt, some or all of the outstanding debt can be repaid (cell C44), but not to take CCC below its minimum cash. - Cash at the end of year (cell C45) is the net cash position plus any borrowings and minus any repayments. 2019 NA 2020 ?? [0.5 mark] ?? [1 mark] NA NA NA INCOME STATEMENT AND 27 CASH FLOW STATEMENT 28 BEGINNING OF YEAR CASH ON HAND 30 Total REVENUE from SALES 31 COSTS AND FEES: 32 COST OF GOODS SOLD 33 SALARIES AND OUTSOURCING 34 COST OF BENEFITS 35 TOTAL COSTS AND FEES 36 PRE-INTEREST EXPENSE MARGIN 37 INTEREST EXPENSE 38 PRE-TAX PROFIT MARGIN 39 TAX EXPENSE 40 NET INCOME NA NA NA NA NA NA NA NA ?? [1 mark] ?? [1 mark] ?? [1 mark] ?? [2 marks] ?? [1 mark] ?? [1 mark] ?? [1 mark] ?? [2 marks] ?? [1 mark] 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)