Step 1: Create your Excel Spreadsheet that will be uploaded and graded
• Name your spreadsheet as follows: LastName_FirstName_Name of your 10-K company_FYE (date of your latest 10-K e.g. 12-31-23)
• Download the latest 10-K financial statements onto your computer. Copy and paste (special) the Balance Sheet and Income Statement into your named spreadsheet on separate worksheets (tabs). Rename the tabs BS 10-K ticker symbol (e.g. BS DHR) and IS 10-K ticker symbol (e.g. IS DHR)
• Insert your company name on the first line of each of these spreadsheets.
• Download the previous year’s 10-K financial statements onto your computer. Copy the balance sheet into your BS worksheet so that you now have 3 years of Balance Sheet data. Hide the duplicate year so that only 3 years are displayed. Realign that second balance sheet so that the total assets, total stockholders’ equity and total liabilities and stockholders’ equity are on the same line for all 3 years.
• Highlight (in red) and bold each of the numbers that you will be referring to in your formulas as you go along. To start with do this for Total assets, total stockholders’ equity and total liabilities and
stockholders’ equity.
• Create a new line under the Balance Sheet entitled “Total Liabilities.” Calculate Total Liabilities for all 3 years by subtracting Total Liabilities and SE from Total SE. If your balance sheet already has a line called Total Liabilities then simply refer to that cell. See the following example: Danaher Balance Sheet FVE 12-31-23)1.xlsx J
Step 2: Prepare another worksheet tab called Midterm.
• Using the following example 2200 Midterm Excel example.pdf as your guide, list your ratios and do your calculations for the past two fiscal years for all of the ratios on the ppt slides. Be sure to perform all calculations using Microsoft Excel (no calculators). Do not type in the data from your source documents (10-K BS and IS) but instead refer to their cells.
• Properly format your calculations e.g. “returns” should be formatted as percentages; dollars from the 10-K as dollars remove all decimal places.
Step 3: Format your spreadsheet for proper printing
• Go to Page Layout – Click on the “handle” in the bottom right corner of that tab. The Page Setup dialog box pops up.
• Under Page choose fit to 1 wide by 1 tall.
• Under Header/Footer choose “Custom Footer” – click on the open file folder (Insert File Path) – click on “OK”
• Under “Custom Header” – Move your curser to the right section. Click on the calendar “date” – hit the space bar three times then click on the clock- click on “Ok”
• Under the Sheet Tab – click the gridlines box. Click on OK. • Choose 2 of the 6 ratios and prepare a written analysis (3-4 sentences) of what your data shows you. Feel free to add additional calculations on your midterm Excel spreadsheet to help you with your analysis. (Such as year over year variance and percent change.
Step 5: Upload both your Word and Excel documents.
Under no circumstance are you to share your Excel spreadsheet with any of your classmates or use a spreadsheet that was prepared by one of your classmates. This can result in both students (the sharer and
