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).- Calculations (cell A17 to C25) are described below: This part calculates the intermediate results for the Income & Cash Flow Statement. These calculations are based on the input values in cell C14 and C15, and possibly based on 2019 values.' Outsourcing cost (cell C18) is based on the outsourcing method chosen in cell C15. It is zero if no outsourcing is chosen. If the flat fee method is chosen, outsourcing cost is equal to a constant value. If the % of sales method is chosen, outsourcing cost is equal to the total revenue (sales) multiplied by the specified percentage. The average number of chocolates sold per day (cell C19) will rise by 10% in year 2020 if an optimistic economy exists; otherwise it will rise only by 1% in year 2020. As this is an average number, partial number of chocolates (e.g. 13.65) is not expected; only whole number of chocolates is expected (e.g. 13). If no outsourcing is chosen, number of employees (cell C20) will increase by 1 in year 2020. However, if outsourcing is chosen, CCC will only have 15 employees in year 2020.- Selling price per chocolate (cell C21) will rise by 6% in year 2020 if an optimistic economy exists; otherwise it will only rise by 1 % in year 2020. Selling price per chocolate should be accurate in forecast, thus cent is to be retained. Costs of goods sold per chocolate (cell C22) will rise by 2% in year 2020 if an optimistic economy exists; otherwise it will only rise by 1 % in year 2020. Costs of goods sold per chocolate should be accurate in forecast, thus cent is to be retained. - Simple interest must be paid on debt owed to the bank. Interest rate (cell C23) is expected to be 6% in year 2020 if an optimistic economy exists; otherwise it will be 5% in year 2020.- Salary costs in year 2020 (cell C24) equal to the average employee base salary per year per employee times the number of CCC employees. As this is an average number, any fractional part of a dollar is discarded (e.g. "56.78 should be $56''). Number of chocolates sold for year 2020 (cell C25) is a function of average number of chocolates sold per day and the number of business days in year 2020. 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 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).- Calculations (cell A17 to C25) are described below: This part calculates the intermediate results for the Income & Cash Flow Statement. These calculations are based on the input values in cell C14 and C15, and possibly based on 2019 values.' Outsourcing cost (cell C18) is based on the outsourcing method chosen in cell C15. It is zero if no outsourcing is chosen. If the flat fee method is chosen, outsourcing cost is equal to a constant value. If the % of sales method is chosen, outsourcing cost is equal to the total revenue (sales) multiplied by the specified percentage. The average number of chocolates sold per day (cell C19) will rise by 10% in year 2020 if an optimistic economy exists; otherwise it will rise only by 1% in year 2020. As this is an average number, partial number of chocolates (e.g. 13.65) is not expected; only whole number of chocolates is expected (e.g. 13). If no outsourcing is chosen, number of employees (cell C20) will increase by 1 in year 2020. However, if outsourcing is chosen, CCC will only have 15 employees in year 2020.- Selling price per chocolate (cell C21) will rise by 6% in year 2020 if an optimistic economy exists; otherwise it will only rise by 1 % in year 2020. Selling price per chocolate should be accurate in forecast, thus cent is to be retained. Costs of goods sold per chocolate (cell C22) will rise by 2% in year 2020 if an optimistic economy exists; otherwise it will only rise by 1 % in year 2020. Costs of goods sold per chocolate should be accurate in forecast, thus cent is to be retained. - Simple interest must be paid on debt owed to the bank. Interest rate (cell C23) is expected to be 6% in year 2020 if an optimistic economy exists; otherwise it will be 5% in year 2020.- Salary costs in year 2020 (cell C24) equal to the average employee base salary per year per employee times the number of CCC employees. As this is an average number, any fractional part of a dollar is discarded (e.g. "56.78 should be $56''). Number of chocolates sold for year 2020 (cell C25) is a function of average number of chocolates sold per day and the number of business days in year 2020. 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
