Preparation of Financial Statements
You are working as a Junior Corporate Accountant at TechFlow Solutions. The financial controller has just handed you the end-of-year adjusted trial balances for FY2026.
Your task is to transform this raw financial data into formal, structured financial statements. You must first construct a multi-step Income Statement to determine the company's profitability. Then, you will analyze the critical linkage between the statements by calculating the Ending Retained Earnings. Finally, you will build the Balance Sheet and prove that the fundamental accounting equation remains perfectly balanced.
Business Scenario
Pre-Lab Preparation
Topic : Financial Modelling (Excel-based)
1) Financial statement structure
2) Three-statement model overview
3) Assumptions and drivers
4) Free cash flow calculation
5) Sensitivity and scenario analysis
Task 1: Build Income Statement
You must first build the raw data repository in your spreadsheet.
1
Create the Data Table
a
In cell A1, type Account Name. In cell B1, type Balance (₹). Make both bold.
b
Enter the following exact financial balances into your spreadsheet from rows 2 through 15:
| Account Name | Balance (₹) |
|---|---|
| Sales Revenue | 500,000 |
| Cost of Goods Sold (COGS) | 200,000 |
| SG&A Expenses | 120,000 |
| Interest Expense | 10,000 |
| Income Tax Expense | 34,000 |
| Cash & Cash Equivalents | 80,000 |
| Accounts Receivable | 60,000 |
| Inventory | 40,000 |
| Property, Plant & Equipment (PPE) | 300,000 |
| Accounts Payable | 50,000 |
| Long-Term Debt | 150,000 |
| Common Stock | 100,000 |
| Beginning Retained Earnings | 44,000 |
| Account Name | Balance (₹) |
|---|---|
| Sales Revenue | |
| Cost of Goods Sold (COGS) | |
| SG&A Expenses | |
| Interest Expense | |
| Income Tax Expense | |
| Cash & Cash Equivalents | |
| Accounts Receivable | |
| Inventory | |
| Property, Plant & Equipment (PPE) | |
| Accounts Payable | |
| Long-Term Debt | |
| Common Stock | |
| Beginning Retained Earnings | |
| Dividends Paid | 0 |
The Income Statement
The Income Statement measures financial performance over a specific period. Set this up in columns D and E.
Step 1: Gross Profit Calculation
1) In cell D1, type TechFlow Solutions - Income Statement. Make it bold.
2) In cell D2, type Sales Revenue. In cell E2, link to the revenue value: =B2.
3) In cell D3, type Cost of Goods Sold. In cell E3, link to the COGS value: =B3.
4) In cell D4, type Gross Profit. In cell E4, calculate the difference: =E2-E3. (Output should be 300,000).
Step 2: Operating and Net Income Calculation
1) In cell D5, type SG&A Expenses. In cell E5, link to the SG&A value: =B4.
2) In cell D6, type Operating Income. In cell E6, calculate: =E4-E5. (Output should be 180,000).
3) In cell D7, type Interest Expense. In cell E7, link to the interest value: =B5
4) In cell D8, type Profit Before Tax (EBT). In cell E8, calculate: =E6-E7.
5) In cell D9, type Income Tax Expense. In cell E9, link to the tax value: =B6.
6) In cell D10, type Net Income. In cell E10, calculate the final bottom line: =E8-E9. (Output should be 136,000).
Task 2 : Analyze the Linkages
The Net Income from the Income Statement does not simply vanish; it belongs to the shareholders and flows directly into the Equity section of the Balance Sheet via Retained Earnings.
Calculate Ending Retained Earnings
1) In cell D12, type Statement of Retained Earnings. Make it bold.
2) In cell D13, type Beginning Retained Earnings. In cell E13, link to the raw data: =B14.
3) In cell D14, type Add: Net Income. In cell E14, link directly to your calculated Net Income: =E10. (This is the crucial linkage!)
4) In cell D15, type Less: Dividends Paid. In cell E15, link to the raw data: =B15.
5) In cell D16, type Ending Retained Earnings. In cell E16, calculate: =E13+E14-E15. (Output should be 180,000).
Task 3 : Prepare the Balance Sheet
The Balance Sheet is a snapshot of financial position at a single point in time. Set this up in columns G and H.
1
Total Assets
a) In cell G1, type TechFlow Solutions - Balance Sheet. Make it bold.
b) In cells G2 through G5, list the asset accounts: Cash, Accounts Receivable, Inventory, and PPE.
c) In cells H2 through H5, link to their respective values from the raw data table (=B7, =B8, =B9, =B10).
d) In cell G6, type Total Assets. In cell H6, calculate the sum: =SUM(H2:H5). (Output should be 480,000).
2
Liabilities
a) In cells G8 and G9, list the liability accounts: Accounts Payable and Long-Term Debt.
b) In cells H8 and H9, link to their respective values (=B11, =B12).
c) In cell G10, type Total Liabilities. In cell H10, calculate the sum: =H8+H9. (Output should be 200,000).
3
Shareholders' Equity & Validation
a) In cell G12, type Common Stock. In cell H12, link to the raw data: =B13.
b) In cell G13, type Ending Retained Earnings. In cell H13, link strictly to your calculation from Task 2: =E16.
c) In cell G14, type Total Equity. In cell H14, calculate the sum: =H12+H13.
d) In cell G16, type Total Liabilities & Equity. In cell H16, calculate: =H10+H14.
The Ultimate Check: Look at cell H6 (Total Assets) and cell H16 (Total Liabilities & Equity). If both numbers equal 480,000, your financial statements are perfectly linked and balanced!
Activity
Zenith Manufacturing Ltd.
You are provided with the adjusted trial balance for Zenith Manufacturing Ltd. for FY2026. Prepare the Income Statement, Statement of Retained Earnings, and Balance Sheet in a Excel sheet (Sheet2) using the exact structural formulas
Raw Data Set:
| Account Name | Account Type | Balance (₹) |
|---|---|---|
| Sales Revenue | Revenue | 8,50,000 |
| Cost of Goods Sold (COGS) | Expense | 3,80,000 |
| Operating Expenses (SG&A) | Expense | 1,90,000 |
| Interest Expense | Expense | 20,000 |
| Tax Expense | Expense | 65,000 |
| Cash & Cash Equivalents | Current Asset | 1,10,000 |
| Trade Receivables | Current Asset | 95,000 |
| Inventories | Current Asset | 75,000 |
| Property, Plant & Equipment (Net PPE) | Non-Current Asset | 5,20,000 |
| Trade Payables | Current Liability | 80,000 |
| Short-Term Borrowings | Current Liability | 30,000 |
| Long-Term Debt | Non-Current Liability | 2,40,000 |
| Account Name | Account Type | Balance (₹) |
|---|---|---|
| Sales Revenue | Revenue | |
| Cost of Goods Sold (COGS) | Expense | |
| Operating Expenses (SG&A) | Expense | |
| Interest Expense | Expense | |
| Tax Expense | Expense | |
| Cash & Cash Equivalents | Current Asset | |
| Trade Receivables | Current Asset | |
| Inventories | Current Asset | |
| Property, Plant & Equipment (Net PPE) | Non-Current Asset | |
| Trade Payables | Current Liability | |
| Short-Term Borrowings | Current Liability | |
| Long-Term Debt | Non-Current Liability | |
| Share Capital (Equity) | Equity | 2,00,000 |
| Beginning Retained Earnings | Equity | 1,15,000 |
| Dividends Paid | Financing Cash Outflow | 35,000 |