AutoSave O OFF A A P . G ... Assignment 1 - Conducting Financial Analysis - Data Home Insert Draw Page Layout Formulas Data Review View Automate ? Tell me Comments Share Calibri (Body) v 11 AA 207 ab Wrap Text v General Insert v Ex AY- O. Ex Delete v Paste BIUV DVAv E Merge & Center v $ ~ % " Conditional Format Cell Sort & Find & Analyze Sensitivity Formatting as Table Styles Format v Filter Select Data F20 4 X V fx A B C D E F G H J K L M N O P Q R Appendix A1: RetroSpin Records Comparative Income Statement ($M) For the Years Ended Dec 31, 2013 to 2016 2013 2014 2015 2016 Sales $ 75.00 $ 78.75 $ 86.63 $ 95.29 Cost of Goods Sold 41.94 $ 44.03 $ 46.24 $ 48.55 Gross Profit $ 33.06 $ 34.72 $ 40.39 $ 46.74 Selling and Administration 18.75 $ 17.72 $ 17.33 $ 19.06 Depreciation 1.51 $ 2.71 $ 4.31 $ 4.18 10 Operating Profit 12.81 $ 14.29 $ 18.76 $ 23.50 11 Interest 1.88 $ 2.51 $ 7.10 $ 4.93 12 Earnings Before Taxes 10.92 $ 11.78 $ 11.65 $ 18.57 13 Taxes 2.73 $ 2.95 $ 2.91 $ 4.64 14 Net Income $ 8.19 $ 8.84 $ 8.74 $ 13.93 15 16 tax rate 25% 25% 25% 25% 17 1 - tax rate 75% 75% 75% 75% 18 Net Income + 1(1-tax rate) $ 9.60 $ 10.72 $ 14.07 $ 17.63 19 20 21 22 23 24 25 26 27 28 29 A1 - IS A1 - BS A1 - Industry Averages Rubric + Ready 1 Accessibility: Good to go + 141%AutoSave O OFF A A P . G ... Assignment 1 - Conducting Financial Analysis - Data Home Insert Draw Page Layout Formulas Data Review View Automate ? Tell me Comments Share Calibri (Body) v 11 V AA Accounting Insert v = = ab Wrap Text v Ex AY- O. Ex Delete v v Z Paste BIUV DV Av E Merge & Center v $ ~ % " Conditional Format Cell Sort & Find & Analyze Sensitivity Formatting as Table Styles Format v DV Filter Select Data H6 4 X V fx =AVERAGE(C6:D6) A B D E F G H I J K L M N O P Q R S T Appendix A2: RetroSpin Records Comparative Balance Sheet ($M) For the Years Ended Dec 31, 2013 to 2016 A W 2013 2014 2015 2016 2014 2015 2016 UT Cash 2.05 $ 2.21 $ 2.19 $ 3.48 A /R 1.02 $ 1.10 $ 1.09 $ 1.74 avg $ 1.06 $ 1.10 $ 1.42 Inventory 15.00 $ 15.75 $ 17.33 $ 19.06 avg $ 15.38 $ 16.54 $ 18.19 Total Current Assets $ 18.07 $ 19.06 $ 20.60 $ 24.28 Land, Plant and Equipment 10.86 $ 32.46 $ 36.84 $ 35.60 avg 21.66 $ 34.65 $ 36.22 10 Other Asset 0.49 $ 1.08 $ 1.68 $ 1.76 0.79 $ 1.38 $ 1.72 11 Total Assets $ 29.43 $ 52.60 $ 59.11 $ 61.65 avg $ 41.01 $ 55.86 $ 60.38 12 13 A/F 10.75 $ 25.00 $ 22.43 $ 21.99 avg $ 17.88 $ 23.72 $ 22.21 14 Current Portion of LT Debt 0.58 $ 0.58 $ 0.58 $ 0.58 15 Total Current Liabilities $ 11.33 $ 25.58 $ 23.01 $ 22.57 16 Long-term Debt 10.56 $ 10.56 $ 10.56 $ 10.56 17 Total Liabilities $ 21.89 $ 36.14 $ 33.57 $ 33.13 18 Shareholders' Equity $ 7.54 $ 16.46 $ 25.54 $ 28.51 avg $ 12.00 $ 21.00 $ 27.03 19 Total Liabilities and Equities 29.43 $ 52.60 $ 59.11 $ 61.65 20 21 $ 8.92 $ 9.09 $ 2.97 22 23 24 25 26 27 28 29 30 31 32 33 A1 - IS A1 - BS A1 - Industry Averages Rubric + Ready 1 Accessibility: Good to go + 125%AutoSave .0\" a Assignment1 - Conducting Financial Analysis - Data Home Insert Draw Page Layout Formulas Data Review View Automate Q Tell me Cl Comments 6 Share . . A v : = _ '0 ab V ' l I 6 Insert v v E\" v EA V Callbrl (Body) v 11 v A A _ : 'o/' V Cy Wrap Text v General ]!f v J v / v E Dale's v V 9? v p v H V s B I u v 0 v A v a E E E E E v $ v % 9 $8 :3 E23123: .2123; 5.39.2. r v o v 512:? 23:3 \"323:9 027 ; fx v A B C D E F G H J K L M N 1 Appendix A3: RetroSpin Records 2 Industry Averages 3 2016 4 Current Ratio 1.35 5 Cash Ratio 0.25 6 Inventory Turnover (days) 40_12 7 AIR Turnover (days) 34.5 3 NF Turnover (days) 50.1 9 Cash Conversion Cycle 15.35 10 Fixed Assets Turnover 3.5 1 1 Total Assets Turnover 1.75 12 Debt Ratio 0.5 13 Times Interest Earned 10.5 14 Gross Prot Margin 28.00% 15 Operating Prot Margin 12.00% 16 Net Prot Margin 7.50% 17 Return on Assets 15.50% 18 Return on Equity 37.50% 19 20 21 22 23 24 25 26 27| :. \"m Rubric + Ready t13-\AutoSave OFF A A CGP ... New FIN2305 - Assignment 1 - Conducting Financial Analysis - Saved to my Mac Home Insert Draw Design Layout References Mailings Review View ? Tell me Comments Editing Share Calibri (Bo... v 14 A A Aav A AaBbCcDdEe AaBbCcDdEe AaBb( AaBbCc AaBbCcDc Paste BIUvab X X ADVA v Norma No Spacing Heading 1 Heading 2 Heading 3 Styles Dictate Sensitivity Editor Pane Welcome to your new job as an Analyst with Lexi Holdings, an investment firm with a wide range of investment holdings! Your first assignment is to assess RetroSpin. Your new boss, Wally Oliver, hands you a multi-year Income Statement (Appendix Al ) and multi-year Balance Sheet (Appendix A2) for RetroSpin Records. RetroSpin is record store, wholly owned by Lexi Holdings, catering to music enthusiasts who prefer vinal records over digital music or other music formats. RetroSpin brought on a new CEO in Jan 1, 2015. Your boss asks you to assess whether or not RetroSpin is doing well under the new CEO. Your job is to calculate and interpret important financial ratios and to make comments on how things have changed since the new ALGONQUIN Centre for Continuing COLLEGE | & Online Learning CEO came on board. You are also required to compare RetroSpin to others in the industry. Appendix A3 contains industry average information. Please use the information in Appendix Al to A3 to calculate the following ratios: 1. Current ratio 2. Account Receivable ratio (or days receivable) 3. Accounts Payable ratio (or days payable) 4. Debt ratio 5. Gross Profit margin 6. Operating Profit margin 7. Net Profit margin 8. Return on Assets 9. Return on Equity Calculate each of the ratios above for 2014, 2015 and 2016. Interpret your calculations: what does this information mean? How is the company doing under the new CEO? (You should compare things before and after the CEO came on board). How is the company doing relative to the industry? (Total: calculations 25 marks + interpretation 25 marks, see attached rubric) Page 1 of 7 873 words X English (Canada) Accessibility: Investigate Focus E + 124%