Question

I just need the excel formula of C27 to C66

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 2 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 means 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. Advertising 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:

• Tax rate: The corporate tax rate which is applied to pre-tax profit, is expected to increase each year from 31% (row 4) for the next three years (2020 – 2022).

• Interest rate on debt: The interest rate applies (row 5) applies to any debt owed at the start of the year.

• Number of employees: YYY would always need 2 (row 5) hourly-paid employees at work, not counting the manager.

• Base hourly wage: This is the minimum pay rate per hour determined by the government (row 7), but to attract and retain good people, this is expected to increase each year.

• Number of people in town: This is an approximate population figure in the town (row 8), and this figure is expected to increase each year because government is pushing an urbanization policy in country towns.

• Base coffee selling price: A cup of coffee is set to sell at $2 (row 9); as this is the average floor price for all the kinds of coffee sold by the shop, but the actual selling prices are usually more than this base.

• Minimum cash required to start next year: YYY’s policy is to have at least $10000 cash on hand at the end of each year, in order to start next year‘s business (row 10). YYY’s banker will lend YYY whatever amount YYY needs at the end of each year if YYY falls below this minimum cash level.

• City business license per year: YYY’s coffee shop places a few small tables on the side walk; and the city charges license fee (row 11) on this. This fee increases each year.

• Number of business days and hours: The coffee shop will be open 300 days a year, 10 hours a day (rows 12, 15).

• Rental expense: This is fixed portion per year (row 13), and is expected to increase each year.

• The market share factor: This is the percentage of people in town who are likely to buy from a coffee shop in a day (row 14). YYY thinks this percentage will increase each year as western style coffee shops become more popular.

Calculations (rows 26 to 34) are described below:

• Total market in town (row 27): This is the total number of cups of coffee sold per day in the town, which is the market share factor times the number of people in town.

• Number of cups of coffee sold per day (row 28): This is the number of cups of coffee sold per day at YYY’s shop, which is the total market divided by the number of coffee shops stated in row 18.

• Average selling price per cup (row 29): This follows the equation below: (base coffee selling price + ((number of coffee shops+1)/(advertising level + 1)) - (number of coffee shops*$0.5))

• Number of cakes (etc.) per day (row 30): Most people who buy a coffee will also buy a piece of cake or sandwich, but this is not always so. YYY thinks the number of cakes sold per day will be 80% of the number of cups of coffee sold per day.

• Average selling price per cake (row 31): YYY thinks the average selling price per cake is twice the average selling price per cup of coffee.

• Cost of sales per cup of coffee (row 32): The average cost of sales per cup is 110% of the base coffee selling price.

• Cost of sales per cake (row 33): The average cost of sales per cake is 75% of the selling price of these items.

• Cost of salaries per day (row 34): This is a function of the number of employees, the hourly wage rate, and the expected number of working hours per day.

Income & Cash Flow Statements (rows 36 to 60) are described below:

• Beginning of year cash on hand (row 37) is the cash left at the end of previous year.

• Revenue from sales of cups of coffee for the year (row 40).

• Revenue from sales of cakes for the year (row 41).

• Total revenue from sales of both cakes and coffee for the year (row 42).

• Cost of sales for all cups of coffee sold for the year (row 44).

• Cost of sales for all cakes sold for the year (row 45). • Cost of salaries for all employees for the year (row 46).

• Rental expense for the year (row 47) is the fixed portion per year (a constant) plus 5% of total revenue for the year.

• Advertising expense for the year (row 48) is in proportion to the intensity level of advertising chosen:$10,000 a year for low level, $20,000 a year for medium level, $30,000 a year for high level.

• Interest (simple) expense for the year (row 52) is a simple interest based on the year’s interest rate and the debt owed at the beginning of that year.

• Tax expense for the year (row 54) is a function of the tax rate of the year and the pre-tax profit margin of the year. However, tax office will not charge any tax if pre-tax profit margin is zero or negative. Also the tax office will ignore any cents in the tax.

• Net Cash Position (NCP) (row 57): NCP at the end of a year equals the cash beginning of a year, plus the year’s net income, assuming that there are no receivables or payables.

• Assume that YYY’s bankers will lend enough money (row 58) at the end of a year to get to YYY’s minimum cash target (see row 10). If the NCP is less than the minimum cash at the end of a year, YYY must borrow enough to reach the minimum cash target. Borrowings increase cash on hand, of course.

• If the NCP is more than the minimum cash and there is outstanding debt from previous year(s), then some or all of the debt should be repaid, but not to take YYY below the minimum cash level (row 59).

• Cash at the end of the year equals the NCP, plus any borrowings and less any repayments (row 60).

Debt Owed (rows 62 to 66) is described below:

• Debt owed at the beginning of a year (row 63) equals the debt owed at the end of the previous year.

• Amounts borrowed and repaid (row 64, 65) that have been calculated before can be echoed to this section.

• The amount owed at the end of a year (row 66) equals to the debt owed at the beginning of the year plus any borrowings, and less any repayments.

Α. B D 2022 0.33 0.1 2 7 3 CONSTANTS 4 TAX RATE EXPECTED 5 INTEREST RATE FOR YEAR 6 NUMBER OF EMPLOYEES 7 BASE HOURLY WAGE 8 NUMBER OF PEOPLE IN TOWN 9 BASE COFFEE SELLING PRICE 10 MIN CASH RQRD AT START OF YR 11 |CITY BUSINESS LISCENSE/YR 12 NUMBER OF BUSINESS DAYS 13 RENT EXPENSE -- FIXED PORTION/YR 14 MARKET SHARE FACTOR 15 NUMBER OF HOURS OPEN/DAY 16 17 INPUTS NUMBER OF COFFEE SHOPS 18 IN TOWN (INCLUDE YOURS) = N с 2019 2020 NA 0.31 NA 0.1 NA 2 NA 6 NA 13000 NA 2 NA 10000 NA 1000 NA 300 NA 12000 NA 0.07 NA 10 2021 0.32 0.1 2 6.5 13500 2.1 10000 1100 300 14400 0.073 10 14000 2.2 10000 1200 300 16800 0.077 10 2019 2020 2021 2022 NA ADVERTISING LEVEL (1 = HI; 19 2 = MED, 3 = LOW) = A NA

Answer 1

Attached are the screenshots with formula to use in each row and column:

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

36 Income Statement and Cash flow statement 37 Beginning of year cash on hand NA 2020 10,000.00 2021 9,67,469.53 Formula to use in "C" 2022 column with prefix"=" 1,436,593.57 B60 Formula to use in "D" column with prefix"=" C60 Formula to use in "E" column with prefix":" D60 38 40 41 NA 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) Coffee Cakes, etc. 42 Total Revenue (sales) Costs and Expenses Cost of sales - Coffee Cost of sales - Cakes, etc. cost of Salaries Rent Expense 2,002.00 2,366.00 36,000.00 88,895.00 1,138.25 1,241.73 39,000.00 54,756.23 48 49 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 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,10000)) ,20000, 10000)) ,20000,10000)) 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 051-C52 D51-D52 E51-E52 151,688.19 IC(C53<0,0,053*C4) IF(D53<0,0,D53*D4) IF(E53<0,0,E53*E4) 307,972.99 C53-C54 D53-D54 E53-E54 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 NA 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