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: 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 westem 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 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 POtion per year (a constant) plus 5% of total revenue for the year.* Advertising expgnse 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 margjn 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. A C D 3 CONSTANTS 4 TAX RATE EXPED 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 PORTIONYR 14 MARKET SHARE FACTOR 15 NUMBER OF HOURS OPEN/DAY 2019 2020 2021 0.32 2022 NA 0.31 0.33 NA 0.1 0.1 0.1 NA 2 2 2 NA 6.5 7 13000 NA 13500 14000 NA 2 2.1 2.2 NA 10000 10000 10000 NA 1000 1100 1200 NA 300 300 300 14400 0.073 NA 12000 16800 NA 0.07 0.077 NA. 10 10 10 16 17 INPUTS 2019 2020 2021 2022 NUMBER OF COFFEE SHOPS NA 1 18 IN TOWN (INCLUDE YOURS) = N 2 ADVERTISING LEVEL (1 = HI; 19 2= MED; 3 LOW) = A 2 2 NA C D 25 2019 2020 NA ?(1 mark) NA ?(1 mark) NA ?(3 marks)copy&paste copy&paste NA ?(1 mark) NA ?(1 mark) NA ?(1 mark) NA (1 mark) NA ?(1 marks) copy&paste copy& paste 26 CALCULATIONS 2021 copy&paste copy & paste copy&paste copy & paste 2022 27 TOTAL MARKET IN TOWN (CUPS/DAY) 28 NUMBER OF CUPS SOLD/DAY 29 AVG SELLING PRICE/CUP copy&paste copy&paste copy&paste copy&paste copy&paste copy& paste copy&paste copy&paste 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 C D E 35 INCOME STATEMENT AND 36 CASH FLOW STATEMENT 37 BEGINNING OFYEAR CASH ON HAND 2019 2020 2021 NA ?(0.5 mark) copy & paste copy&paste 2022 38 39 REVENUE (SALES) 40 COFFEE 41 CAKES, ETC. 42 TOTAL REVENUE (SALES) NA copy & paste copy&paste copy&paste copy & paste copy&paste copy& paste NA ? (1 marks) NA (1 marks) NA (1 mark) 43 COSTS AND EXPENSES: NA 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 y & paste co & aste copy & paste copy&paste copy&paste copy & paste copy &paste copy & paste y & paste co & aste copy&paste copy & paste copy& paste copy &paste copy&paste copy & paste 44 COST OF SALES -- COFFEE COST OF SALES-- CAKES, ETC. 45 46 COST OF SALARIES 47 RENT EXPENSE ADVERTISING EXPENSE 48 49 BUSINESS LISCENSE EXPENSE 50 TOTAL COSTS AND EXPENSES 51 PRE-INTEREST EXPENSE MARGIN 52 INTEREST EXPENSE 53 PRE-TAX PROFIT MARGIN 54 TAXEXPENSE 55 NET INCOME NET CASH POSITION (NCP) BEFORE BORROWINGS AND REPAYMENTS OF NA 57 DEBT (BEG CASH PLUS NET INCOME) ? (1 mark) copy & paste copy & paste NA ?(2 marks) copy&paste copy& paste NA ? (6 marks) Copy&paste copy&paste 10000 ?(1 marks) copy&paste copy&paste 58 ADD: BORROWINGS FROM BANK 59 LESS: REPAYMENTS TO BANK 60 EQUALS: END OF YEAR CASH ON HAND 61 62 DEBT OWED 2021 2019 2020 NA ? (0.5mark) copy&paste copy&paste NA ?(0.5mark) copy&paste copy& paste NA ?(0.5 mark) copy&paste copy & paste ? (0.5mark) copy& paste copy&paste 2022 63 OWED TO BANK AT BEGINNING OF YEAR 64 ADD: BORROWED FROM BANK 65 LESS: REPAYMENTS TO BANK 66 EQUALS: OWED TO BANK AT END OF YEAR CT 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: 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 westem 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 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 POtion per year (a constant) plus 5% of total revenue for the year.* Advertising expgnse 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 margjn 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. A C D 3 CONSTANTS 4 TAX RATE EXPED 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 PORTIONYR 14 MARKET SHARE FACTOR 15 NUMBER OF HOURS OPEN/DAY 2019 2020 2021 0.32 2022 NA 0.31 0.33 NA 0.1 0.1 0.1 NA 2 2 2 NA 6.5 7 13000 NA 13500 14000 NA 2 2.1 2.2 NA 10000 10000 10000 NA 1000 1100 1200 NA 300 300 300 14400 0.073 NA 12000 16800 NA 0.07 0.077 NA. 10 10 10 16 17 INPUTS 2019 2020 2021 2022 NUMBER OF COFFEE SHOPS NA 1 18 IN TOWN (INCLUDE YOURS) = N 2 ADVERTISING LEVEL (1 = HI; 19 2= MED; 3 LOW) = A 2 2 NA C D 25 2019 2020 NA ?(1 mark) NA ?(1 mark) NA ?(3 marks)copy&paste copy&paste NA ?(1 mark) NA ?(1 mark) NA ?(1 mark) NA (1 mark) NA ?(1 marks) copy&paste copy& paste 26 CALCULATIONS 2021 copy&paste copy & paste copy&paste copy & paste 2022 27 TOTAL MARKET IN TOWN (CUPS/DAY) 28 NUMBER OF CUPS SOLD/DAY 29 AVG SELLING PRICE/CUP copy&paste copy&paste copy&paste copy&paste copy&paste copy& paste copy&paste copy&paste 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 C D E 35 INCOME STATEMENT AND 36 CASH FLOW STATEMENT 37 BEGINNING OFYEAR CASH ON HAND 2019 2020 2021 NA ?(0.5 mark) copy & paste copy&paste 2022 38 39 REVENUE (SALES) 40 COFFEE 41 CAKES, ETC. 42 TOTAL REVENUE (SALES) NA copy & paste copy&paste copy&paste copy & paste copy&paste copy& paste NA ? (1 marks) NA (1 marks) NA (1 mark) 43 COSTS AND EXPENSES: NA 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 y & paste co & aste copy & paste copy&paste copy&paste copy & paste copy &paste copy & paste y & paste co & aste copy&paste copy & paste copy& paste copy &paste copy&paste copy & paste 44 COST OF SALES -- COFFEE COST OF SALES-- CAKES, ETC. 45 46 COST OF SALARIES 47 RENT EXPENSE ADVERTISING EXPENSE 48 49 BUSINESS LISCENSE EXPENSE 50 TOTAL COSTS AND EXPENSES 51 PRE-INTEREST EXPENSE MARGIN 52 INTEREST EXPENSE 53 PRE-TAX PROFIT MARGIN 54 TAXEXPENSE 55 NET INCOME NET CASH POSITION (NCP) BEFORE BORROWINGS AND REPAYMENTS OF NA 57 DEBT (BEG CASH PLUS NET INCOME) ? (1 mark) copy & paste copy & paste NA ?(2 marks) copy&paste copy& paste NA ? (6 marks) Copy&paste copy&paste 10000 ?(1 marks) copy&paste copy&paste 58 ADD: BORROWINGS FROM BANK 59 LESS: REPAYMENTS TO BANK 60 EQUALS: END OF YEAR CASH ON HAND 61 62 DEBT OWED 2021 2019 2020 NA ? (0.5mark) copy&paste copy&paste NA ?(0.5mark) copy&paste copy& paste NA ?(0.5 mark) copy&paste copy & paste ? (0.5mark) copy& paste copy&paste 2022 63 OWED TO BANK AT BEGINNING OF YEAR 64 ADD: BORROWED FROM BANK 65 LESS: REPAYMENTS TO BANK 66 EQUALS: OWED TO BANK AT END OF YEAR CT