For the spreadsheet, it is my understanding that each year would need to be put into a "common size" (all 3 years in one income statement, and then all 3 years in one balance sheet). Then, in the same spreadsheet, calculations for the following: quick ratio, current ratio, inventory turnover, average collection period (365 days), ROE, debt-to-asset ratio, and times interest earned. I need assistance with questions 1 & 3. Thank you!
Revord Refrigeration is a manufacturer of refrigerators. From its inception until 2 years ago, the company had a steady record of growth in sales, assets, and profits. During the second half of 2016 the company experienced a drop in demand for refrigerators. 2017 continued to be a poor year and, despite intense marketing efforts that included high discounts and extended credit, sales barely increased. Inventories ballooned and the company's payments to suppliers slowed considerably. In February 2018, Alex Revord and his finance manager Chris Baranek were reviewing the prospects for 2018. Mr. Revord anticipated a surge in demand for refrigerators in the coming years. He felt that production facilities needed to be expanded in order to meet future demand. He anticipated that if steps were initiated immediately, the expansion program could be completed by the middle of 2018. By then, the company's bulging inventories could be brought down. In order to finance the expansion, he proposed to obtain a $1 million loan from the company's bank. He instructed Mr. Baranek to prepare a forecast for 2018 EXCLUDING the expansion plan. Complete the REQUIRED items listed below. Assume you are the loan officer for the company's bank. Would you approve the loan? If yes, what conditions would you attach to the loan? Please make sure to justify your answer either way. REQUIRED: 1. SPREADSHEET: a. In an Excel spreadsheet, common size the attached data for all three years provided in the: i. Income statement ii. Balance sheet b. In the same Excel spreadsheet, calculate case ratios indicated below for all three years. 2. PAPER: a. Case background / scenario / define the problem (why are you doing this case) b. Income statement analysis i. Trend analysis ii. Benchmark analysis c. Balance sheet analysis i. Trend analysis ii. Benchmark analysis d. Ratio analysis i. Trend analysis ii. Benchmark analysis e. Take a stand would you approve or deny the loan? Why? f. Summarize your report and offer any suggestions to Alex for financial improvements 3. EXTRA CREDIT - 10 POINTS a. In the same Excel spreadsheet, develop a Statement of Cash flows for 2017, you must also discuss this in your paper. Income Statements (Thousands of Dollars) Net Sales Cost of goods sold Gross Profit 2016 74,251 60,368 13,883 Revord Refrigeration 2017 76,140 65,345 10,795 Proj. 2018 83,754 70,808 12,946 Peer Com. Size 100.00 81.00 19.00 10.00 S & A Expenses Depreciation Operating Profit 7,600 666 5,617 8,188 820 1,787 9,292 950 2,704 1.00 8.00 1,023 Interest Expense Pre-Tax Profit 705 4,912 1,202 585 1.00 7.00 1,681 Tax (34%) After Tax profit 1,670 3,242 199 386 572 1,109 2.38 4.62 150 Dividends Retained Earnings 1,252 1,990 600 509 236 1,450 No. of Shares Stock Price/Share 1,450 13 1,450 9 Balance Sheets (Thousands of Dollars) Peer Com. Size 3.50 2016 1,610 7,424 13,281 22,315 Cash Accounts Receivable Inventory Total Current Assets Revord Refrigeration 2017 1,579 11,804 18,762 32,145 Proj. 2018 1,600 10,325 13,983 25,908 28.00 47.00 78.50 Net Fixed Assets Total Assets 6,211 28,526 6,506 38,651 7,964 33,872 21.50 100.00 Accounts Payable Notes Payable Accruals Total Current Liabilities 4,224 2,051 2,050 8,325 8,141 7,331 2,848 18,320 6,131 3,862 3,143 13,136 15.00 9.00 7.50 31.50 Long Term Debt Total Liabilities 4,891 13,216 4,785 23, 105 4,681 17,816 19.00 50.50 Common Equity Total Liabilities & Equity 15,310 28,526 15,546 38,651 16,056 33,872 49.50 100.00 1.00 2.49 Quick Ratio Current Ratio Inventory Turnover Avg Coll Period (365 days) ROE Debt to Asset Ratio Times Interest Earned 6.60 35.00 17.00% 50.50% 8.00
