Section B (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. CCC is a state in the United States. CCC has a gambling program that is run by the CCC's lottery office. Currently, people go to any of CCC's four horse racing tracks and bet on the races, and play slot machines or card games as if they were at a gambling casino. The racetracks are thus sometimes called 'racinos'. CCC's neighbouring states also have racetracks but they don't have other forms of gambling, thus many people from these neighbouring states drive to CCC's racinos to gamble. Currently, gambling is a significant source of revenue for CCC. However, CCC's neighbouring states are also considering allowing racinos in their states, and this will affect CCC's revenue from racinos. CCC's authorities are considering expanding their gambling program to collect more revenue by introducing gambling on sports events such as soccer, baseball, and basketball at new sports gambling lounges within racinos. Sports bettors could view various real time sports via satellite with food and drinks being served. The new sports gambling lounges would be major construction costs (estimated to be $60 million in cell B59) to expand the racinos, and CCC would need to borrow enough money to cover this construction cost and other operating costs such as extra sports betting staff, and gambling profits would eventually have to pay off this debt. Sports book agent is another major operating cost as the agent would set the real time odds on sport events, record bets, pay winners and collect from losers etc. "Sports book agent is expensive because setting real time odds on sport events involves special expertise like probability, statistics, actuarial mathematics and betting mathematics. CCC would like to forecast the net income, debt owed and cash flow for introducing sports gambling in the next three years (2022 to 2024) based on 2021's data. You are asked to help CCC and write Excel formulas in cells C23 to C59 (see table 1) for this forecast by performing a what-if analysis using Microsoft Excel. Constants (rows 3 to 9) are described below: Tax rate (row 4): is 20% each year on betting income. Cash needed to start next year (row 5): A CCC's policy is to have at least US$5,000,000 cash on hand at the end of each year, in order to start next year's business. It is assumed that CCC's banker will lend whatever is needed at the end of a year to begin the next year with US$5,000,000 Interest rate on debt (row 6): is 6% simple interest each year as CCC borrows money for the new sports gambling lounge and sports betting staff. Number of possible gamblers (row 7): surveys have established how many sports gamblers there might be each year, this number is expected to increase each year with increasing population growth. Expected administrative costs (row 8): is the overhead amount required to run the sports betting operation, this amount is expected to increase each year. Litigation cost expected (row 9): attorneys will need to be hired to defend any oppositions from any anti-gambling groups from the general public, and this amount expects to decrease each year. Inputs (cells C11 to E15) estimates made by CCC for the 3-year forecast, are described below: Average amount of sports bet (row 12): is the cash amount of each bet, CCC hopes this will increase as years go on. Neighbour states introduce gambling? (cell B13): Y' if neighbouring states will introduce gambling, 'N' if not Average inflation rate in 10 years (cell B14): this rate affects the average salary level for sports gambling workers. This single value is assumed to prevail from year 2022 to 2024. Average number of bets (cell B15): is the average number of bets a gambler places each time he or she comes to a racino. This single value is assumed to prevail from year 2022 to 2024. Calculations (row 23 to 30), which calculate intermediate results for the income and cash flow statement later, are described below: Number of actual gamblers per year (row 24): if neighbouring states stay out of gambling, this amount is 10% of possible gamblers in the year. If neighbouring states are expected to enter gambling, this amount is 5% of possible gamblers in the year. Extra workers needed (row 25): A worker is hired for every 1000 actual customers. This must be an integer number as it is impossible to have a fractional number of workers. Average yearly pay per worker (row 26): is a function of previous year's average salary and current year's inflation rate. For example, if the inflation rate is 1%, 2022's average salary will be 1% greater than 2021's. Labour expense per year (row 27): is a function of the number of extra workers needed per year, and the average yearly pay per worker. Sports betting revenue (row 28): is a function of the actual number of gamblers, the average number of bets per gambler, and the average amount of a bet. Sports book agent fee (row 29): The sports book agent takes 10% of sports betting revenue, and there is no base fee. Interest income (row 30): Since CCC's policy is to maintain a minimum cash level in the bank for operation during the year, CCC's treasurer will invest this minimum cash on a short-term basis and earn some interest. The interest rate on a short-term cash investment is 2% less than the interest rate on debt owed. The amount of cash for short-term investment is the larger of two values: 1) the minimum cash level (a constant), 2) half of the cash at the beginning of the year. Income and cash flow statement (row 32 to 53) are described below: Beginning-of-the-year cash on hand (row 33): is the cash at the end of previous year. Sports betting revenue (row 35): is a calculated value that can be echoed here. Interest income (row 36): is a calculated value that can be echoed here. Total revenue (row 37): is the sum of sports betting and interest revenues. Labour expense in a year (row 38): is a calculated value that can be echoed here. Sports book agent fee (row 39): is a calculated value that can be echoed here. Cost of litigation (row 40). is a constant that can be echoed here. Administrative costs (row 41): is a constant that can be echoed here. Depreciation expense (row 42): the $60 million (cell B59) is invested in fixed assets, which . depreciates over next 3 years (2022 to 2024) using the straight-line method, this expense is fixed each year. This means $60 million should be divided by the number of years of investment Total costs (row 43): is the sum of labour expense, sports book agent fee, litigation costs, administrative costs and depreciation expense. Income before interest and tax (row 44): is the difference between total revenue and total costs. Interest expense (row 45): is a function of the debt owed at the start of the year and the annual interest rate on debt. Income before taxes (row 46): is the income before interest and taxes, minus interest expense. Income tax expense (row 47): is zero if income before taxes is zero or less; otherwise, apply the tax rate for the year to the income before taxes. Tax office ignores any cents in income tax expense. Net income after taxes (row 48): is the difference between income before taxes and income tax expense. Net Cash Position (NCP) (row 50): NCP at the end of a year equals the cash beginning of a year, plus that year's net income. Assume that CCC's bankers will lend enough money (row 51) at the end of a year to reach to CCC's minimum cash target (see row 5). If the NCP is less than the minimum cash at the end of a year, CCC 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 your company below the minimum cash level (row 52). Cash at the end of the year equals the NCP, plus any borrowings and less any repayments (row 53). Debt Owed (rows 55 to 59) is described below: Debt owed at the beginning of a year equals the debt owed at the end of the previous year (row 56). Amounts borrowed and repaid that have been calculated before can be echoed to this section (rows 57, 58). The amount owed at the end of a year equals to the debt owed at the beginning of the year plus any borrowings, and less any repayments (row 59). Table 1: 'NA' = Not Applicable, meaning no entry is required in the cell. D E 2023 0.2 A 3 CONSTANTS 4 TAX RATE 5 CASH NEEDED TO START NEXT YEAR 6 INTEREST RATE ON DEBT 7 NUMBER OF POSSIBLE GAMBLERS 8 EXPECTED ADMINISTRATIVE COSTS 9 COST OF LITIGATION EXPECTED 10 11 INPUTS 12 AVERAGE AMOUNT OF SPORTS BET 13 NEIGHBOR STATES ADOPT GAMBLING? 14 AVERAGE INFLATION RATE IN 10 YEARS 15 AVERAGE NUMBER OF BETS 16 B 2021 NA NA NA NA NA NA C 2022 0.2 5000000 0.06 1000000 8000000 2000000 5000000 0.06 =C7+5000 8080000 1500000 E 2024 0.2 5000000 0.06 =D7+5000 8160800 1000000 2023 55 2021 NA Y 0.02 5 2022 55 NA NA NA NA NA NA 2024 55 NA NA NA B D E 2021 2022 2023 2024 NA? (3 marks) copy & paste copy & paste NA ? (1 marks) copy & paste copy & paste 40000 ? (2 marks) copy & paste copy & paste NA ? (1 mark) copy & paste copy & paste NA ? (2 marks) copy & paste copy & paste NA ? (1 mark) copy & paste copy & paste NA ? (4.5 marks) copy & paste copy & paste A 23 CALCULATIONS 24 NUMBER OF ACTUAL CUSTOMERS IN YEAR 25 EXTRA WORKERS NEEDED 26 AVERAGE YEARLY PAY PER WORKER 27 LABOR EXPENSE PER YEAR 28 SPORTS BETTING REVENUE 29 SPORTS BOOK AGENT FEE 30 INTEREST INCOME INCOME STATEMENT AND 32 CASH FLOW STATEMENT 33 BEGINNING-OF-THE-YEAR CASH ON HAND 341 35 SPORTS BETTING REVENUE 36 INTEREST INCOME 37 TOTAL REVENUE 38 LABOR EXPENSE IN YEAR 39 SPORTS BOOK AGENT FEE 40 COST OF LITIGATION 41 ADMINISTRATIVE COSTS 42 DEPRECIATION EXPENSE 43 TOTAL COSTS 44 INCOME BEFORE INTEREST AND TAXES 45 INTEREST EXPENSE 46 INCOME BEFORE TAXES 2021 2022 2023 2024 NA? (0.5 mark) copy & paste copy & paste NA? (0.5 mark) copy & paste copy & paste NA ? (0.5 mark) copy & paste copy & paste NA? (0.5 mark) copy & paste copy & paste NA? (0.5 mark) copy & paste copy & paste NA ? (0.5 mark) copy & paste copy & paste NA? (0.5 mark) copy & paste copy & paste NA ? (0.5 mark) copy & paste copy & paste NA ? (1 mark) copy & paste copy & paste NA ? (1 mark) copy & paste copy & paste NA ? (1 mark) copy & paste copy & paste NA ? (1 mark) copy & paste copy & paste NA ? (1 mark) copy & paste copy & paste NA A B D E 47 INCOME TAX EXPENSE NA ? (2 marks) copy & paste copy & paste 48 NET INCOME AFTER TAXES NA ? (1 mark) copy & paste copy & paste 49 NET CASH POSITION (NCP) BEFORE BORROWINGS AND REPAYMENT OF DEBT 50 (BEG OF YEAR CASH + NET INCOME) ? (1 mark) copy & paste copy & paste 51 ADD: BORROWING FROM STATE NA ? (3 marks) copy & paste copy & paste 52 LESS: REPAYMENT TO STATE NA ? (6 marks) copy & paste copy & paste 53 EQUALS: END-OF-THE-YEAR CASH ON HAND 5000000 ? (1 mark) copy & paste copy & paste 54 55 DEBT OWED 2021 2022 2023 2024 56 BEGINNING-OF-THE-YEAR DEBT OWED NA ? (0.5 mark) copy & paste copy & paste 57 ADD: BORROWING FROM STATE NA ? (0.5 mark) copy & paste copy & paste 58 LESS: REPAYMENT TO STATE NA ? (0.5 mark) copy & paste copy & paste 59 EQUALS: END-OF-THE-YEAR DEBT OWED 60000000 ? (1 mark) copy & paste copy & paste