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). The following constants (cell A3 to C10) for the forecast are described below:- The average employee base salary per year is estimated in cell C4 for year 2020.- Each year has 250 business days cell C5. The outsourcing fees for % of sales and flat fee methods are in cells C6 and C7 respectively. The government tax rate is in cell C8. CCC's policy is to have at least $10,000 in cash on hand at the end of each financial year in order to continue business for the next year. This is called the minimum cash required at the start of financial year cell C9. CCC will obtain a bank loan if there is insufficient cash to meet the minimum cash required at the start of financial year. 4 The average cost of employee benefits is 50% (cell C10) of the average base salary. C B 2019 2020 3 CONSTANTS 4 AVERAGE EMPLOYEE BASE SALARY PER YEAR 5 NUMBER OF BUSINESS DAYS 6 OUTSOURCE FEE-- % OF SALES 7 OUTSOURCE FEE -- FLAT FEE 8 TAX RATE EXPECTED 9 MIN CASH RQRD AT START OF YEAR BENEFITS COST -- % OF SALARY 13 INPUTS ECONOMIC OUTLOOK (O = OPTIMISTIC; 14 P = PESSIMISTIC) OUTSOURCE METHOD (N = NONE; F = 15 FLAT FEE; P = PERCENT OF SALES) NA NA NA NA NA NA NA 40000 250 0.28 800000 0.3 10000 0.5 2019 2020 NA NA 2019 NA 1500 30 CALCULATIONS 18 OUTSOURCING COST 19 AVERAGE NUMBER OF chocolates SOLD/DAY 20 NUMBER OF EMPLOYEES 21 SELLING PRICE PER chocolate 22 COST OF GOODS SOLD PER chocolate 23 INTEREST RATE FOR YEAR 24 SALARIES COST 25 NUMBER OF chocolates SOLD IN A YEAR 7.9 2020 ?? [3 marks] ?? [2 marks] ?? [2 marks] ?? [2 marks] ?? [2 marks] ?? [2 marks] ?? [1 mark] ?? [1 mark] 3 NA NA NA 20 2019 NA 2020 ?? [0.5 mark] ?? [1 mark] INCOME STATEMENT AND 27 CASH FLOW STATEMENT 28 BEGINNING OF YEAR CASH ON HAND 30 Total REVENUE from SALES 31 COSTS AND FEES: COST OF GOODS SOLD 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 NA NA NA ?? [1 mark] ?? [1 mark] ?? [1 mark] ?? [2 marks] ?? [1 mark] ?? [1 mark] ?? [1 mark] ?? [2 marks] ?? [1 mark] 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 51 EQUALS: OWED TO BANK AT END OF YEAR NA NA NA 10000 2019 NA NA NA ?? [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). The following constants (cell A3 to C10) for the forecast are described below:- The average employee base salary per year is estimated in cell C4 for year 2020.- Each year has 250 business days cell C5. The outsourcing fees for % of sales and flat fee methods are in cells C6 and C7 respectively. The government tax rate is in cell C8. CCC's policy is to have at least $10,000 in cash on hand at the end of each financial year in order to continue business for the next year. This is called the minimum cash required at the start of financial year cell C9. CCC will obtain a bank loan if there is insufficient cash to meet the minimum cash required at the start of financial year. 4 The average cost of employee benefits is 50% (cell C10) of the average base salary. C B 2019 2020 3 CONSTANTS 4 AVERAGE EMPLOYEE BASE SALARY PER YEAR 5 NUMBER OF BUSINESS DAYS 6 OUTSOURCE FEE-- % OF SALES 7 OUTSOURCE FEE -- FLAT FEE 8 TAX RATE EXPECTED 9 MIN CASH RQRD AT START OF YEAR BENEFITS COST -- % OF SALARY 13 INPUTS ECONOMIC OUTLOOK (O = OPTIMISTIC; 14 P = PESSIMISTIC) OUTSOURCE METHOD (N = NONE; F = 15 FLAT FEE; P = PERCENT OF SALES) NA NA NA NA NA NA NA 40000 250 0.28 800000 0.3 10000 0.5 2019 2020 NA NA 2019 NA 1500 30 CALCULATIONS 18 OUTSOURCING COST 19 AVERAGE NUMBER OF chocolates SOLD/DAY 20 NUMBER OF EMPLOYEES 21 SELLING PRICE PER chocolate 22 COST OF GOODS SOLD PER chocolate 23 INTEREST RATE FOR YEAR 24 SALARIES COST 25 NUMBER OF chocolates SOLD IN A YEAR 7.9 2020 ?? [3 marks] ?? [2 marks] ?? [2 marks] ?? [2 marks] ?? [2 marks] ?? [2 marks] ?? [1 mark] ?? [1 mark] 3 NA NA NA 20 2019 NA 2020 ?? [0.5 mark] ?? [1 mark] INCOME STATEMENT AND 27 CASH FLOW STATEMENT 28 BEGINNING OF YEAR CASH ON HAND 30 Total REVENUE from SALES 31 COSTS AND FEES: COST OF GOODS SOLD 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 NA NA NA ?? [1 mark] ?? [1 mark] ?? [1 mark] ?? [2 marks] ?? [1 mark] ?? [1 mark] ?? [1 mark] ?? [2 marks] ?? [1 mark] 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 51 EQUALS: OWED TO BANK AT END OF YEAR NA NA NA 10000 2019 NA NA NA ?? [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)