MUST BE AN EXPERT IN EXCEL!!!
please let me know if you have any questions and after reviewing the assignment let me know if i am missing any documents.
Choice Hotels 10-Q In Project 2, you will learn how to access U S Securities and Exchange Commission public information about companies. We will also start with ratio calculations. Some ratios can be useful using quarterly financial statements, and some will need annual financial statements. We will start with quarterly statements, the 10-Q. Start by looking up the 10-Q for Choice Hotels (CHH) for quarter 2, 2018 on the SEC website. Follow these steps: Go to WWW.SEC.GOV At the top on the right, click on COMPANY FILINGS In the Fast Search box, enter the Ticker Symbol for Choice Hotels, CHH You can find the ticker symbol for any company in Yahoo Finance or other financial sites. Click Search 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-Q Click Search You should see a 10-Q with a filing date in October 2018 and another with a filing date of 2018-08-08. This is the latest available at the time this project was developed. There are 2 available formats of this 10-Q data, and we will use both. Use Documents to answer the qualitative/word questions. Use Interactive Data to calculate quantitative questions, percentages, and ratios. To prepare for this assignment, download the Choice Hotels 2018-08-08 10-Q Interactive Data. Click on Interactive Data. See: “View Filing Data. Choice Hotels International. Print Document and View Excel Document.” Click on View Excel Document. Open the file and save the file as Your Name Choice 10Q July 2018. Next, open the Documents, See a list of 4 documents and click on the red chh-10q06302018.htm next to the caption 10-Q. We will use this document for question two. For your interest: Quarterly Financial Statements are not audited. Only annual financial statements are audited by a public accounting firm. Incorrect information may still be a felony for management.
|Consolidated Statements of Income – USD ($) $ in Thousands||3 Months Ended||6 Months Ended|
|Jun. 30, 2018||Jun. 30, 2017||Jun. 30, 2018||Jun. 30, 2017|
|Royalty fees||$ 103,219||$ 91,599||$ 179,917||$ 159,893||12.5%|
|Initial franchise and relicensing fees||6,481||5,728||12,695||11,534|
|Marketing and reservation system||157,347||140,477||264,348||239,330|
|Selling, general and administrative||46,270||44,038||87,134||77,783|
|Depreciation and amortization||3,669||1,659||6,722||3,385|
|Marketing and reservation system||136,568||128,780||255,796||236,774||73.2%|
|Total operating expenses||186,507||174,477||349,652||317,942|
|Gain on sale of land and building, net||82||0||82||0|
|OTHER INCOME AND EXPENSES, NET|
|Equity in net (income) loss of affiliates||(567)||859||5,401||2,939|
|Total other income and expenses, net||8,992||10,125||24,780||21,249|
|Income before income taxes||100,024||76,414||130,485||110,693||17.9%|
|Net income||$ 79,839||$ 50,685||$ 104,925||$ 74,954||40.0%|
|Basic earnings per share (in dollars per share)||$ 1.41||$ 0.90||$ 1.85||$ 1.33|
|Diluted earnings per share (in dollars per share)||1.40||0.89||1.83||1.32|
|Cash dividends declared per share (in dollars per share)||$ 0.215||$ 0.215||$ 0.43||$ 0.43|
Please copy and paste all of the sheet titled Consolidated Statements of Income to the right of the questions here. Use this data to answer the following questions. We will use only the six-month ended data. Some questions will require you to use the 10-Q document. Note that all dollar amounts are in thousands of dollars. So a net income of $104,925 really means $104,925,000. We don’t need the last three zeros.
1. In US dollars ($), how much did total revenue increase from the 6 months ended June 30, 2017 to the 6 months ended June 30, 2018? We will use only the six-month figures. What is the percent change? You can copy and paste numbers or click on the cell and type = and then click on the number you need.
2a. In the revenues for 2018, what is the amount for marketing and reservation system? In operating expense, what is the amount for arketing and reservation system?
2b. In past years, the revenue from marketing and reservation system was equal to the marketing and reservation operating expense. Now open the Documents 10-Q Data. There is a heading title “Recently Adopted Accounting Standards” on page 7. On page 8 there is a paragraph that starts “Topic 606 also impacted the Company’s accounting for surpluses and deficits generated from marketing and reservation system activities.” Has the company earned a profit in past from marketing and reservation system? Does it intend to do so in the future?
NOTE: This is how financial analysts use SEC documents. They look at numbers and look for an explanation in the words. There is more explanation on page 11 in the footnote that begins “Marketing and reservation system expenses are those expenses incurred to facilitate the delivery of marketing and reservation system service.”
4. Going back to your Excel file income statement, what is the dollar increase in operating income? What is the percent increase?
5. What is the increase in dollars ($) and percent (%) for income before income tax? Please create your own format for your answer.
6. What is the increase or decrease in dollars and percent for income tax?
7. What is the increase in dollars and percent for net income?
8. Looking at your work above, what contributed to the increase in net income?
9. Calculate profit margin in percent for both years. Profit margin is defined as net income / total revenues. The typical company has a profit margin near 10 percent.
10. Management accounting uses data differently from GAAP accounting and this can be included in the 10-Q or 10-K. Management would like to know what is the profit margin in percent if we exclude the revenue for marketing and reservation system. Please calculate these numbers for June 30, 2018 and 2017.
11. On page 37 of the 10-Q written words, there is a heading, “ITEM 2. MANAGEMENT’S DISCUSSION AND ANALYSIS OF FINANCIAL CONDITION AND RESULTS OF OPERATIONS.” (MD&A) What happened on February 1, 2018?
12. On page 37, Profitable Growth, what does management think its success is dependent on?
13. On page 37 and continuing onto page 38, Maximizing Financial Returns and Creating Value for Shareholders, historically, how has Choice returned value to its shareholders?
14. Based on your experience as a financial analyst, why is net income up?
15. Read all of page 37 and the first half of 38, and tell how much of Choice revenue comes from owned hotels?
16. How much was the June 2018 equity in net (income) loss?
Consolidated Statements of Income – USD ($) in thousands
“Topic 606 also impacted the Company’s accounting for surpluses and deficits generated from marketing and reservation system activities. The Company has historically, consistent with its existing agreements, not earned a profit or generated a loss from marketing and reservation activities, and as a result, the Company recorded excess marketing and reservation system revenues or expenses as assets or liabilities on the Company’s balance sheet prior to the adoption of Topic 606. However, as a result of the adoption of Topic 606, the Company will no longer defer revenues and expenses or record assets and liabilities when system revenues exceed expenses in the current period or vice versa. The Company intends to manage these activities to break-even over time but anticipates that net income or loss may be generated quarterly due to the seasonal nature of the hotel industry and annually based on the level of investments needed for new initiatives that benefit our franchisees.”
3. Going back to your Excel file income statement, what is the dollar increase in total operating expenses? What is the percent increase?
|Consolidated Balance Sheets – USD ($) $ in Thousands||Jun. 30, 2018||Dec. 31, 2017|
|Cash and cash equivalents||$ 37,148||$ 235,336|
|Receivables (net of allowance for doubtful accounts of $14,087 and $12,221, respectively)||186,034||125,870|
|Income taxes receivable||50||0|
|Notes receivable, net of allowance||29,237||13,256|
|Other current assets||30,683||25,967|
|Total current assets||283,152||400,429|
|Property and equipment, at cost, net||112,567||83,374|
|Intangible assets, net||248,469||100,492|
|Notes receivable, net of allowances||78,921||80,136|
|Investments, employee benefit plans, at fair value||20,349||20,838|
|Investments in unconsolidated entities||133,478||134,226|
|Deferred income taxes||23,310||27,224|
|Accrued expenses and other current liabilities||73,940||84,315|
|Current portion of long-term debt||1,099||1,232|
|Liability for guest loyalty program||81,178||79,123|
|Total current liabilities||286,673||284,651|
|Long-term deferred revenue||103,754||98,459|
|Deferred compensation and retirement plan obligations||24,866||25,566|
|Income taxes payable||29,041||29,041|
|Deferred income taxes||0||39|
|Liability for guest loyalty program||48,592||48,701|
|Commitments and Contingencies|
|Common stock, $0.01 par value, 160,000,000 shares authorized; 95,065,638 shares issued at June 30, 2018 and December 31, 2017 and 56,633,606 and 56,679,968 shares outstanding at June 30, 2018 and December 31, 2017, respectively||951||951|
|Accumulated other comprehensive loss||(5,282)||(4,699)|
|Treasury stock (38,432,032 and 38,385,670 shares at June 30, 2018 and December 31, 2017, respectively), at cost||(1,112,376)||(1,064,573)|
|Total shareholders’ deficit||(203,952)||(258,601)|
|Total liabilities and shareholders’ deficit||$ 1,123,016||$ 995,191|
Please copy and paste all of the sheet titled Consolidated Balance Sheets to the right of the questions here. Use this data to answer the following questions. We will use only the six-month ended data. Some questions will require you to use the 10-Q Document. You should see that the balance sheets are only for June 30, not March 31.
1. Does total assets equal total liabilities and shareholders’ Ddficit? What are the amounts of each in 2018?
2. Look at the line items in shareholders’ deficit. Which items are negative? How much is ”accumulated other comprehensive loss”? How much is the negative amount in treasury stock?
3. Now look at the words in the document section. Footnote 7 on page 17 explains the two items in “Accumulated Other Comprehensive Loss.” What are they?
4. On Page 21, how much money did the company spend on buying stock for the treasury stock purchase program in the first 6 months of 2018? On Page 37 and 38 management explains why they buy stock for treasury stock. Why do they do it?
5. Here is a statement from page 5 of the business section of the Annual Report 10-K (not the 10-Q that you copied) “The Company commenced paying quarterly dividends in 2004 and in 2012 the Company elected to pay a special cash dividend totaling approximately $600 million. The Company currently maintains the payment of a quarterly dividend on its common shares outstanding; however the declaration of future dividends is subject to the discretion of the board of directors. The annual dividend in 2017 was $0.86 per share. We expect to continue to pay dividends in the future, subject to quarterly declaration by our board of directors as well as future business performance, economic conditions, changes in income tax regulations and other factors.” How big is $600 million compared to the total amount on the June 30, 2018 balance sheet for total shareholder deficit? Why do you think that the company paid the special cash dividend? Hint, management told us the answer and you commented on it above.
6. Can you find any record of any inventory? If not, why not?
7. What is “Notes Receivables, net of allowances”? There are notes receivable in current assets and notes receivable in long-term assets. How much were they it in June 2018? Here is a statement that describes the reason for equity investments and financing. Choice loans money to hotel owners and records the loans as “Notes Receivables.” “Our direct lodging property real estate exposure is limited to activity in the United States and consists of three parcels of real estate that the Company has acquired and intends to resell to incent franchise development in strategic markets or to pursue hotel development through joint ventures. In addition, our development activities that involve financing, equity investments and guaranty support to hotel developers create limited additional exposure to the real estate markets.”
8. In Footnote 5 on page 16, management tells why it makes investments in Investments in unconsolidated entities. Why did management make these investments? How much was the investment at the end of June 2018?
9. On Page 43, how many hotels and hotel rooms were under franchise at the end of June 30, 2018? Also on page 43, how many hotels and rooms were under construction as of June 30, 2018?
10. Now that you understand the basic facts of Choice Hotels, we will calculate balance sheet ratios. Most finance textbooks start with the current ratio and the quick ratio. These ratios were designed for manufacturing companies, not service companies like Choice Hotels. And they are important for companies starting out in business. For Choice Hotels, there is no inventory so the ratios will be the same: current ratio = current assets / current liabilities; quick ratio = (current assets minus inventory) / current liabilities. Please calculate the current ratio for both 2017 and 2018 balance sheets.
11. The debt to total assets ratio is defined many different ways. In one definition, debt includes only liabilities on which the company pays interest. For Choice hotels this will include long-term debt and current portion of long-term debt. Calculate the debt ratio for June 30, 2018 and December 31, 2017.
12. Before companies bought large amounts of their own stock for treasury stock, fnancial analysts would calculate the debt to equity ratio. Since the balance sheet of Choice Hotels shows negative equity, this ratio makes no sense. In recent years, analysts have looked at the total stock market value of the company. As of September 28, 2018 according to Yahoo Finance, Choice Hotels has a total stock value called market cap (capitalization) of $4.7 billion. The total equity on the financial statements as of June 30, 2018 was a negative $204 million. Calculate the debt to equity ratio using $4.7 billion for equity.
13. Another calculation that is important for manufacturing companies is working capital. Of course, Choice Hotels manufactures nothing. But this will be good to know. Working capital is current assets minus current liabilities. Calculate working capital for Choice for June 30, 2018 and December 31, 2017.
14. Choice has unusual liabilities. One is the liability for the guest loyalty program. What is that for?
Consolidated Balance Sheets – USD ($) in thousands
Cash Flow Statement
|Consolidated Statements of Cash Flows – USD ($) $ in Thousands||6 Months Ended|
|Jun. 30, 2018||Jun. 30, 2017|
|CASH FLOWS FROM OPERATING ACTIVITIES:|
|Net income||$ 104,925||$ 74,954|
|Adjustments to reconcile net income to net cash provided by operating activities:|
|Depreciation and amortization||6,722||3,385|
|Depreciation and amortization – marketing and reservation system||10,048||10,157|
|Franchise agreement acquisition cost amortization||4,375||3,305|
|Loss (gain) on disposal of assets||(82)||4|
|Provision for bad debts, net||4,356||1,707|
|Non-cash stock compensation and other charges||7,716||8,082|
|Non-cash interest and other (income) loss||808||(274)|
|Deferred income taxes||3,828||(732)|
|Equity in net losses from unconsolidated joint ventures, less distributions received||6,702||3,543|
|Franchise agreement acquisition cost, net of reimbursements||(20,326)||(14,108)|
|Change in working capital and other, net of acquisition||(65,258)||(25,915)|
|Net cash provided by operating activities||63,814||64,108|
|CASH FLOWS FROM INVESTING ACTIVITIES:|
|Investment in property and equipment||(21,611)||(10,687)|
|Investment in intangible assets||(1,329)||(2,228)|
|Proceeds from sales of assets||3,052||0|
|Business acquisition, net of cash acquired||(231,317)||0|
|Contributions to equity method investments||(7,206)||(42,127)|
|Distributions from equity method investments||1,210||1,696|
|Purchases of investments, employee benefit plans||(2,047)||(1,736)|
|Proceeds from sales of investments, employee benefit plans||1,828||2,094|
|Issuance of mezzanine and other notes receivable||(19,005)||(14,977)|
|Collections of mezzanine and other notes receivable||3,505||552|
|Other items, net||232||110|
|Net cash used in investing activities||(272,688)||(67,303)|
|CASH FLOWS FROM FINANCING ACTIVITIES:|
|Net borrowings pursuant to revolving credit facilities||69,000||23,200|
|Principal payments on long-term debt||(362)||(309)|
|Purchase of treasury stock||(70,573)||(7,414)|
|Debt issuance costs||(914)||0|
|Proceeds from issuance of long term debt||352||0|
|Proceeds from transfer of interest in notes receivable||173||0|
|Proceeds from exercise of stock options||38,059||6,590|
|Net cash provided by (used in) financing activities||11,281||(2,266)|
|Net change in cash and cash equivalents||(197,593)||(5,461)|
|Effect of foreign exchange rate changes on cash and cash equivalents||(595)||955|
|Cash and cash equivalents at beginning of period||235,336||202,463|
|Cash and cash equivalents at end of period||37,148||197,957|
|Cash payments during the period for:|
|Income taxes, net of refunds||22,470||30,813|
|Interest, net of capitalized interest||21,558||21,206|
|Non-cash investing and financing activities:|
|Dividends declared but not paid||12,114||12,133|
|Investment in property and equipment acquired in accounts payable||$ 3,393||$ 895|
Consolidated Statements of Cash Flows – USD ($) in Thousands
Cash flow numbers that are positive mean that cash flowed in to the company. Negative numbers means that cash flowed out. Please copy and paste the cash flow statements. Please copy and paste the cash flows statements from the sixth tab to the right of the questions.
1. How much cash flowed in as a result of net income in the first six months of 2018.
2. In cash from operating activities, what was the total cash from operating activities for the same period?
3. Which two operating activities had significant cash outflow in 2018 and how much were they?
4. In cash flows from investing activities, how much cash was spent (net) on the cusiness acquisition? Do you recall what business was acquired?
5. How much did the company spend on its own property and equipment?
6. How much did Choice loan to hotel owners (issuance of mezzanine and other notes receivable)?
7. How much did it invest with hotel owners using the contributions to equity method? Investments?
8. In cash flows from financing activities, how much did the company borrow?
9. How much did the company spend for purchases of treasury stock? In the next project we will look at the stock price over time.
10. How much did the company spend on dividend payments?
11. How much cash did it get from employees exercising stock options?
12. How much cash did the company have as of December 31, 2017?
13. How much cash did the company have as of June 30, 2018?
14. Where did the cash go?
Business Performance Ratios
|Ratios||Jun. 30, 2018 6 Month End||Dec. 31, 2017||Formulas|
|Quick ratio||quick ratio = (Cash and Cash Equivilents + all Accounts Receivable) / current liabilities|
|Debt-to-assets ratio||debt-to-total assets ratio = total liabilities / total assets|
Questions from Choice Hotels: Note: Use the ratios in your answers, and explain what the ratios mean. Note: Shareholder equity remains negative at the end of 2017. The deficit is decling. The large negative equity was caused by earlier purchases of Treasury Stock. This will make the equity multiplier, return on equity, and the debt to equity ratios meaningless. 1. To help improve our operations, comment on how we are doing with respect to asset management. Are there any areas for improvement? 2. To measure our company’s solvency, which of these ratios would we use and why? For Companies with deficits in shareholder equity, many analysts look to consistent stron cash flow from operations as the best indication of solvency 3. In an effort to help us improve our overall debt situation, we would like you to provide us with an assessment of our company’s solvency and leverage. What would be your plan to achieve postive equity? 4. Why might the ratios have increased or decreased?
Balance Sheet Ratios
|Ratios||Jun. 30, 2018 6 Month End||Dec. 31, 2017||Formulas|
|Current ratio||current ratio = current assets / current liabilities|
|Working capital||working capital = current assets – current liabilities|
|Deferred revenue consists of the following:|
|Initial, relicensing and franchise fees||8,905||9,352|
|Procurement services fees||3,939||7,668|
Questions from Choice Hotels: Note: Use the ratios in your answers, and explain what the ratios mean. 1. Based on your calculations of current ratio and total-asset turnover ratio, what would you recommend we do to improve our asset management? 2. We would like to improve the use of our working capital. Based on your ratio calculations. What are your specific recommendations? Please note that the current liability for deferred revenue consists primarily of amounts owed to customers in Loyalty programs. This will limit what can be done to increase working capital. See below: