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 westem 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 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. I 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 cach 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) Hunyou must work, not 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, barabb 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 *50.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 int e rest rate and 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. S30.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. Table 1: 'NA = Not Applicable, meaning no entry is required in the cell. 3 CONSTANTS TEREST RATE FOR YEAR G NUMBER OF EMPLOYEES 7 BASE NOURLY WADE & NUMBER OF PEOPLE IN TOWN 9 BASE COFFEE SEUNG PRICE 10 MN CASH RORD AT START OF YR 11 CITY BUSTESSUSCENSEUR 12 NUMBER OF BUSINESS DAYS 13 RENT EXPENSE - FED PORTOWYR 14 MARKET SHARE FACTOR 15 NUMBER OF HOURS OPENDAY 17 INPUTS NUMBER OF COFFEE SHOPS 18 N TOWNICLUDE YOURSEN ADVERTISING LEVEL 1 19 MED LOWA - - B C D E 21 CALCULATIONS 27 TOTAL MARKET IN TOWN (CUPSDAY 21 NUMBER OF CUPS SOLD DAY 21 AVG SELLING PRICE CUP 31 NUMBER OF CAKES ETC DAY 31 ANG SELLING PRICE CAKE 32 00ST OF SALES PER CUP 3) COST OF SALES PER CAKE 34 COST OF SALARESDAY a k NA mak) NA 13 ma NA 9/1 mark) NA 91 ma NA (1 mark) NA mak NA?(maks) copy paste copy paste copy paste copy paste y paste & paste Copy & paste copy&paste y paste copy & paste copy&paste copy paste copy & paste copy paste copy paste copy paste BCDE INCOME STATEMENT AND 36 CASIONE STATEMENT 37 BEGRING OF YEAR CASH ON HAND NA 105 mnt copy paste copy paste HA (mar c apy paste copy paste copy paste Oy c apy paste copy & paste HA (1 mal 39 REVENUE SALES 40 COFFEE 41 CANCES ETC 42 TOTAL REVENUE SALES 4) COSTS AND EXPENSES 44 COST OF SALES COFFEE 5 COST OF SALES-CAKES ETC 0 COST OF SALARES 47 FENT EXPENSE 4 ADVERTISING EXPENSE DUSHESSUSCENSE PENSE 50 TOTAL COSTS AND EXPENSES 51 PRE INTEREST EXPENSE MARGIN 5 INTEREST EXPENSE 53 PRE TAX PROFIT MARGN 54 TAXE PENSE 55 ET INDONE TA 7 copy paste copy paste Cape & mo Copy paste copy paste A copy paste copy paste T a schpy paste copy paste A m ante copy paste copy & paste Hey A p y paste copy paste A copy A copy paste y paste A mas copy paste copy paste A man copy paste copy paste NET CASH POSITION NOP BEFORE BORROWNGS AND REPAYMENTS OF 57 DEBT EEG CASH PLUS NET INCOME) copy paste copy paste 58 ADD: BORROWINGS FROM BANK 59 LESS REPAYMENTS TO BANK 60 EQUALS EID OF YEAR CASH ON HAND NA ? maka NA ?6 marts 100001 mats copy paste copy paste copy paste copy paste copy&paste copy & paste 2 DEBTOINED 2019 2020 2021 2022 63 OWED 10 BAIK AT BEGINNING OF YEARNA ?05 makapy paste copy paste 64 ADD BORROWED FROM BANK NA ?05 copy paste copy paste 66 LESS REPAYMENTS TO BANK NA ? ma copy paste copy&paste 66 EQUALS OWED TO BANK AT BO OF YEAR ? makapy paste copy paste Table 1: NA = Not Applicable, meaning no entry is required in the cell. E 2021 0.32 0.1 2022 0.33 0.1 BLC 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 14000 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 22 6.5 13500 2.1 10000 1100 300 14400 0.073 10 10000 1200 300 16800 0.077 10 2019 2020 2021 2022 NA ADVERTISING LEVEL (1 = HI 19 2 = MED: 3 = LOW) = A 122 B C D 2021 26 CALCULATIONS 27 TOTAL MARKET IN TOWN (CUPS/DAY) 28 NUMBER OF CUPS SOLD/DAY 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 2019 2020 NA ? (1 mark) NA ? (1 mark) NA ? (3 marks) NA ? (1 mark) NA ? (1 mark) NA ? (1 mark) NA ? (1 mark) NA ? (1 marks) 2022 copy & paste copy & paste Copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste 2019 2020 NA ? (0.5 mark) 2021 copy & paste 2022 copy & paste NA NA NA NA ? (1 marks) ? (1 marks) ? (1 mark) copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste NA 35 INCOME STATEMENT AND 36 CASH FLOW STATEMENT 37 BEGINNING OFYEAR CASH ON HAND 38 39 REVENUE (SALES): 40 COFFEE 41 CAKES, ETC. 42 TOTAL REVENUE (SALES) 43 COSTS AND EXPENSES 44 COST OF SALES - COFFEE 45 COST OF SALES - CAKES, ETC. 46 COST OF SALARIES 47 RENT EXPENSE 48 ADVERTISING EXPENSE 49 BUSINESS LISCENSE EXPENSE 50 TOTAL COSTS AND EXPENSES 51 PRE-INTEREST EXPENSE MARGIN 52 INTEREST EXPENSE 53 PRE-TAX PROFIT MARGIN 54 TAX EXPENSE 55 NET INCOME NA ? (1 marks) NA ? (1 marks) NA ? (1 mark) NA ? (2 marks) NA ? (2 marks) NA ? (0.5 mark) NA ? (1 mark) NA ? (1 mark) NA ? (1 mark) NA ? (1 mark) NA ? (2 marks) NA? (1 mark) copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste NET CASH POSITION (NCP) BEFORE BORROWINGS AND REPAYMENTS OF 57 DEBT (BEG CASH PLUS NET INCOME) 58 ADD: BORROWINGS FROM BANK 59 LESS: REPAYMENTS TO BANK 60 EQUALS: END OF YEAR CASH ON HAND ? (1 mark) B C NA ? (2 marks) NA ? (6 marks) 10000 ? (1 marks) copy & paste copy & paste D E copy & paste copy & paste copy & paste copy & paste copy & paste copy & paste 62 DEBT OWED 2019 2020 2021 2022 63 OWED TO BANK AT BEGINNING OF YEAR NA 2 (0.5 mark) copy & paste copy & paste 64 ADD: BORROWED FROM BANK NA ? (0.5 mark) copy & paste copy & paste 65 LESS: REPAYMENTS TO BANK NA ? (0.5 mark) copy & paste copy & paste 66 EQUALS: OWED TO BANK AT END OF YEAR O ? (0.5 mark) copy & paste copy & paste Az