Order from us for quality, customized work in due time of your choice.
If you do not already have Excel installed on your computer, please download it (for free) from the UMaine IT page here: https://umaine.edu/it/microsoft-office365/ . Please do NOT do the project in Numbers or Google Sheets. I cannot open them and will not grade them. Virtually no one in the business world uses either, so get used to it.
Download the FSA Spreadsheet from its folder in Brightspace (if you’re reading this, you’re already there) and open it.
Identify which two companies you will be comparing: Enter your first and last names in the boxes as indicated (D3 and K3). Use the first letter of your first and last names to select companies from columns R-AC (First name) and AE-in the appropriate cells (D5 and K5).
Copy the financial statements from 2020 for Company 1 and paste it in Column B. To do so, highlight the cells you want to copy (Rows 9 through 31 and Rows 35 to 48 – do these separately!) and use Ctrl-C. Then select cell B9 and use Ctrl-V to Paste. (You can also right-click and select copy and paste from the popup menu)
IMPORTANT NOTE: YOU SHOULD NOT TYPE ANY NUMBERS ANYWHERE IN THIS SPREADSHEET. ALL DATA CAN EITHER BE COPIED FROM ELSEWHERE WITHIN THE SHEET OR CALCULATED USING FORMULAS. UNDER NO CIRCUMSTANCES SHOULD YOU CALCULATE SOMETHING ON YOUR CALCULATOR AND THEN TYPE THE NUMBER INTO EXCEL. IF YOU DO SO I WILL TAKE POINTS OFF!
Repeat the process for 2019 for Company 1 and for 2020 and 2019 for Company 2 (pasting the numbers into columns D, I, and K).
Do you see a bunch of ####s? If so, that just means that the numbers are too big for the cell. You can click and drag the column borders up at the top of the worksheet to make them larger! Or you can doubleclick the column’s right border up top and it will auto-size the column for you.
Create Common Size Financial Statements for both companies and both years.
Divide all Balance Sheet accounts by Total Assets.
Divide all Income Statement accounts by Total Revenue
See the following Excel Videos for help:
Mathematical formulas: divide, subtract (Chapter 2)
How to use Absolute Cell Referencing in Excel (Chapter 10)
This will allow you to copy and paste the formula from Cell C9 to the rest of the balance sheet accounts and Cell B35 to the rest of the income statement accounts
Note that you will need a new absolute reference for each year’s financial statements. Don’t divide 2020’s Cash by 2019’s Total Assets!
Highlight your common size calculations and click the % button within the “Number” section of the top menu ribbon (Under the Home tab). This will turn all those decimals into nice pretty %s.
Calculate % Change of All Balance Sheet and Income Statement accounts for both companies.
To calculate the change in cash, enter =(B9 – D9)/D9 into cell G9.
Copy and paste that cell’s formula into Column G for the rest of the financial statement lines for Company 1
Repeat the process for Company 2 using a similar formula.
Use that little % button here too! No ugly decimals for us!
Perform Ratio Calculations with Excel formulas for Company 1 in Cells D50 to D63. Please note that I only want ratios for a single year.
Current Ratio: Total Current Assets / Total Current Liabilities
Working Capital: Total Current Assets – Total Current Liabilities
Accounts Receivable Turnover: Revenue / Average Accounts Receivable
Use the formula B35/(AVERAGE(B10, D10))
Make sure you have two )s at the end!
Assume that all revenues were Credit Sales. That’s how business typically works.
Average Collection Period: 365 / Accounts Receivable Turnover
Use the formula = 365/B52
For funsies, type a different number into B35 and see how both AR Turnover and Average Collection Period update!
Click the undo button to return Revenue to the correct number.
Inventory Turnover: Cost of Goods Sold / Average Inventory
Adapt the formula from c. above
Don’t forget that this is COGS, not Sales!
Days inventory Held: 365 / Inventory Turnover
Adapt the formula from d above.
Funsies again, if you wish, followed by undo.
Debt to Assets: Total Liabilities / Total Assets
Debt to Equity: Total Liabilities / Total Equity (note that this is different than Total Assets. If your number is the same as g above, you screwed up!)
Times Interest Earned: “Operating Income” / Interest Expense
Asset Turnover: Revenue / Average Total Assets
See c. above for how to do the average
Return on Assets: Net Income / Average Total Assets
See c. above for how to do the average
Return on Equity: Net Income / Average Total Equity (note that this is different than Total Assets)
See c. above for how to do the average
Let’s make these ratios pretty!
For the two return ratios, use that % button again.
For all the others, click on the button that looks like 00->0 to the right of the % button. This will decrease the number of decimal places shown. We don’t need more than one, so turn that 2.353672346 into 2.4!
Verify that the numbers make sense. If a company has an AR turnover of 500, that means they are collecting 100% of their receivables more than once a day. This is, obviously, dumb. We have numbers from the examples in the text and the notes. If your calculation is a factor of 10 different from those, you’re probably wrong.
Copy the formulas from Column D to column K.
Highlight cells B50 toB63 and type Ctrl-C
Click in Cell I50
Type Ctrl-V
Look! Now you don’t have to type in all those formulas again! Or even redo the formatting! Yay Excel!
Analyze the data!
For each ratio, indicate which Company (1 or 2) has a better ratio in Column N.
Note that higher is not always better. Think about what each ratio means and whether you would like that number to be higher or lower. Review the notes or the text (or the internet) if necessary. This is part of the Big Payoff of making all MBS students take ACC 201.
Be consistent! Average Collection Period is just AR Turnover divided into 365. If company 1 is better for Turnover, it’s going to be better for Average Collection Period.
Do not enter anything in cells N57 or N58. Debt to Assets and Debt to Equity, being leverage ratios, are ambiguous without deeper analysis. That is, you can’t say that one company’s ratio is better or worse, just higher or lower.
Order from us for quality, customized work in due time of your choice.