??

?

The following are the excel spreadsheet:

? ?

?

??

Chicago Fortune Insurance (CFI) Co. just named you General Manager of the 1,300 room Starstruck Hotel, located in far north west section of downtown Chicago. The Starstruck is a world class "independent" hotel without brand affiliation. The previous GM was dismissed after 18 months due to "ineffective leadership". You just arrived on property and received a call from Susan Criatti. Susan, your boss, is employed by the GENERAL PARTNER (CFI) as VP and Asset Manager. The owner needs your recommendations on how to improve the performance of the hotel and get more cash out of the business over the next year, and going forward. CFI has rejected the budget for 2018 as put forth by the previous General Manager. Ms. Criatti wants you to brief her board of directors during a meeting on March 17th. She asked that you have a full write-up of your plans and actions to generate cash flow and profit improvements, and that you provide your spreadsheets as well. She gave you a template to use to write your memo to her in an Executive Format. She has also provided a spreadsheet template. Background-Chicago Fortune Insurance Co. purchased the property at the previous market high and has been the owner of the hotel for 8 years. CFI recently converted a number of rooms to condominiums, leaving the hotel with 1,300 hotel rooms to sell. The hotel was also recently completely renovated. The Director of Capital Improvements, who plans and oversees all renovations, indicated all bills have been paid. CFI requires the hotel to record and fund a 3% (annual sales) capital reserve for the purchase of furniture, fixtures and equipment (FF&E) and to fund future renovations. Sales and Revenue Outlook The Starstruck is a 90% group hotel. There are few office buildings and limited demand drivers in the immediate area, so transient demand is negligible. Business flows evenly throughout the year and there are no business "dips and valleys" except for one week during Thanksgiving and the last two weekends in December for holidays. The ADR has declined about 3% a year for the past several years. In 2011, the 2,400 room MGM convention hotel opened in the outskirts of Northern suburbs of Chicago and started "closing" on downtown and Starstruck convention business. The Director of Sales confirms 15 large annual conventions (+$1.0M each) have been "taken" from The Starstruck by MGM since 2012 and that the sales team has replaced these conventions with lower rated groups over the last four years. The MGM is NOT in the hotel's STAR Market Share Competitive set because STR does not allow only one hotel to be added. Starstruck's competitive set (not including the MGM) is expected to achieve a rate of $235 and an occupancy of 72% in 2017. The previous General Manager projected that the competitive set would raise rate by 4% in 2018, and maintain an occupancy of 72%. Labor Costs and Labor Environment - The Starstruck Hotel is under the second year of a five year collective bargaining agreement. The agreement was signed with Local 13 of Chicago Hotel Motel Trades Council. The hotel is required to pay 100% of all medical, Labor Costs and Labor Environment - The Starstruck Hotel is under the second year of a five year collective bargaining agreement. The agreement was signed with Local 13 of Chicago Hotel Motel Trades Council. The hotel is required to pay 100% of all medical, dental and pension costs. Union wage increases will average a 3.8% increase, each year. Union positions cannot be eliminated and benefits cannot be altered. Union Full-Time Hourly # of Workers Hourly Rate $16.23 800 Non-Union Management # of Managers Avg. Salary 105 $72,000/yr. Director level and above managers receive incentive bonuses of approximately 15% to 25% of pay. The General Manager receives a 20% to 45% incentive bonus, and the General Manager's Annual Salary is $235,000. The previous General Manager engaged an outside consultant about three months ago to review management staffing. The consultant's report indicated there are (15) fifteen convention and event managers with an average tenure of 8-10 years of experience. The report indicated the department is too large and each is handling too few groups per manager. The report indicated a more reasonable staffing level is (11) eleven managers for this department. Food Cost of Sales The forecasted full year food purchases for 2017 are $8,103,452. Beginning inventory was $187,452 and the ending inventory is projected to be $192,458. Transfer of food to beverage will be $79,457. Transfer from beverage to food will be $42,457. Employee meal credit to benefit costs is projected at $773,000 for 2017. Food to beverage sales run a 65% food mix and 35% beverage mix. Total food and beverage sales for 2017 are forecasted at $39,919,448 Latest Owner's Audit The last owners audit revealed a need to implement an automated data capture system for credit cards. Credit cards are still processed manually using the credit card batch procedure. This dated procedure delays the payments of credit cards. The audit revealed that 60% of sales are settled by credit card, and 40% of the sales are direct billed via master accounts. The owners audit also revealed that master accounts are delayed in Guest Ledger between 10 and 12 days before the bills are sent out to the credit card processing banks. The delay is due to missing back up to support the charges. Best in class hotels run a 3 to 5 day billing transfer. The owners audit also revealed the hotel accounting office does a terrific job paying its vendors on time and has day billing transfer. The owners audit also revealed the hotel accounting office does a terrific job paying its vendors on time and has an outstanding relationship with its vendors. Nearly all bills are paid within 12-13 days of receipt. Starstruck Additional Transactions The hotel had the following additional transactions during 2017 A new computer system was purchased for $270,000. A 30 year, 10.5% note with a balance of $1,287,500 was paid off. The Limited Partners received total distributions (dividends) of $1,000,000 in 2017 (which is the same amount they received in 2016). Asset Managers Instructions and Expectations You have three weeks to gather your team and develop a plan. Your submission to Ms. Criatti should include an Executive Style memorandum following the format and content from the example that Ms. Criatti provided. In addition, your submission should include ONE Excel WorkBOOK file with SIX (6) WorkSHEET tabs included. Your written report and Excel Workbook file should be uploaded to Canvas no later than 5pm on March 17th. If you were asked to do a 5 minute oral overview presentation (Lisa, Tala, and Pitchya), then you should ALSO upload 4-5 power point slides (by 3/17) and plan to present them at the Board of Director's Meeting (class) on 3/17/20. NOTE: ALWAYS include your first initial and last name within your filenames for all files submitted to Canvas. Ms. Criatti has asked for the following: First Based on the available General Ledger information (Exhibit B), prepare the balance sheets for 2016 and 2017 in as much detail as possible. Follow a proper USALI format as shown in your book and in class. Once your Balance Sheet is prepared, calculate the Current Ratio for 2016 and 2017. Prepare on Tab 1 of the Workbook file. Second Prepare a 2017 Statement of Retained Earnings. Prepare on Tab 1 of the Workbook file. Third Prepare a 2017 Statement of Cash Flows (SCF) in the proper format. Prepare on Tab 1 of the Workbook file. Fourth. The asset manager suggests that your team complete a detailed ratio analysis to arrive at your action plan recommendations. In her telephone conversation, she specifically mentioned to look at and determine the items listed on the following page. > Calculate 2017 Rate, Occupancy and Revpar for the Starstruck Hotel. Then, calculate 2017 Rate Index, Penetration Index, and Yield Index for Starstruck based on the competitor information given in the case. Show these calculations on Tab 2 of the Workbook file. > Calculate 2016 and 2017 Operating Efficiency Ratios (GOP Margins) for Starstruck Hotel. Show these calculations on Tab 2 of the Workbook file. > Calculate Food Costs of Goods Sold for 2017 from the information provided in the case. Calculate Food Costs as a % of Sales for 2017. Compare your result to the Industry Average on Exhibit A. Calculate the cash flow improvement opportunity that would result from reducing Food Cost % from its current level down to the industry average. Show these calculations on Tab 3 of the Workbook file. > Compute the food inventory turnover for 2017. Compute the average food days on hand for 2017. Determine the cash flow improvement opportunity that would result from reducing Food Days on Hand to the Industry Average shown on Exhibit A. Show these calculations on Tab 3 of the Workbook file >Calculate Accounts Receivable Turnover and Days Credit Sales Outstanding for Credit Card Receivables. Calculate the cash flow opportunity that would result from REDUCING the number of Days Credit Sales Outstanding (for CREDIT CARD Receivables) to the Industry Average shown on Exhibit A. Show these calculations on Tab 4 of the Workbook file. > Calculate Accounts Receivable Turnover and Days Credit Sales Outstanding for Direct Bill Receivables. Calculate the cash flow improvement opportunity that would result from REDUCING the number of Days Credit Sales Outstanding (for DIRECT BILL Receivables) to the Industry Average shown on Exhibit A. Show these calculations on Tab 4 of the Workbook file. > Compute the Accounts Payables Turnover and the Average Payables Days Outstanding. Calculate the cash flow opportunity that would result from INCREASING the number of Payables Days Outstanding to the Industry Average shown on Exhibit A. Show these calculations on Tab 5 of the Workbook file. > Quantify any other Cash Flow improvement opportunities that appear to be available based on the facts presented in the case. Don't forget to review the information in the Exhibits when looking for opportunities. Show these in the case. Don't forget to review the information in the Exhibits when looking for opportunities. Show these calculations on Tab 6 of the Workbook file. Summarize all cash flow improvement opportunities on Tab 6. Be sure to give a brief description of the assumptions you are using for each line item. IMPORTANT NOTE: Do NOT include/calculate REVENUE improvement opportunities. You do not have enough information provided in the case to make good decisions regarding rate, occupancy and revenue improvement suggestions. Stick to improving costs and inventory, collections and payables procedures. > Also on Tab 6: - Make note of investments/expenses that will be incurred to realize the improvements you identified. You do NOT need to quantify the AMOUNT of the investments. Just list the nature of the items/investments that will be needed. - Make note of any additional ideas that you have to make improvements that you have not yet quantified...things that you want to explore further. NOTE: This would be a good place to list revenue improvement opportunities. Fifth Prepare an Executive Business Style Memorandum (see example posted with assignment). Ms. Criatti and the CFI Board are time pressed. Be clear and consise in your writing. Address the following in the Memorandum: . An opening paragraph giving a QUICK AND BRIEF synopsis of the request that was given to you, the work that you did as a result of that request, and your overall (not detailed) findings from the work that you did. In the next section of the memo, provide a numbered list of EACH cash flow opportunity. For EACH opportunity, write SPECIFIC PLANS & ACTIONS that you will take to achieve this opportunity. Just because you were able to compute a dollar figure for improved cash flow based on improving a ratio up to the Industry Average, that does NOT mean that the improvements will happen on their own. As the new General Manager, what SPECIFICALLY will you be working on with your team in order to ACHIEVE the improvements? Print Tab 6 of the spreadsheet and attach it to the memorandum as Exhibit A. Exhibit A includes a numbered list of improvement opportunities. HINT: There should be EIGHT distinct opportunities and calculations. If you are creative, you may find more. Your numbered list in the body of the memo should refer to and follow the numbered list on Exhibit A. Last, write a closing paragraph that gives Ms. Criatti confidence that you intend to deliver the opportunities that you have outlined and generate a significant improvement in Cash Flow to CFI. | Key Assets $ and Stats Direct Bill Accounts Receivable Days Credit Sales Outstanding, Direct Bill % of TOTAL Sales, Direct Bill Credit Card Accounts Receivable Days Credit Sales Outstanding Credit Cards % of TOTAL Sales, Credit Card Rooms Linen per Room China, Glass and Silver per Room Goods PURCHASED By Hotel on Credit Food Inventory Food Inventory Days on Hand Key Liabilities Stats Accounts Payable Accounts Payable Days Outstanding Key Profitability Stats Food Cost as a % of Food Sales Gross Operating Profit % (aka Operating Efficiency Ratio) (Exhibit A) Key Performance Statistics 2016 $4,266,916 N/A N/A $3,352,576 N/A N/A N/A N/A N/A 187,452 N/A 2016 $310,000 N/A 2016 N/A ?? 2017 $4,832,550 ?? 40% $3,797,004 ?? 60% $290 $225 $9,000,000 192,458 ?? 2017 $300,000 ?? 2017 ?? ?? Industry Average N/A 32 days N/A N/A 8 days N/A $140 $175 N/A N/A 7 days or fewer Industry Average N/A 45 Industry Average 23.7% 33% (2015 GOP% for Full- Service Hotels in the same Geographic area Accounts Payable Accumulated Depreciation equipment Retained Earnings Cash Cash Bank Balance for Capital Reserve (FF&E) (Hint: An FF&E Reserve shows on the Balance Sheet as both an Asset and a Liability) Accounts Receivable Food and Beverage Inventories Operating Inventories 15 Year 8.5% Note Prepaid Insurance Investments (Long-term) Land Capital One 6.5% Line of Credit (Hint: A Current Liability) Current Maturities of Long Term Debt Building Equipment Host Hotels and Resorts, LLC Member Accumulated Depreciation, Building Organization Pre-Opening Costs Starstruck Hotel 2016 $ 310,000 4,500,000 561,718 319,935 0 7,528,058 320,200 609,300 8,564,056 75,000 385,000 15,000,000 25,000 332,986 2017 $ 300,000 5,000,000 2,957,708 77,669 3,339,951 8,525,999 450,000 700,000 8,201,637 144,329 385,000 15,000,000 25,000 362,419 260,000,000 260,000,000 19,930,000 20,200,000 14,209,233 14,209,233 260,000,000 260,000,000 250,000 200,000 Organization Pre-Opening Costs Wages Payable Thomas B. Mellor, LLC Member Colvin Mill Capital, LLC Member Contractual Escrow Reserve (offset expensed to P&L) 30-Year 10.5% Note 250,000 40,000 6,400,000 8,187,000 0 1,287,500 200,000 40,000 6,400,000 8,187,000 3,339,951 0 O 1 2 B 1 5 5 7 3 D 1 2 Starstruck Hotel Balance Sheet as of December 31st 2016 Current Assets Total Current Assets $ Property and Equipment Less: Accum. Deprec. (enter negative #) Total Property & Equipment $ $ $ 2017 Incr/Decr $ $ $ $ Starstruck Hotel Statement of Cash Flow for Year Ended 12/31/17 Cash Flow from Operations Net Income Adj. to Determine Operating Cash Flow + Depreciation & Amortization Ttl. Cash Flow from Operations Cash Flow from Investments Ttl. Cash Flow from Investments Cash Flow from Financing Hint: Don't forget the amortization of pre-oper 17 18 19 Less: Accum. Deprec. (enter negative #) 20 21 22 23 24 25 26 FF&E Reserve 27 Organization Pre-Opening Costs 28 29 30 31 Total Property & Equipment $ 32 33 34 35 36 37 38 39 Other Assets Investments $ Total Other Assets $ TOTAL ASSETS $ Current Liabilities $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Ttl. Cash Flow from Investments Cash Flow from Financing +/- Borrow/Pay LT Debt Ttl Cash Flow from Financing Net Cash Flow 2017 Change in Cash on Balance Sheet Current Ratio $ $ 2016 >>>>>>>> LT Debt Ending Balance Plus:Current Maturity of LT Debt Minus: LT Debt Beginning Balance LT Debt Addtl. Borrowed or Paid } This number should match the number in ce This number should match the number in ce 2017 Formula: Current Assets/Current Li 59 TOTAL LIABILITIES AND OWNER'S EQUITY $ 60 61 62 2017 Statement of Retained Earnings 63 Beginning Retained Earnings 64 Plus: 2017 Net Income 65 Less: Partner Distributions (Dividends) 66 Ending Retained Earnings 67 $ $ I < < Enter negative number here. Accounts Payable Accounts Payable Turnover and A/P Days Outstanding 2016 2017 2017 Goods Purchased By Hotel on Credit $ < < from case Cash Flow Opportunity From LENGTHENING Payable Outstanding from 12.37 Days to 45 Days 2017 Average Accounts Payable Value of One day improvement Desired Days Improvement Cash Flow Improvement - < < from case Avg. Accts. Payable A/P Turnover EXISTING A/P Days Outstndg. Desired A/P Day O/S Desired Days Improvement A/P < < Formula (Beg. A/P + Ending A/P)/2 < < Formula: Goods Purchased by Hotel on Credit/ Avg. Accts. Payable 2 Description of Savings/Improvement Opportunity 4 Improve Food Cost% 5 Improve Food Days on Hand 6 Improve Credit Card Collections 7 Improve Direct Bill Collections 8 Improve Payables/Lengthen Payment Period Exhibit A: Summary of Improvement Opportunities Savings/ Cash Flow TOTAL Offsetting Expenditures to Realize Improvements (No dollar amounts, just items): Assumptions