Section B (40 marks) Refer to Table I. Write the Excel formula for each sell marked with 7" in column C such that formul

Business, Finance, Economics, Accounting, Operations Management, Computer Science, Electrical Engineering, Mechanical Engineering, Civil Engineering, Chemical Engineering, Algebra, Precalculus, Statistics and Probabilty, Advanced Math, Physics, Chemistry, Biology, Nursing, Psychology, Certifications, Tests, Prep, and more.
Post Reply
answerhappygod
Site Admin
Posts: 899604
Joined: Mon Aug 02, 2021 8:13 am

Section B (40 marks) Refer to Table I. Write the Excel formula for each sell marked with 7" in column C such that formul

Post by answerhappygod »

Section B 40 Marks Refer To Table I Write The Excel Formula For Each Sell Marked With 7 In Column C Such That Formul 1
Section B 40 Marks Refer To Table I Write The Excel Formula For Each Sell Marked With 7 In Column C Such That Formul 1 (56.37 KiB) Viewed 59 times
Section B 40 Marks Refer To Table I Write The Excel Formula For Each Sell Marked With 7 In Column C Such That Formul 2
Section B 40 Marks Refer To Table I Write The Excel Formula For Each Sell Marked With 7 In Column C Such That Formul 2 (47.68 KiB) Viewed 59 times
Section B 40 Marks Refer To Table I Write The Excel Formula For Each Sell Marked With 7 In Column C Such That Formul 3
Section B 40 Marks Refer To Table I Write The Excel Formula For Each Sell Marked With 7 In Column C Such That Formul 3 (65.73 KiB) Viewed 59 times
Section B 40 Marks Refer To Table I Write The Excel Formula For Each Sell Marked With 7 In Column C Such That Formul 4
Section B 40 Marks Refer To Table I Write The Excel Formula For Each Sell Marked With 7 In Column C Such That Formul 4 (45.43 KiB) Viewed 59 times
Section B 40 Marks Refer To Table I Write The Excel Formula For Each Sell Marked With 7 In Column C Such That Formul 5
Section B 40 Marks Refer To Table I Write The Excel Formula For Each Sell Marked With 7 In Column C Such That Formul 5 (81 KiB) Viewed 59 times
Section B (40 marks) Refer to Table I. Write the Excel formula for each sell marked with 7" 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 Exed formula for cells marked with "Copy & paste". Label cach formula clearly with cell reference position, COC is a state in the United States. CCC has a gambling program that is run by the CCC lottery office. Currently, people go to any of CCC's four bone 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 that 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 racines 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 COCY authorities are considering expanding their gambling program to collect more revenue by introducing gambling on sports events such as soccer, haschall, 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 conte (estimated to be $60 million in cell B59) to expand the racines, and COC 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. "Sportsbook 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. Sportsbook 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 10 2024) based on 2021% dute. You are asked to help CCC and write Excel formulas in cells C23 C59 (see table 1) for this forecast by performing a what if analysis using Microsoft Excel Constants Crows 3.609) are described below • Tax rate (row 4); is 20% cach year en 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 cach year, in order to start next year's business. It is usumed that COC's bunker will lend whatever is needed at the end of a year to begin the next year with US$5,000,000 • Interest rate an debe (row 6); is 0% simple interest each year as CCC bomow money for the new sports gambling lounge and sports betting staff Number of possible gamblers (now Ty surveys have established how many sports gamblers there might be each year, this number is expected to increase cach year with increasing population growth • Expected administrative costs (row $) 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 many oppositions from any anti-gambling groups from the general public, and this amount expects to decrease ch, h và

Input: (osis CHL 10 E19) estimates made by the stars are described below. • Average amount of sports ber (row 12 is the cand amount of cach bet, CCC hopes this will increase as years go on • Neighbour states introduce gambling? (cell B13% Til neighbouring states will introduce gambling. N'if not • Average inflation rate in 10 years (cell B14 disme affects the average salary level for sports gambling workers. This single aule is not prevail from year 2022 to 2024. • Average number of bets (cell B15 is the verge umber of bets a gambler places cach time he or she comes to a racine. This singles armed to prevail from year 2022 to 2024 Calestations from 23.1030) which calculate intermediate for the income and cash flow statement later. As described • Number of actual gamblers per year (now 21 if neighbouring states stay out of gambling this amount is 10% of possible gamblers in the year neighbouring states are expected to enter gambling, this amount is 5% of possible gamblers in the year. . Extra workers needed frow 25): A workers 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 25) is a function of previous year's average salary and Current year's inflation rate. For example, if the inflatie mate is 16, 2012's average salary will be 1% greater than 2021 Labour expense per year (now 27%. is a fimctice of the member of extra workers needed per year, and the average yearly pay per worker • Sports betting revenue grow 283: is a function of the actual number of gamblers, the average number of bets per gambler, and the average • Sports book agent fee (row 29): The sportsbook agent takes 10% of sports betting revenue. and there is no base fee • Interest income row 30: Since CCC) policy is to maintain a minimum cash level in the bank for operation during the year, CO will invest this minimum cahona short-term basis and car 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 cab level (a constant), 2) half of the cash at the beginning of the year Income and cash flow statement from 3220 $5) are described den • Beginning-of-the-year cash on hand ( 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 (rew 36 is a calculated value that can be echoed here • Total revence (row 37): is the sum of sports being and interest revenues • Labour expense in a year frow 38) is a caldated value that can be echoed bere • Sportsbook agent fee (row 39 is a calculated that can be echoed here. Cost of litigation (row 40) is a constant that can be echoed here. • Administrative costs (row41is a constant that can be echoed here Depreciation expense row the 500 million cells) 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.

B B 2021 NA NA NA NA А 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 2022 0.2 5000000 0.06 1000000 8000000 2000000 D 2023 0.2 5000000 0.06 =C7+5000 8080000 1500000 E 2024 0.2 5000000 0.06 =D745000 8160800 1000000 NA NA 2022 55 2024 55 2021 NA Y 0.02 5 2023 55 NA NA NA NA NA NA NA NA NA

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 2023 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 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 D B 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) NA ? (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 ? 10.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
Join a community of subject matter experts. Register for FREE to view solutions, replies, and use search function. Request answer by replying!
Top
Post Reply

Return to “Archived Questions & Answers”