Financial Modelling

profilezyber
ModelcasestudyFinancialStatementforSubterrainFinancialspreadsheetstyledv37.xlsx
Home>Business & Finance homework help>Financial Modelling

P&L mgmt

SUBTERRAIN LIMITED
Income Statement
Year ended 31 December 2021 2022 2023 2023
Actual Actual Budget Forecast * 2022 vs 2021 2023 vs 2022
Acc code ($000s) ($000s) ($000s) ($000s) ($000s) ($000s)
10000 Sales revenue 92,574 90,150 94,500 92,000 (2,424) 4,350
Opening Inventory (11,089) (11,159) (12,400) (12,400) (70) (1,241)
Purchases (44,598) (44,341) (41,994) (41,564) 257 2,347
Closing Inventory 11,159 12,400 11,000 11,000 1,241 (1,400)
Cost of sales (44,528) (43,100) (43,394) (42,964) 1,428 (294)
Gross profit 48,046 47,050 51,106 49,036 (996) 4,056
51.9% 52.2% 54.1% 53.3%
Overheads
82105 Accounting and consulting fees 96 135 135 135 39 - 0
84302 Advertising and marketing 1,900 2,050 2,200 2,400 150 150
75000 IT Expenses 1,774 1,568 1,700 1,600 (206) 132
84306 Legal and professional fees 75 85 85 85 10 - 0
84380 Motor vehicle expenses 280 220 220 220 (60) - 0
76000 Power and utilities 767 725 725 800 (42) - 0
70000 Rent 9,620 11,049 11,530 11,600 1,429 481
84390 Repairs and Maintenance 455 345 345 370 (110) - 0
64110 Staff training and amenities 500 670 750 770 170 80
61000 Superannuation 1,812 1,888 1,940 1,970 76 52
64350 Travel 420 487 600 500 67 113
60000 Wages and salaries 19,074 18,880 19,400 19,700 (194) 520
85300 Other expenses 6,903 5,463 5,500 5,300 (1,440) 37
Total overheads 43,676 43,565 45,130 45,450 (111) 1,565
EBITDA 4,370 3,485 5,976 3,586 (885) 2,491
Depreciation 1,200 1,020 1,050 1,050 (180) 30
Operating profit 3,170 2,465 4,926 2,536 (776) 2,461
Financing income 760 800 - 0 - 0 40 (800)
Interest (141) (146) (150) (150) (5) (4)
PBT 3,789 3,119 4,776 2,386 (670) 1,657
Income tax (1,158) (873) (1,336) (716) 285 (463)
Profit after tax 2,631 2,246 3,440 1,670 (385) 1,194
Dividends 3,960 - 0 - 0 - 0 (3,960) - 0
* 2023 Forecast includes 5 months actual results (Jan-May) and 7 months budget
© 2022 Chartered Accountants Australia and New Zealand ABN 50 084 642 571. All rights reserved.

Balance Sheet

SUBTERRAIN LIMITED
Statement of Financial Position
As at 31 December 2019 2021 2022 2023
Actual Actual Budget
($000s) ($000s) ($000s)
CURRENT ASSETS
Cash and cash equivalents 21,426 16,543 16,230 20,620
Trade receivables 1,140 1,232 1,038 1,065
Prepayments and other receivables 1,308 1,609 1,080 1,580
Inventories 11,089 11,159 12,400 11,000
Total current assets 34,963 30,543 30,748 34,265
NON-CURRENT ASSETS
Property, plant and equipment 19,160 20,405 18,385 18,335
Intangible assets 592 716 716 716
Total non-current assets 19,752 21,121 19,101 19,051
TOTAL ASSETS 54,715 51,664 49,849 53,316
CURRENT LIABILITIES
Trade payables 7,338 7,453 7,920 8,006
Current borrowings 1,502 842 805 820
Employee benefits 1,144 1,082 1,056 1,600
Taxation payable 888 117 90 250
Other payables 3,065 3,256 3,583 3,631
Total current liabilities 13,937 12,750 13,454 14,307
NON-CURRENT LIABILITIES
Non-current borrowings 2,755 2,585 1,780 960
Total non-current liabilities 2,755 2,585 1,780 960
Total liabilities 16,692 15,335 15,234 15,267
NET ASSETS 38,023 36,329 34,615 38,049
EQUITY
Contributed equity ($1 par) 12,000 12,000 12,000
Retained earnings 24,329 22,615 26,049
TOTAL EQUITY 36,329 34,615 38,049
© 2022 Chartered Accountants Australia and New Zealand ABN 50 084 642 571. All rights reserved.

Cashflow

SUBTERRAIN LIMITED
Statement of Cash flow
As at 31 December 2021 2022 2023
Actual Actual Budget
($000s) ($000s) ($000s)
Operating cashflows
Cash receipts
Sales receipts from customers 92,482 89,940 94,377
Other cash receipts (301) 29 0
Cash payments
Payments to suppliers (44,506) (44,874) (41,720)
Payments to staff (20,948) (20,794) (21,296)
Interest (141) (146) (150)
Taxes (1,929) (900) 1,226
Other cash expenses (22,790) (23,526) (24,790)
Net operating cashflows 1,867 (271) 7,647
Investing activities
Purchase of PP&E (2,720) 0 0
Net investing cashflows (2,720) 0 0
Financing activities
Financing income 760 800 0
Repayment of loan (830) (842) (805)
Dividends paid (3,960) 0 0
Net financing cashflows (4,030) (42) (805)
Increase/(decrease) in cash (4,883) (313) 6,842
Reconciliation of cash balance
Cash balance at start of year 21,426 16,543 16,230
Cash balance at end of year 16,543 16,230 20,620
Increase/(decrease) in cash (4,883) (313) 4,390
© 2022 Chartered Accountants Australia and New Zealand ABN 50 084 642 571. All rights reserved.

Operational data

Actual Budget
Acc code 2022 2023
($000s) ($000s)
Wages
60100 Retail stores 14,800 15,200
60200 Distribution 1,650 1,700
60300 Head office 2,430 2,500
18,880 19,400
Rent
70100 Retail stores 9,369 9,800
70200 Warehouse 1,200 1,250
70300 Head office 480 480
11,049 11,530
© 2022 Chartered Accountants Australia and New Zealand ABN 50 084 642 571. All rights reserved.