Assume you work as an accountant for Finish Line (the publicly traded shoe company based in Indianapolis,
Question:
Assume you work as an accountant for Finish Line (the publicly traded shoe company based in Indianapolis, Indiana – ticker symbol “FINL”). The CFO would like you to perform some financial analysis that she can use to benchmark how well Finish Line is doing compared to competitors in the retail footwear industry (SIC code “5661”).
Download financial information for SIC 5661 companies from Blackboard. The following fields may be necessary for your analysis:
GVKEY |
Identifying key for the company |
DATADATE |
date of the data provided |
FYEAR |
fiscal year for data provided |
TIC |
ticker symbol |
CITY |
city of headquarters |
SIC |
Industry code |
CONSOL |
|
INDFMT |
|
DATAFMT |
|
POPSRC |
|
CURCD |
Currency code |
COSTAT |
|
CONM |
Company name |
CUSIP |
Cusip # |
CIK |
CIK companny identifying code |
EXCHG |
Stock Exchange Code |
FYR |
Fiscal year end month code |
ACT |
Total Current Assets |
AT |
Total Assets |
CH |
Total Casth |
DLTT |
Total Long-term Debt |
GDWL |
Goodwill |
INVT |
Total Inventory |
LCT |
Total Current Liabilities |
LT |
Total Liabilities |
RE |
Retained Earnings |
RECTR |
Total Receivables |
XPP |
|
COGS |
Cost of Goods Sold |
NI |
Net Income |
OPITI |
Operating Income |
REVT |
Total Revenues |
SALE |
Total Net Sales |
TXT |
Taxes |
XAD |
|
XRD |
|
XSGA |
|
CSHO |
|
EMP |
Employees |
You must use Excel tools and formulas to perform financial ratio analysis and benchmarking for the CFO. You must provide the CFO with benchmarking analysis comparing Finish Line to its competitors for:
- Inventory Turnover: COGS/Average Inventory
- Current Ratio: Current Assets/Current Liabilities
- Cash Holdings: Cash/Average Total Assets
First, as always in real life, examine the data and clean it before you can analyze it. To start, open your downloaded file in Excel. Next, examine and clean the data file using the steps below. Keep note of answers to questions listed.
- What is the range of fiscal years with data available for Finish Line in the download? ___________
- Remove any companies that report in non-US currency (since these are not comparable). List the name(s) of any companies reporting in non-US currency that need to be filtered out. _______________________________________________
- The CFO only wants to look at the 3 most recent years of data for the benchmarking analysis. Filter out all years not needed for the benchmarking analysis requested by the CFO. (remember you need to calculate “average inventory” and “average total assets” for each of the 3 most recent years…which requires you to also keep the 4 th most recent year in this step)
- Remove firm-year rows with missing key data (i.e., remove rows that are missing information for the key variables needed to calculate our specific ratios). Look at the data in detail again…remove any other rows that you feel are not needed.
- Copy the filtered data into a new tab since you don’t need all the other data (and the large amount of rows in Excel makes processing much slower). Be sure to use Special Paste (values & formats). This step is important so since formulas will change when you sort. DON’T SKIP!
- Sort the data by Company, then by fiscal year (in ascending order).
Next, perform some benchmarking analysis.
- Use formulas in Excel to create new fields that will help you calculate the following 3 financial ratios:
- Inventory Turnover
- First, calculate each company’s “average Inventory” for each year of data available. (note: this is done by taking the current year inventory plus the prior year inventory divided by 2). Use an “IF” conditional statement to make sure the calculation is only made when data is available.
- Use the average inventory just calculated and the COGS to calculate Inventory Turnover in a new column.
- Current Ratio
- Use current assets and current liabilities to calculate Current Ratio in a new column.
- Cash Holdings
- First, calculate each company’s “average total assets” for each year of data available. (note: this is done by taking the current year total assets plus the prior year total assets divided by 2). Use an “IF” conditional statement to make sure the calculation is only made when data is available.
- Use the average total assets just calculated and the cash to calculate cash holdings as a percentage of average total assets.
- Hide unnecessary columns at this point to clean things up a bit. Your data should now nicely fit onto one screen.
- Use filters and sorts to quickly and easily see where Finish Line ranks in order of each ratio.
- Create a screenshot of your Excel data. Make sure that the Fiscal Year, Company Name and three calculated ratios appear on screen. All other fields should be hidden per step 2 above.
- Use the project assignment link in Blackboard and answer all questions. Make sure to read each question closely.
Global Company Key | Data Date | Data Year - Fiscal | Industry Format | Level of Consolidation - Company Annual Descriptor | Population Source | Data Format | Ticker Symbol | CUSIP | Company Name | ISO Currency Code | Fiscal Year-end Month | Current Assets - Total | Assets - Total | Cash | Cost of Goods Sold | Long-Term Debt - Total | Employees | Goodwill | Inventories - Total | Current Liabilities - Total | Liabilities - Total | Net Income (Loss) | Operating Income - Total | Retained Earnings | Receivables - Trade | Revenue - Total | Sales/Turnover (Net) | Income Taxes - Total | Stock Exchange Code | CIK Number | Active/Inactive Status Marker | City | Standard Industry Classification Code |
5109 | 20030131 | 2002 | INDL | C | D | STD | GCO | 371532102 | GENESCO INC | USD | 1 | 269.431 | 419.214 | 55.929 | 418.917 | 103.245 | 5.7 | 0 | 168.622 | 88.266 | 236.435 | 36.28 | |
73.327 | 16.762 | 828.307 | 828.307 | 22.379 | 11 | 18498 | A | Nashville | 5661 |
5109 | 20040131 | 2003 | INDL | C | D | STD | GCO | 371532102 | GENESCO INC | USD | 1 | 283.766 | 430.187 | 81.549 | 426.766 | 86.25 | 6.2 | 0 | 167.234 | 92.442 | 214.589 | 28.73 | |
107.051 | 12.515 | 837.379 | 837.379 | 15.715 | 11 | 18498 | A | Nashville | 5661 |
5109 | 20050131 | 2004 | INDL | C | D | STD | GCO | 371532102 | GENESCO INC | USD | 1 | 305.919 | 635.571 | 60.068 | 530.331 | 161.25 | 9.6 | 97.223 | 207.197 | 129.674 | 363.506 | 48.249 | |
150.517 | 17.906 | 1112.681 | 1112.681 | 28.642 | 11 | 18498 | A | Nashville | 5661 |
5109 | 20060131 | 2005 | INDL | C | D | STD | GCO | 371532102 | GENESCO INC | USD | 1 | 341.188 | 686.118 | 60.451 | 596.247 | 106.25 | 11.1 | 96.235 | 230.648 | 156.202 | 337.367 | 62.686 | |
213.028 | 21.171 | 1283.876 | 1283.876 | 39.844 | 11 | 18498 | A | Nashville | 5661 |
5109 | 20070131 | 2006 | INDL | C | D | STD | GCO | 371532102 | GENESCO INC | USD | 1 | 335.066 | 729.373 | 16.739 | 689.337 | 109.25 | 12.75 | 107.651 | 261.037 | 134.736 | 324.147 | 67.646 | |
285.295 | 24.084 | 1460.478 | 1460.478 | 42.871 | 11 | 18498 | A | Nashville | 5661 |
5109 | 20080131 | 2007 | INDL | C | D | STD | GCO | 371532102 | GENESCO INC | USD | 1 | 383.667 | 804.556 | 17.703 | 704.89 | 155.22 | 13.95 | 107.618 | 300.548 | 145.574 | 383.141 | 6.885 | |
293.02 | 24.275 | 1502.119 | 1502.119 | 24.247 | 11 | 18498 | A | Nashville | 5661 |
5109 | 20090131 | 2008 | INDL | C | D | STD | GCO | 371532102 | GENESCO INC | USD | 1 | 398.119 | 818.027 | 17.672 | 724.823 | 118.52 | 14.125 | 111.68 | 306.078 | 138.982 | 371.093 | 152.636 | |
401.626 | 23.744 | 1551.562 | 1551.562 | 95.683 | 11 | 18498 | A | Nashville | 5661 |
5109 | 20100131 | 2009 | INDL | C | D | STD | GCO | 371532102 | GENESCO INC | USD | 1 | 450.072 | 863.652 | 82.148 | 731.349 | |
13.925 | 118.995 | 290.974 | 169.657 | |
28.813 | |
423.406 | 27.217 | 1574.352 | 1574.352 | 21.402 | 11 | 18498 | A | Nashville | 5661 |
148224 | 20110131 | 2010 | INDL | C | D | STD | BKRSQ | 57465106 | BAKERS FOOTWEAR GROUP INC | USD | 1 | 28.514 | 48.006 | 0.146 | 130.341 | 8.123 | 2.488 | 0 | 25.912 | 37.222 | 53.993 | -9.292 | |
-46.432 | 1.485 | 185.626 | 185.626 | -0.187 | 19 | 1171032 | I | St Louis | 5661 |
148224 | 20120131 | 2011 | INDL | C | D | STD | BKRSQ | 57465106 | BAKERS FOOTWEAR GROUP INC | USD | 1 | 26.822 | 41.711 | 0.148 | 133.645 | 8.185 | 2.341 | 0 | 24.398 | 42.598 | 58.325 | -10.957 | |
-57.389 | 1.151 | 185.095 | 185.095 | 0 | 19 | 1171032 | I | St Louis | 5661 |
21898 | 20120331 | 2011 | INDL | C | D | STD | BOOT | 99406100 | BOOT BARN HOLDINGS INC | USD | 3 | |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
11 | 1610250 | A | Irvine | 5661 |
11584 | 20030131 | 2002 | INDL | C | D | STD | FL | 344849104 | FOOT LOCKER INC | USD | 1 | 1284 | 2486 | 357 | 3165 | 356 | 40.151 | 136 | 835 | 572 | 1376 | 153 | |
733 | 33 | 4509 | 4509 | 84 | 11 | 850209 | A | New York | 5661 |
11584 | 20040131 | 2003 | INDL | C | D | STD | FL | 344849104 | FOOT LOCKER INC | USD | 1 | 1519 | 2689 | 448 | 3302 | 335 | 40.298 | 136 | 920 | 545 | 1314 | 207 | |
965 | 41 | 4779 | 4779 | 115 | 11 | 850209 | A | New York | 5661 |
11584 | 20050131 | 2004 | INDL | C | D | STD | FL | 344849104 | FOOT LOCKER INC | USD | 1 | 1832 | 3237 | 225 | 3722 | 347 | 44.109 | 271 | 1151 | 684 | 1407 | 293 | |
1224 | 47 | 5355 | 5355 | 119 | 11 | 850209 | A | New York | 5661 |
11584 | 20060131 | 2005 | INDL | C | D | STD | FL | 344849104 | FOOT LOCKER INC | USD | 1 | 2014 | 3312 | 289 | 3944 | 275 | 44.276 | 263 | 1254 | 717 | 1285 | 264 | |
1430 | 49 | 5653 | 5653 | 142 | 11 | 850209 | A | New York | 5661 |
11584 | 20070131 | 2006 | INDL | C | D | STD | FL | 344849104 | FOOT LOCKER INC | USD | 1 | 2034 | 3249 | 221 | 4014 | 220 | 45.406 | 264 | 1303 | 516 | 954 | 251 | |
1689 | 59 | 5750 | 5750 | 145 | 11 | 850209 | A | New York | 5661 |
11584 | 20080131 | 2007 | INDL | C | D | STD | FL | 344849104 | FOOT LOCKER INC | USD | 1 | 2064 | 3248 | 488 | 4017 | 221 | 44.415 | 266 | 1281 | 501 | 977 | 51 | |
1694 | 50 | 5437 | 5437 | -99 | 11 | 850209 | A | New York | 5661 |
11584 | 20090131 | 2008 | INDL | C | D | STD | FL | 344849104 | FOOT LOCKER INC | USD | 1 | 1764 | 2877 | 385 | 3777 | 142 | 39.758 | 144 | 1120 | 418 | 953 | -80 | |
1335 | 53 | 5237 | 5237 | -21 | 11 | 850209 | A | New York | 5661 |
11584 | 20100131 | 2009 | INDL | C | D | STD | FL | 344849104 | FOOT LOCKER INC | USD | 1 | 1772 | 2816 | 582 | 3522 | 138 | 38.764 | 145 | 1037 | 433 | 868 | 48 | |
1342 | 37 | 4854 | 4854 | 26 | 11 | 850209 | A | New York | 5661 |
21898 | 20130331 | 2012 | INDL | C | D | STD | BOOT | 99406100 | BOOT BARN HOLDINGS INC | USD | 3 | 75.574 | 224.282 | 1.19 | 145.769 | 67.5 | |
78.033 | 67.995 | 58.12 | 146.658 | 0.646 | |
-3.725 | 1.078 | 233.203 | 233.203 | 0.826 | 11 | 1610250 | A | Irvine | 5661 |
25357 | 20110228 | 2010 | INDL | C | D | STD | FINL | 317923100 | FINISH LINE INC -CL A | USD | 2 | 509.684 | 664.845 | 299.323 | 788.114 | 0 | 11.5 | 0 | 193.505 | 126.42 | 174.6 | 68.834 | |
372.047 | 10.552 | 1229.002 | 1229.002 | 41.277 | 14 | 886137 | A | Indianapolis | 5661 |
25357 | 20120229 | 2011 | INDL | C | D | STD | FINL | 317923100 | FINISH LINE INC -CL A | USD | 2 | 552.748 | 711.496 | 307.494 | 862.039 | 0 | 11.8 | 8.503 | 220.405 | 138.683 | 181.959 | 84.804 | |
445.884 | 9.041 | 1369.259 | 1369.259 | 49.978 | 14 | 886137 | A | Indianapolis | 5661 |
25357 | 20130228 | 2012 | INDL | C | D | STD | FINL | 317923100 | FINISH LINE INC -CL A | USD | 2 | 491.694 | 706.422 | 226.982 | 927.621 | 0 | 11.9 | 13.888 | 243.77 | 134.037 | 177.89 | 71.473 | |
504.883 | 14.768 | 1443.365 | 1443.365 | 43.314 | 14 | 886137 | A | Indianapolis | 5661 |
11584 | 20110131 | 2010 | INDL | C | D | STD | FL | 344849104 | FOOT LOCKER INC | USD | 1 | 1934 | 2896 | 696 | 3533 | 137 | 38.007 | 145 | 1059 | 489 | 871 | 169 | |
1442 | 41 | 5049 | 5049 | 88 | 11 | 850209 | A | New York | 5661 |
11584 | 20120131 | 2011 | INDL | C | D | STD | FL | 344849104 | FOOT LOCKER INC | USD | 1 | 2079 | 3050 | 851 | 3827 | 135 | 39.157 | 144 | 1069 | 548 | 940 | 278 | |
1584 | 49 | 5623 | 5623 | 157 | 11 | 850209 | A | New York | 5661 |
11584 | 20130131 | 2012 | INDL | C | D | STD | FL | 344849104 | FOOT LOCKER INC | USD | 1 | 2363 | 3367 | 880 | 4148 | 133 | 40.639 | 145 | 1167 | 636 | 990 | 397 | |
1905 | 68 | 6182 | 6182 | 210 | 11 | 850209 | A | New York | 5661 |
24171 | 20030131 | 2002 | INDL | C | D | STD | DSW | 23334L102 | DSW INC | USD | 1 | 483.154 | 831.799 | 11.059 | 1457.667 | 264.664 | 17.4 | 37.619 | 389.825 | 301.764 | 610.635 | -3.665 | |
79.021 | 11.599 | 2458.124 | 2458.124 | 0.944 | 11 | 1319947 | A | Columbus | 5661 |
24171 | 20040131 | 2003 | INDL | C | D | STD | DSW | 23334L102 | DSW INC | USD | 1 | 499.254 | 863.945 | 14.226 | 1540.235 | 326.94 | 18.4 | 37.619 | 420.338 | 264.397 | 647.178 | -4.446 | |
74.384 | 9.106 | 2599.816 | 2599.816 | -1.718 | 11 | 1319947 | A | Columbus | 5661 |
24171 | 20050131 | 2004 | INDL | C | D | STD | DSW | 23334L102 | DSW INC | USD | 1 | 595.736 | 983.355 | 29.258 | 1607.104 | 343.375 | 18 | 25.899 | 473.051 | 359.556 | 790.641 | -19.448 | |
49.299 | 7.956 | 2746.345 | 2746.345 | -12.428 | 11 | 1319947 | A | Columbus | 5661 |
24171 | 20060131 | 2005 | INDL | C | D | STD | DSW | 23334L102 | DSW INC | USD | 1 | 743.689 | 1086.574 | 138.731 | 1745.25 | 165.995 | 18 | 25.899 | 491.867 | 558.728 | 857.632 | -183.418 | |
-43.011 | 19.696 | 2922.307 | 2922.307 | 13.224 | 11 | 1319947 | A | Columbus | 5661 |
24171 | 20070131 | 2006 | INDL | C | D | STD | DSW | 23334L102 | DSW INC | USD | 1 | 887.947 | 1267.217 | 160.221 | 1793.913 | 265.783 | 17.342 | 25.899 | 545.584 | 613.508 | 1037.169 | -150.913 | |
-185.011 | 20.558 | 3077.223 |
Fundamentals of Financial Accounting
ISBN: 978-0078025914
5th edition
Authors: Fred Phillips, Robert Libby, Patricia Libby