Creating Excel-based Financial Statements
A step-by-step Excel tutorial that guides students through building and validating financial statements.
This presentation is a step-by-step Excel tutorial designed for Introduction to Financial Accounting (Principles I) courses. Students follow 40 PowerPoint slides with individually numbered steps that guide them through building, formatting, and validating Excel-based financial statements. The assignment emphasizes foundational Excel skills—such as formulas, formatting, worksheet management, and data validation—while reinforcing core financial accounting concepts through hands-on application.
- Workbook Setup and Data Preparation
- Spreadsheet Setup and Navigation
- Saving workbooks using the correct Excel file format (
.xlsx) - Recording and referencing a unique Spreadsheet ID
- Using the Go To command for efficient worksheet navigation
- Freezing panes to keep headings visible during data entry
- Text and Data-Cleaning Functions
- Core Formulas and Logical Functions
- Creating calculations using:
SUMIF
- Applying formulas to verify accounting relationships (e.g., debits equal credits)
- Using absolute and relative cell references appropriately
- Rounding and Financial Accuracy
- Applying rounding functions:
ROUNDROUNDUPROUNDDOWN
- Applying rounding functions:
- Mathematical, Financial, and Logical Functions
- Formatting and Professional Presentation
- Applying conditional formatting to flag key conditions (e.g., “In Balance” vs. “Out of Balance”)
- Formatting worksheets for professional appearance:
- Centering and indenting text
- Wrapping headings
- Adjusting row heights and column widths
- Applying bolding, underlining, and thick borders
- Using Comma Style for numeric readability
- Worksheet and Workbook Management
- Inserting, renaming, moving, and organizing worksheets
- Structuring and organizing the Chart of Accounts
- Filling series to streamline data entry
- Deleting unnecessary worksheets
- Transposing account data
- Removing duplicate entries to clean datasets
- Data Validation and Worksheet Protection
- Creating data validation rules to control allowable inputs
- Understanding worksheet protection and which cells are unlocked for editing
- Working safely within protected templates without needing a password
- Financial Statement Construction
- Building Excel-based financial statements step by step
- Recording and calculating accounting adjustments, including:
- Deferred revenue
- Interest
- Salaries and payroll taxes
- Rent and other accruals
- Creating post-closing trial balances
- Finalizing worksheets for submission
- Review, Audit, and Output Tools
- Auditing formulas using Show Formulas
- Creating Waterfall Charts to visualize financial results
- Adjusting page layout settings for printing and PDF output
This assignment covers approximately 25% of the material tested on the Microsoft Office Specialist Excel Certification exam.
Step 1: Record your 4-digit Spreadsheet ID
The Spreadsheet ID is used throughout the assignment to help ensure each student submits unique work.
- Students: Your Spreadsheet ID appears on your dashboard.
- Faculty/Teaching Assistants: You may use any 4-digit number as your Spreadsheet ID.
Step 2: Download the Excel Template
If you already downloaded the exercise from your dashboard, you may skip this step. You’ll use this template while following along with the video.
↓ Download the Excel TemplateMost cells in the template are intentionally protected.
You do not need a password—only the color-coded cells are meant to be edited.
Step 3: Complete the assignment alongside the instructional video
Choose the tutorial that matches your Excel environment:
Step 4: Submit your completed spreadsheet via your dashboard
Keep these tips in mind to ensure clean submission of your work:
- Double-check that your spreadsheet is not an .XLTX workbook (see Step 2 of 40).
- Close Excel completely before attempting to upload your work.
- Make use of the alternate submission methods if needed.
How This Assignment Is Graded
An automated tool is used to grade your work based on completion and accuracy, including partial points for incorrect steps. Your instructor determines how scores are applied toward course credit.
Need help?
Support
Support is email-based. In rare instances Zoom meetings are available when issues cannot be resolved otherwise.