70.2K

Verified Solution

Link Copied!

I want the answers starting from 18-51 in terms of Excel formula. Thank you!

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 010) 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. The average cost of employee benefits is 50% (cell C10) of the average base salary. 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. 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. NA 0.5 2020 2019 NA NA 2019 NA 1500 30 10 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) 17 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 20 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 7.9 3 2020 ?? [3 marks] ?? [2 marks] ?? [2 marks] ?? [2 marks] ?? [2 marks] ?? [2 marks] ?? [1 mark] ?? [1 mark] NA NA NA 2019 NA NA NA NA 2020 ?? [0.5 mark] ?? [1 mark] NA NA NA ?? [1 mark] ?? [1 mark] ?? [1 mark] ?? [2 marks! NA NA NA 35 TOTAL COSTS AND FEES 36 PRE-INTEREST EXPENSE MARGIN 37 INTEREST EXPENSE 38 PRE-TAX PROFIT MARGIN 39 TAX EXPENSE 40 NET INCOME ?? [2 marks] ?? [1 mark] ?? [1 mark] ?? [1 mark] ?? [2 marks] ?? [1 mark] NA NA NA B C NA NA NA 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 10000 2019 ?? [1 mark] ?? [2 marks] ?? [6 marks] ?? [1 marks] 2020 ?? [0.5 mark] ?? [0.5 mark] ?? [0.5 mark] ?? [1 marks) NA NA 0 Figure 1: (NA stands for Not Applicable)

Answer & Explanation Solved by verified expert

See Answer