Write the Excel formula for each cell marked with ? in column C such that formula could
Question:
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.
Expert Answer:
Applied Statistics in Business and Economics
ISBN: 978-0073521480
4th edition
Authors: David Doane, Lori Seward
