Please assist with figuring out how to do this. I'm not good with excel.

AutoSave . Off) Project 3 Choice Hotels Workbook 2195 - Excel Walker, Willetta WW X File Home Insert Page Layout Formulas Data Review View Help Search 15 Share Comments & Cut AutoSum Paste [B) Copy Calibri 11 " A" A" ab Wrap Text General Normal Bad Good Neutral AY O BIU~ ~ A FEE Merge & Center ~ Conditional Format as Calculation Format Painter Check Cell Formatting ~ Table Explanatory ... Followed Hy... Insert Delete Format 1 Fill Clear Sort & Find & Ideas Filter ~ Select Clipboard Font Alignment Number Styles Cells Editing Ideas A1 X V B D E F G H K M N O P Q R S T U V Off W X Y Z AA AB AC A W N Choice Hotels 10-K In Project 3, you will learn how to access US Securities and Exchange Commission public information about companies. You will also learn how to calculate and anlyze ratios, analyze and make decisions based on cost, and develop a sales forecast and budget. Start by looking up the 10-K for Choice Hotels (CHH) for year 2018 on the SEC website. Follow these steps: 1. Go to www.SEC.gov. 2. At the top on the right, click Company Filings. Recy 3. In the fast search box, enter the Ticker Symbol for Choice Hotels, CHH. 4. Click Search 5. EDGAR search results will appear. Notice the name and address for Choice Hotels. Also notice the box that reads Filter Results: Filing Type. Enter "10-K" and click Search. 6. You should see a 10-K with a filing date of 2019-02-26. This is the latest available at the time this project was developed. 7. Repeat 1 through 6 for Marriott International (MAR) for year 2018 on the SEC website. You should see a 10-K with a 14 filing date of 2019-03-01. This is the latest available at the time this project was developed. 8. There are two available formats of this 10-K data, and we will use the Documents to answer the questions. You will Walker 16 use the data provided in the worksheets to complete the Ratio Analysis and to answer related questions. 17 9. Complete the financial statements by filling in the Excel formulas for each grey box. 18 10. Answer all questions on each tab in this workbook. to a Note: Quarterly Financial Statements are not audited. Only annual financial statements are audited by a public accounting firm. MCG Attach Instructions Income Statement Balance Sheet Cash Flow Cost and Investing Budget and Forecast + -+ 100% 12:25 PMFile Home Insert Page Layout Formulas Data Review View Help Search 15 Share Comments X Cut This CE Copy 11 ~ A A al Wrap Text General Normal Bad Good Neutral AutoSum ~ Paste APO Format Painter Merge & Center $ ~ % 9 Conditional Format as Calculation sert Delete Format 1 Fill Formatting ~ Table Explanatory ... Followed Hy.. Clear Sort & Find & Ideas Clipboard Filter ~ Select Font Alignment Styles Cells Editing Ideas B44 X V fic 3. Which of the two companies has the financially stronger income statement? Explain your rationale thoroughly. N o Common Size Income Statements Consolidated Statements of Income - USD 12 Months Ended Consolidated Statements of Income - 12 Months Ended (S) USD ($) ($ in millions, except per share REVENUES: Dec. 31, 2018 % of Total revenue Dec. 31, 2017 % of Total revenue Dec. 31, 2016 % of Total revenues Dec. 31, 2018% of Total revenuec. 31, 2017% of Total revenuec. 31, 2010% of Total revenues Royalty fees $ 376,676,000 REVENUES Initial franchise and relicensing fees S 26,072,000 52,088,000 $ 23,038,000 $ 317,699,000 Procurement services $ 40,451,000 $ 19,720,000 Base management fees 1,140 1,102 806 Franchise fees S 1,586 649 Marketing and reservation system $ 499,625,000 $ 409,120,000 Incentive management fees $ 607 425 Other 42,791,000 Gross fee revenues 3,295 2,38 8 Recycle Total revenues $ 1,041,304,000 $ 36,438,000 $ 941,297,000 $ 25,526,000 $ 807,909,000 Contract investment amortization (58 ) (50) (40) OPERATING EXPENSES: Net fee revenues 3,580 S 3,245 2,348 Selling, general and administrative S 170,027,000 $ 165,821,000 $ 154,720,000 Owned, leased, and other revenue $ 1,125 Depreciation and amortization 14,330,000 Marketing and reservation system 534,266,000 $ 6,680,000 Cost reimbursement revenue $ 15,543 $ 11,934 15 Total operating expenses 718,623,000 $ 479,400,000 6,996,000 Total revenues 20,758 20,452 OPERATING COSTS AND EXPENSES $ 15,407 16 $ 651,901,000 $ 459,765,000 Impairment of goodwill (4,289,000) $ 621,481,000 Owned, leased, and other-direct 1,306 1,411 Gain on sale of assets, net 82,000 257,000 627,000 Depreciation, amortization, and other $ 226 229 901 921 119 Operating income $ 289,653,000 General, administrative, and other 927 386 Walker, V 20 OTHER INCOME AND EXPENSES, NET: $ 187,055,000 159 743 Interest expense 45,908,000 (7,452,000) $ 45,039,000 15,778 Interest income $ (5,920,000) Total operating expenses S $ 17,948 $ 11,834 13 ,983 Other (gain) loss Equity in net (income) loss of affiliates 5,323,000 S (3,229,000) (3,535,000) OPERATING INCOME S S 2.504 Total other income and expenses, net $ 40,436,000 (492,000 Gains and other income, net 688 1,424 45,216,000 4,546,000 S 38,915,000 Interest expense (340) (288) (234 ) Income before income taxes Income taxes 56,903,000 $ 249,217,000 $ 126,890,000 $ 148,140,000 Interest income 22 38 quity in earnings 103 40 35 Net income INCOME BEFORE INCOME TAXES 2,982 9 216,355,000 $ 122,327,000 $ 41,428,000 Basic earnings per share $ 106,712,000 Provision for income taxes (438 ) (1,523) 1,239 1,907 (431) Basic earnings per share (in dollars per NET INCOME 1,459 808 29 share 3.83 Diluted earnings per share (in dollars S 2.16 S 1.90 EARNINGS PER SHARE per share) 3.80 2.15 1.89 Earnings per share - basic 5.45 Earnings per share - diluted 5.38 3.8 3.84 S 2.78 Questions: 2.73 1. What are two accounts in the Choice Hotels income statement that show the biggest change over the past 3 years? What information in the 10-K report helps to explain these changes? MCG Sof Cent 38 2. What are two accounts in the Marriott income statement that show the biggest change over the past 3 years? What information in the 10-K report helps to explain these changes? Attachm 3. Which of the two companies has the financially stronger income statement? Explain your rationale thoroughly. Attachma Instructions Income Statement Balance Sheet Cash Flow Cost and Investing Budget and Forecast +G M Common Size Balance Sheets 12 Months Ended Consolidated Balance Sheets - USD ($] Dec. 31. 201% of Total assets Dec. 31. 201 % of Total asset Common Size Balance Sheets 12 Months Ended $ in Thousands Consolidated Balance Sheets - USD Dec. 31. 2 & of Total assets Dec. 31. 2 8 of Total assets Current assets ($) $ in Millions Cash and cash equivalents 26,642 Current assets 235,336 Receivables [net of allowance for doubtful Cash and equivalents 316 383 accounts of $15,905 and $12,221, respectively) 138,018 125.870 Income taxes receivable 10.122 Accounts and notes receivable, net 2,135 1,973 9 10 Notes receivable, net of allowances Prepaid expenses and other 249 36,753 13,256 235 Assets held for sale 149 11 Other current assets 8 12 Total current assets 32,243 243,784 25,967 Total current assets 400,423 Property and equipment, net $ 2,706 2,740 1,793 13 Property and equipment, at cost, net 1,356 27.535 $3,374 Goodwill 168,996 Intangible assets 14 80,757 15 Intangible assets, net 271,188 00,492 Brands Contract acquisition costs and other 3.190 Notes receivable, net of allowances $ 2,590 5,922 63,440 80,136 Goodwill $ 9,039 $ 2,622 16 Investments, employee benefit plans, at fair value 9,207 17 3,338 Investments in unconsolidated entities 20,838 109,016 34,226 Total long-term asset $ 17,419 17,751 18 19 Deferred income taxes 30,613 27,224 Equity method investments 732 54,400 Notes receivable, net 734 Other ssects 67,715 125 142 20 21 Total assets Deferred tax assets 171 93 Current liabilities $ 1,138,370 895,191 Other noncurrent assets 587 593 Total assets $ 23,696 23 Accounts payable Accrued expenses and other current liabilities 73,511 92,651 67,839 34,315 Current liabilities $ 23,846 24 25 Defered Revenue 67.614 52,142 Current portion of long-term debt 833 Liability for quest loyalty program 83,566 Accounts payable 398 767 79,123 783 26 Current portion of long-term debt 345 1,097 1,232 Accrued payroll and benefits 1,214 27 Liability for quest loyalty program 2,523 28 Total current liabilities $18,439 284,651 Accrued expenses and other 963 2,121 Long-term debt 53,514 725,292 1,291 Long-term portion 10,278 98,459 Total current liabilities Long-term debt .437 5,807 Deferred compensation and retirement plan 8,514 7,840 30 obligations 24,212 Income taxes payable 26.276 25,566 29,041 Liability for quest loyalty program Deferred tax liabilities *2,932 485 2,813 32 33 Deferred income taxes 605 39 34 Liability for quest loyalty program 52,327 Deferred revenue 731 583 46,101 Other noncurrent liabilities 2,372 2,610 35 Other liabilities Total liabilities 37,096 42,043 Shareholders' equity Commitments and Contingencies $ 1,322,142 $ 1,253,792 Class A Common Stock 5 Common stock, $0.01 par value; 160,000,000 Additional paid-in-capital 5,814 $ 5,770 shares authorized; 95,065,638 shares issued at December 31, 2018 and December 31, 2017; 55,679,207 and 56,679,968 shares outstanding at December 31, 2018 and December 31, 2017 Additional paid-in-capital 95 951 Retained earnings Accumulated other comprehensive loss 213,170 182,448 $ 8,982 $ 7,242 Treasury stock, at cost; 39,386,431 and [5.446) [4.699) Treasury stock, at cost Accumulated other comprehensive loss $ [12.185) (391)] $ [3,418) 38,385,670 shares at December 31, 2018 and 40 December 31, 2017, respectively 41 Retained earnings $ [1,187,625) $ [1,064,573) Total shareholders' equity $ 2,225 $ 3,582 42 43 Total shareholders' deficit $ 795,178 Total liabilities and shareholders" (183,772) $ 627,272 Total liabilities and shareholders" [258,601 $ 23,696 $ 23,846 44 $ 1,138,370 995,191 Questions: 46 47 48 I. What are two accounts in the Choice Hotels balance sheet that show the biggest change over the past 2 years? What information in the 10-K report helps to explain these changes 49 50 51 52 53 2. What are two accounts in the Marriott balance sheet that show the biggest change over the past 2 years? What information in the 10-K report helps to explain these changes? 54 55 56 57 58 3. Which of the two companies has the financially stronger balance sheet? Explain your rationale thoroughly. 59AutoSave . Off - @ 53 Project 3 Choice Hotels Workbook 2195 - Excel File Home Insert Page Layout Formulas Data Review View Help Search K Cut [ Copy Calibri 11 - A A 29 Wrap Text General Normal Bad Paste Format Painter = = = = = Merge & Center * $ ~ % 9 08 30 Conditional Format as Calculation Check Ce Formatting * Table Clipboard G Font Alignment Number Style AU78 X V fc OPERATING ACTIVITIES OPERATING ACTIVITIES In girl mask provided by uprealion +216,355, 101 14,321, 101 11,542 101 365,101 Herserieslated shares wwack islerrul and alker 15,216, LOL 22, 152, 101 Excily in url lowers from 1,125, 101 Mel mack presided by cerealies avlisilice change in working sagilal and alber. ISTESTING ACTIVITIES Hel wash presided by speralias CASH FLOWS FROM 242, 035, 10 Capital reprodileres TIME ACTIVITIES Freeeris from sales of everly Helloout Jourd ic provided by fouralimy an livilice FINANCING ACTIVITIES words from sales of Repapered of lourdlere dell 2,025 010 Dividendoraid Cashin alinilice .324,252 101 FINANCING ACTIVITIES DECREASE IN CASH, CASH EQUIVALENTS, AND RESTRICTED CA $ 1631 Frissipal pagerule so long term -145,103, 010 25,235, 010 CASH, CASH EQUIVALENTS, AND RESTRICTED CASH, braingiant $ 423 ASH, CASH EQUIVALENTS, AND RESTRICTED CASH, red of pret + 161 24,232, 101 12,151, 212,273,100 Cock and wack ryrivalrules 212,463, 10 26, 642, 101 235,396, 100 212,463, 101 Dividends declared bal and raid 12,142, 101 Sale of isuralural is seems 1,133,101 Instructions Income Statement Balance Sheet Cash Flow Cost and Investing | Budget and Forecast +AutoSave . Off - Project 3 Choice Hotels Workbook 2195 - Excel Walker, Willetta WW X File Home Insert Page Layout Formulas Data Review View Help Share Comments X Cut [A Copy Calibri 11 ~ A A ab Wrap Text Number Normal Bad Good Neutral AutoSum ~ AY Fill O Paste Insert Delete Format Ideas Format Painter BIU~ ~ MAv = =E E E Merge & Center ~ $ ~ % 9 68 28 Conditional Format as Calculation Check Cell Explanatory ... Followed Hy. Sort & Find & Formatting Table v Clear Filter ~ Select v Clipboard Font Alignment Number Styles Cells Editing Ideas H35 X V fox =E35+F35+G35 E H K M N O Q R S T Choice Hotels has contracted with a mid-size furniture manufacturer for the production of guestroom furniture Choice Hotels Sales, Production, and Cost Information Overhead Costs W N for three models of guest rooms: the standard guest room, Junior Suite, and Presidential Suite. Room Type Standard Presidential Guest Room Junior Suite Type Cost Suite The Standard Guest Room comes with basic furniture, bathroom plan, and amenities. It sells for $140,000 to olume 150 110 25 Depreciation $3,200,000 franchise hotels. Price $140,000 $240,000 $1,050,000 Maintenance $1,800,000 The Junior Suite model is larger and includes an enhanced furniture selection, upgraded bathroom fixtures, more Unit costs Purchasing $320,000 comfortable bedding. The guest room is considered an upgrade from the standard guestroom model. The Junior Direct materials $30,000 $92,000 $310,000 Inspection $850,000 Suite sells for $240,000 to franchise hotels. Direct labor $54,000 $85,000 $640,000 Indirect materials $490,000 The Presidential Suite model is a custom-made guest room with floors and walls constructed from specialty 8 wood. The drapes are made from the traditional flax-based canvass. It has the look and feel of a room in the Manufacturing $30,000 $30,000 $30,000 Supervision $1,700,000 9 White House, with modern comforts and security. The Presidential Suite sells for $1,050,000 to franchise hotels. Workers who build the Presidential Suite are specialized craftsmen. They earn twice the hourly rate of those overhead Supplies $190,000 working on the Standard Guest Room and Junior Suite models. The labor rate is fully burdened to include Total benefits. Total unit cost $114,000 $207,000 $980,000 manufacturing $8,550,000 10 Most of Choice Hotels' guest room sales come from the Standard Guest Room and the Junior Suite, but sales of overhead cost the Presidential Suite model have been growing. The company's sales, production, and cost information for last Unit gross $26,000 Note: Manufacturing overhead 11 year is provided to the right. profit $33,000 $70,000 costs are fixed. They do not vary Direct labor 12 hours 1,200 1,300 5,940 with the volume of manufacturing activity. 13 Rate per hour $45.00 $65.38 $107.74 14 15 16 Questions: Answer Questions 1 and 2 Below: 17 1. The cost-allocation system Choice Hotels has been using allocates over 90 percent of overhead costs to the 18 Standard Guest Room and the Junior Suite, because over 90 percent of the models produced were one of these 19 two models. How much overhead was allocated to each of the three models last year? Discuss why this might 20 not be an accurate way to assign overhead costs to products 21 22 2. Choice Hotels' production manager proposes allocating overhead by direct labor hours instead, since the 23 different models require different amounts of labor. How much overhead would be allocated to each guest room 24 (per unit and in total) using this method? Show all supporting calculations. 25 26 27 28 Instructions Income Statement Balance Sheet Cash Flow Cost and Investing Budget and Forecast + 100%AutoSave Off Project 3 Choice Hotels Workbook_2195 - Excel Walker, Willetta WW File Home Insert Page Layout Formulas a Review View Help Search Share Co LO & Cut Calibri * 11 ~ A" A " E ab Wrap Text Number Normal Bad Good Neutral 41 2 AutoSum AY O Paste [B Copy ~ Format Painter BIU~ ~ A~ = = E Merge & Center $ ~ % 9 08 20 Conditional Format as Calculation Check Cell Explanatory ... Followed Hy. nsert Delete Format Fill Sort & Find & Ideas Formatting ~ Table > Clear Filter ~ Select Clipboard Font Alignment Number Styles Cells Editing Ideas H3 X =E35+F35+G35 B C E F G H M N O P Q R S U 30 31 32 Choice Hotels' controller developed the following data for use in activity-based costing: Complete the calculations to help you answer the questions below Answer Questions 3 to 10 Below: 33 Manufacturing overhead Cost driver Standard Presidential Cost per cost cost per Cost per Amount Sum of Cost Cost per 34 Guest Room Junior Suite Suite Drivers driver Standard Junior Suite Presidential Check Guest room Suite 35 36 Depreciation 3,200,000| Square feet 50,000 30,000 30,000 110000 S 29.09 $ 872,727.27 $ 872,727.27 37 38 Maintenance $1,800,000 Direct labor 143,000 148,500 39 hours 180,000 40 # of Purchasing $320,000 purchase 2,500 1,500 9,000 orders 43 Inspection # of 44 $850,000 inspections 1,000 850 3,500 45 Indirect Units materials $490,000 manufacture 150 110 25 46 d Supervision # of 850 47 $1,700,000 inspections 1.000 3,500 48 Supplies $ 190,000 Units manufacture 150 110 25 Total $8,550,000 234,800 176,420 194,550 52 53 Questions: 54 3. Use activity-based costing to allocate the costs of overhead per unit and in total to each guest room type. Show all supporting calculations in the space provided to the right. 55 56 4. Calculate the cost of one Presidential Suite using activity-based costing 58 5. At the current selling price, is the company covering its true cost of production of the Presidential Suite? Briefly discuss. 59 60 6. What should price should Choice Hotels charges for the Presidential Suite? 61 7. Assume that the Presidential Suite has the same profit margin as the standard guest room. What should its selling price be? Show all calculations. 62 63 8. What should Choice Hotels do if the quantity of the Presidential Suite Guest Rooms sold at the new price falls to 10 per year? 65 9. What should Choice Hotels do if the price of the Presidential Suite cannot exceed $1,050,000? 66 67 10. At a selling price of $1,050,000 each, what is the breakeven unit volume for the Presidential Suite? Instructions Income Statement Balance Sheet Cash Flow Cost and Investing Budget and Forecast +AutoSave (C off) Project 3 Choice Hotels Workbo File Home Insert Page Layout Formulas Data Review View Help Search & Cut [B Copy Calibri 11 AA 29 Wrap Text General Norm Paste Format Painter BIU~ ~ MAY CE E Merge & Center $ * % 9 68 28 Conditional Format as Calcul Formatting * Table Clipboard Font Alignment Number T61 X V M N P In a February 15, 2019 Press Release. Choice Hotels announced the company's 2018 fourth quarter and full year results. Using the data from this press release, create a 2019 budget and forecast. http:/media.choicehotels.com/2019-02-15-Choice-Hotels-International-Exceeds-Top-End-Of-Full-Year-Guidance-For-EPS-And-Reports-Largest-Domestic-Pipeline-In-Companys-History? printable=] To complete the budget, use the following information: are expected to grow at a rate of 2.5% according to the full-year outlook. Given the expected growth and recent investments, expenses are expected to increase by 1%. ncome takes are expected to be 22% To complete the forecast. use the following information: The low-range forecast is expected to be 2% The mid-range forecast is expected to be 2.5% The high-range forecast is expected to be 3% 17 Budget Forecast Forecast 18 Low Midpoint Forecast 19 Consolidated Statements of Inc. Dec. 31. 2018 Dec. 31. 2 Dec. 31. 2019 Dec. 31. 2019 Dec. 31. 2019 High REVENUES 20 Royalty fees 21 *376,676,000 22 Initial franchise and relicensing fees 26,072,000 23 Procurement services Marketing and reservation system 52,088,000 24 543.677,000 25 Other 42,791,000 26 Total revenues OPERATING EXPENSES: $ 1,041,304,000 28 Selling, general and administrative 170,027,000 Depreciation and amortization Marketing and reservation system 14.330,000 ########## $ 191,396,093.56 $193.798.554.98 $ 15.928.521.64 $ 16.400.000.00 # 16.810,000.00 30 31 Total operating expenses 534.266,000 Impairment of goodwill 718,623,000 ########## $ 601,412,865.73 $ 608.961,981.20 ########## $ 808,939.962.13 |$819.094.020.23 32 [4.289,000) Gain on sale of assets, net 82,000 $ (4.767.440.98) $ [4.828,044.05) # (4.888.647.11) Operating income 91,147.16 92,305.81 $ 93.464.46 34 35 OTHER INCOME AND EXPENSES, NET: 318,474,000 ########## $358.500.000.00 |########## Interest expense 45,908,000 36 Interest income (7,452,000) $ 51,029.069.88 $ 45,800,000.00 # 52,326.419.11 37 Other [gain) loss $ [8.283.275.87) $ (7.600,000.00) $ (8.493.867.63] 38 1.437.000 1,637.907.65 39 Equity in net (income) loss of affiliates | $ Total other income and expenses, net $ 5,323,000 $ 1,597.298.37 $ 46.700.000.00 $ 45,216,000 5,916.784.42 $ 3,900,000.00 $ 6,067 211.14 40 $50,259.876.79 $ 50.898.773.53 $ 51537.670.27 41 Income before income taxes $ 273.258.000 Income taxes ########## $ 307.601.226.47 $ 311,462,329.73 42 56,903,000 43 Net income Basic earnings per share: 216,355,000 $ 63,250,569.91 $ 56,200,000.00 $ 64.858,635.24 # ######### $ 197.100,000.00 $201,000,000.00 44 45 Basic earnings per share (in dollars per = $ 3.83 Diluted earnings per share (in dollars per $ 3.80 4.00 3.44 $ 4.06 4.06 4.13 3.58 46 48 Questions: Target Expectation Expectation Expectation 49 50 51 . Which revenue category is the most important to forecast accurately? Explain your rationale for your selection and how you developed your three estimates thoroughly . 52 53 54 55 56 2. Which expense category is the most important to forecast accurately?' Explain your rationale for your selection and how you developed your three estimates thoroughly . 57 58 59 60 61 3. Explain thoroughly how you developed your budget estimates for all revenue and operating expense accounts. 62 63 64 Instructions Income Statement Balance Sheet Cash Flow Cost and Investing Budget and Forecast +