Question

Question 1 (40 marks) Refer to Table 1. Write the Excel formula for each cell marked with "" in column C such that formula could be copied and pasted into columns D, and E using Microsoft Excel without further editing, There is no need to explicitly write the Excel formula for cells marked with "Copy & paste". Label each formula clearly with cell reference position. In many country towns of China, there is no western coffee shop such as Starbucks which sell different types of coffees, cakes, pies, cookies and snacks to customers. A food company called, YYY from Hong Kong would like to start opening western style coffee shops in country towns of China as there is not much competition now However, YYY thinks other food companies would also like to venture into China in future. The question is: "How much competition will arise?" YYY's current financial situation would be able to support no more than three coffee shops However, some top-level managers in YYY are not sure if more than one coffee shop could be supported because of the low acceptance of coffee drinking culture by lowly educated country Chinese. YYY's success will depend on 2 factors: how many coffee shops open to compete with YYY's, and how much advertising YYY should do. Top-level management is giving a 3-year trial for this Chinese coffee shop project starting from year 2019. You are required to use Microsoft Excel (see table 1) to forecast net income, cash on hand, and money owed to bank at the end of each year 2020-2022. You are asked to help YYY's management and write Excel formulas in cells C26 to C66 to do these forecasts by performing a what-if analysis using Microsoft Excel. In this Excel worksheet (see table 1), the inputs in row 18 are for the number of coffee shops in a country town, ranging, from 1 (assume YYY's is 1) to 3 (that ueans YYY's and 2 competitors). Different digit can be entered for each year, e.g. 1 for 2020, 2 for 2021, 3 for 2022. The inputs in row 19 are for the intensity level of advertising chosen: low (1) medium (2), or high (3) in the three year period: 2020 to 2022. Adyertising will still be needed even if YYY's is only coffee shop in town. Different digit can be entered for each year, e.g. 1 for 2020, 3 for 2021, 2 for 2022. The following constants (rows 3 to 15) for the forecast are described below:

Answer 1

Please find below 3 screenshots of excel in which answer are provided along with formula to use. Formula to use in C, D & E column are provided in F,G & H column respectively.

Number of coffee shops in town (include 18 years) = N 19 Advertising Level (1=hi, 2=med, 3=low)=A NA NA 2019 26 Calculations 27 Total Market In Town (cups/day) 28 Number of cups sold/day 2020 910.00 910.00 2021 985.50 492.75 29 Avg Selling price/cup 30 Number of cakes (etc)/day 31 Avg Selling price/cake 32 cost of sales per cup 33 Cost of Sales per cake 34 Cost of Salaries/day 2.17 728.00 4.33 2.20 3.25 120.00 2.10 394.20 4.20 2.31 3.15 130.00 Formula to use in "C" 2022 column with prefix"=" 1,078.00 C8*C14 359.33 C27/018 (C9+((C18+1)/(C19+1))- 2.03 (C18*0.5)) 287.47 028*80% 4.07 C29*2 2.42 C9*110% 3.05 31*75% 140.00 C6*07*C15 Formula to use in "D" column with prefix"=" D8*D14 D27/D18 (D9+ (D18+1)/(D19+1))- (D18*0.5)) D28*80% D29*2 D9*110% D31*75% D6*D7*D15 Formula to use in "E" column with prefix"=" E8*E14 E27/E18 (E9+ (E18+1)/(E19+1))- (E18*0.5)) E28*80% E29*2 E9*110% E31*75% E6*E7*E15 35

C D E Formula to use in "C" 2022 column with prefix"=" Formula to use in "D" column with prefix"=" Formula to use in "E" column with prefix "-" 2020 2021 36 Income Statement and Cash flow statement 37 Beginning of year cash on hand NA 38 41 591,500.00 946,400.00 1,537,900.00 310,432.50 496,692.00 807,124.50 219,193.33 C28*C29*C12 350,709.33 C30*C31*c12 569,902.67 SUM(C40:041) D28*D29*D12 D30*D31*D12 SUM(D40:041) E28*E29*E12 E30*E31*E12 SUM(E40:E41) 39 Revenue (sales) 40 Coffee Cakes, etc. 42 Total Revenue (sales) Costs and Expenses Cost of sales - Coffee Cost of sales - Cakes, etc. cost of Salaries Rent Expense 45 2,002.00 2,366.00 36,000.00 88,895.00 1,138.25 1,241.73 39,000.00 54,756.23 20,000.00 1,000.00 150,263.00 1,387,637.00 20,000.00 1,100.00 117,236.21 689,888.29 48 Advertising Expense Business License Expense 50 Total Costs and Expense 51 Pre-interest Expense Margin 52 Interest Expense 53 Pre-tax profit Margin 54 Tax Expense 55 Net Income 869.59 C28*C32 D28*D32 E28*E32 876.77 C30*C33 D30*D33 E30*E33 42,000.00 C34*C12 D34*D12 E34*E12 45,295.13 C13+(C42*5%) D13+(D42*5%) E13+(E42*5%) IF(C19=1,30000, IF(C19=2 IF(D19=1,30000, IF(D19=2 IF(E19=1,30000,IF(E19=2 20,000.00 ,20000, 30000)) ,20000,30000)) ,20000,30000)) 1,200.00 C11 D11 E11 110,241.49 SUM(C44:C49) SUM(D44:D49) SUM(E44:E49) 459,661.17 C42-C50 D42-D50 E42-E50 - B66*C5 C66*D5 D66*E5 459,661.17 C51-C52 D51-D52 E51-E52 151,688.19 IC(C53<0,0,C53*C4) IF(D53<0,0,D53*D4) IF(E53<0,0,E53*E4) 307,972.99 C53-C54 D53-054 E53-E54 NA 1,387,637.00 430,167.47 957,469.53 689,888.29 220,764.25 469,124.04 NA Active

A 150,263.00 1,387,637.00 117,236.21 689,888.29 50 Total Costs and Expense 51 Pre-interest Expense Margin 52 Interest Expense 53 Pre-tax profit Margin 54 Tax Expense 55 Net Income 110,241.49 SUM(C44:C49) 459,661.17 C42-C50 B66*C5 459,661.17 C51-C52 151,688.19 IC(C53<0,0,C53*C4) 307,972.99 C53-C54 SUM(D44:049) D42-D50 C66*D5 D51-D52 IF(D53<0,0,053*D4) D53-D54 SUM(E44:E49) E42-E50 D66*E5 E51-E52 IF(E53<0,0, E53*E4) E53-E54 1,387,637.00 430,167.47 957,469.53 689,888.29 220,764.25 469,124.04 Net Cash position (NCP) before borrowings 57 and repayments of debt(Beg cash plus net NA 967,469.53 1,436,593.57 1,744,566.56 B60+C55 C60+D55 D60+E55 58 Add: Borrowings from bank NA -IF(C57<0,(C57-10000),0) -IF(D57<0,(D57-10000),O) -IF(E57<0,(E57-10000),0) IF((C57+058- IF(D57+D58- IF(E57+E58- B66)>10000,166,0) C66)>10000, C66,0) D66)>10000, D66,0) 1,744,566.56 C57+C58-C59 D57+D58-D59 E57+E58-E59 59 Less: Repayments to bank 60 Equals: end of year cash on hand NA 10,000.00 967,469.53 1,436,593.57 2019 2020 62 Debt Owed: 63 Owed to Bank at beginning of year 64 Add: borrowed from bank 65 Less: Repayment to bank 66 Equals: Owed to bank at end of year Formula to use in "C" 2022 column with prefix"=" B66 - 058 Formula to use in "D" column with prefix"=" C66 D58 D59 D63+D64-D65 Formula to use in "E" column with prefix"=" D66 E58 E59 E63+E64-E65 C59 C63+C64-C65 67 68 Active