Excel Guidelines for the Individual Project
1. Have one tab for selected items from the financial statement and another tab to calculate ratios.
See below for an example. In particular, look at how the Cu
ent Ratio for 2010 is calculated in the “Trend and Ratio Analysis” tab.
*Notice that inputs (typed in numbers) are in blue (per industry standard) and formulas are in black.
*** Note: Numbers are made up and not from Nordstrom’s actual financials.
Do this instead of putting in =4,824/1,879.
You’ll get the same answer but refe
ing to the numbers in the “Key Financial Info” tab allows you to minimize mistakes (and you can copy and paste this formula to other years)
2. Professionalism: At a minimum:
a. Font size and font type should be consistent across the page
. Numbers should be rounded (one decimal is good)
For extra help in Excel, here are some resources:
1. Lynda.com (online tutorials – just search for “basic Excel”)
https:
itservices.usc.edu/lynda
2. Professor Julie Suh’s book on financial modeling using Excel
E-book or hard copy available directly from the publisher:
https:
he.kendallhunt.com/product/model-boss-excel-tutorial-financial-modeling
Also available on Amazon:
https:
www.amazon.com/Model-Like-Boss-Tutorial-Financial/dp/152492170X
ef=sr_1_1?ie=UTF8&qid= XXXXXXXXXX&sr=8-1&keywords=model+like+a+boss
1
Spring 2021
BUAD 280: Introduction to Financial Accounting
Time-Saver Tip: Read carefully before you start your work.
Project Title:
Financial Accounting – A Tale of Two Companies
Background:
For this assignment, you must work individually. Please select one of the two pairs:
Option 1: Lockheed Martin vs. Northrup Grumman
Option 2: Ford Motor Company vs. General Motors
Financial statement information must come from one of these two sites:
· Annual Reports to Shareholders (through company investor relations web sites)
· SEC filings such as ‘10-K’s (available at www.sec.gov under ‘EDGAR Filers’). Be sure to use the financial statements that are contained within the ‘10-Ks’. Some financial information in the 10-Ks looks like financial statements BUT IS NOT. Think of that financial information as ‘fools’ gold’.
· You may benefit from what financial analysts have written about your firms in these outlets:
· Business news agencies (e.g., www.wsj.com, www.money.com, www.foxbusiness.com, etc.)
1) Evaluate the two companies by creating a spreadsheet-based model using annual data from the last three fiscal years. [For convenience, use the data for the XXXXXXXXXXyears to avoid complications from actual fiscal year-ends.] At a minimum, you must do the following:
a. For the five ratios you selected from those listed on the next page, calculate the ratio values for both companies for each of the last three fiscal years.
. Tip: Make sure to practice good Excel modeling skills and not hardcode numbers in your ratio calculations! [Refer to the Excel Guidelines which have been provided.]
2) Use your analysis to determine which of the two companies is performing “better” (or “worse”).
a. Comment on how the performance differs across companies and across time. Use your knowledge of the industry, consumers, trends, etc. to help you with your analysis. Don’t just comment on the ratios being higher or lower – discuss the implications and why they are important. Information gathered from business news agencies can be very helpful in this effort. Be sure to give appropriate citations for any of this information you use.
. Tip: Look through the companies’ annual reports for additional information that might help you with your analysis. The MD&A (Management Discussion & Analysis) will give you the management’s overview of the previous year (Note: MD&A is not audited). The Notes to the financial statements provide additional audited information – they are ‘footnotes’ to the financial statements.
c. Comment on how racial & ethnic diversity is addressed in the company’s corporate mission (if at all). How has the company dealt with diversity in how it serves its customers or sells its products? How about diversity in relation to its employees, leadership, or board of directors? You may need to look outside financial statements for this information (news articles, press releases, etc.).
d. Your write-up must be no more than 500 words. Show the word count at the end of your report word document. Make sure your write-up adheres to professional standards of writing (i.e., co
ect punctuation, grammar, structure, etc.). Your write-up should not read like one long email.
Continued on next page.
This outline will help you organize your write-up:
Brief introduction
Your conclusions as to which firm is the better performing
Your support for your conclusions
Your comments about how racial & ethnic diversity is a positive or a negative for these firms’ futures and the basis for your comments
Briefly recap your conclusions
Required:
One document will be submitted. This document will contain the write-up and the excel spreadsheet. We will have a ‘dry-run’ to ensure you are able to submit a combined word document and an excel spreadsheet as one document.
1) Write-up (no more than 500 words). Show your word count at the end of the write-up. The excel spread sheet does not count as part of the 500 words.
2) Excel spreadsheet with your financial data and ratio calculations
Items 1 & 2 must be combined into one document for submission.
●Submit only your final project. Duplicate submissions cause the ‘elves’ extra work. They become grouchy and it may tarnish their ‘end-of-semester cheerful spirit’. There may be a reduction in score for duplicate submissions.
Selected Ratios:
●For the five ratios you select, make sure you calculate the values in the excel spreadsheet you submit.
●If the firm calculates a ratio and reports it in its 10-K, do not use the firm’s reported value. [Sometimes, firms create a unique definition of the ratio to make themselves ‘look better’.]
●Can you imagine my surprise that they might do that?
●The formulas and definitions of these ratios are available in the text. Using other definitions or formulas creates risk for your score on the project. Comprendez?
●Inventory Turnover
●Cu
ent Ratio
●Return on Assets
●Gross Profit Rate
●Profit Margin
●Asset Turnove
●Debt to Equity Ratio
2