accounting excel need it within 1 hour

profileSim cheong
BIZ201Assessment3-StudentWorkbook1.xlsx
Home>Business & Finance homework help>Accounting homework help>accounting excel need it within 1 hour

BalanceSheet

Summary of Consolidated Balance Sheets
for 2015 - 2019
Vertical Analysis 2019 2018 2017 2016 2015 2019 Marking
$'000 $'000 $'000 $'000 $'000 Correct? Mark Allocated
ASSETS
Current assets
Cash and cash equivalents 47,450 41,793 30,591 45,207 53,323 Yes 0.32
Trade and other receivables and prepayments 311,114 2,616 27,602 37,883 30,363 Yes 0.32
Inventories 346,940 366,839 372,374 396,297 381,907 Yes 0.32
Derivative financial instruments 5,688 6,725 - 351 15,211 Yes 0.32
Total current assets 431,192 441,973 430,567 479,738 480,804 Yes 0.32
Non-current assets
Property, plant and equipment 383,487 424,076 460,211 445,379 469,006 Yes 0.32
Intangible assets 467,604 485,151 985,657 1,019,671 916,108 Yes 0.32
Derivative financial instruments 101 269 - 80 - Yes 0.32
Investment in associate - - - 9,203 -
Other non-current assets 4,228 1,529 2,094 2,271 2,614 Yes 0.32
Deferred tax assets - - - - 18,016
Total non-current assets 855,420 911,025 1,447,962 1,476,604 1,405,744 Yes 0.32
Total assets 1,286,612 1,352,998 1,878,529 1,956,342 1,886,548 Yes 1
LIABILITIES
Current liabilities
Trade and other payables 372,653 381,156 379,740 400,590 387,182 Yes 0.32
Provisions 64,386 70,007 87,295 94,228 85,728 Yes 0.32
Deferred income 8,295 10,294 9,817 10,812 6,997 Yes 0.32
Derivative financial instruments 132 64 7,944 7,127 99 Yes 0.32
Current tax liabilities 5,280 4,321 1,627 7,033 512 Yes 0.32
Other liabilities 373 472 591 795 871 Yes 0.32
Total current liabilities 451,119 466,314 487,014 520,585 481,389 Yes 0.32
Non-current liabilities
Borrowings 86,134 149,165 143,367 147,273 441,179 Yes 0.32
Provisions 12,273 11,856 13,821 19,754 21,198 Yes 0.32
Deferred income 80,158 80,629 75,927 69,702 75,112 Yes 0.32
Deferred tax liabilities 54,869 60,981 84,574 88,444 - Yes 0.32
Other liabilities -
Derivative financial instruments 3 64 958 2,819 4,654 Yes 0.32
Total non-current liabilities 233,437 302,695 318,647 327,992 542,143 Yes 0.32
Total liabilities 684,556 769,009 805,661 848,577 1,023,532 Yes 1
Net assets 602,056 583,989 1,072,868 1,107,765 863,016 Yes 0.36
EQUITY
Contributed equity 738,759 739,020 739,329 739,338 524,755 Yes 0.32
Retained earnings - - 342,146 379,483 335,366
Accumulated losses -138,641 -160,282 - - - Yes 0.32
Reserves 1,938 5,251 -8,607 -11,056 2,895 Yes 0.32
Capital and reserves attributable to owners of Myer Holdings 602,056 583,989 1,072,868 1,107,765 863,016 Yes 0.32
Non-controlling interests
Total equity 602,056 583,989 1,072,868 1,107,765 863,016 Yes 1
Total Marks 12

IncomeStatement

Summary of Consolidated Income Statements
for the period 2015 - 2019
2019 2018 2017 2016 2015 Determine the following Ratios: Formula 2019 2018 2019 2018
$’000 $’000 $’000 $’000 $’000 Is the answer correct? Mark Allocated Is the answer correct? Mark Allocated
Total sales value 2,991,795 3,100,554 3,201,866 3,289,568 3,195,626 Profitability
Concession sales 612,193 653,983 701,678 610,553 501,153 Gross Profit Gross Profit / Total Revenue Yes 0.9 Yes 0.9
Sale of goods 2,379,602 2,446,571 2,500,188 2,679,015 2,694,473 Net Profit Margin NPAT / Total Revenue Yes 0.9 Yes 0.9
Sales revenue deferred under customer loyalty program 34,545 36,583 34,847 38,861 40,122 Return on Assets NPAT / Average Total Assets Yes 0.9 Yes 0.9
Revenue from sale of goods 2,345,057 2,409,988 2,465,341 2,640,154 2,654,351
Other operating revenue 153,576 162,299 176,485 161,689 131,423 Efficiency
Cost of goods sold 1,336,194 1,387,903 1,421,394 1,527,552 1,495,382 Inventory Turnover COGS / Average Inventory Yes 0.9 Yes 0.9
Operating gross profit 1,162,439 1,184,384 1,220,432 1,274,291 1,290,392 Days Held in Inventory 365 / Inventory Turnover Yes 0.9 Yes 0.9
Other income 0 0 0 71 108
Selling expenses 822,832 831,192 819,055 842,217 828,906
Administration expenses 281,109 297,765 292,212 318,039 328,138 Liquidity
Share of net profit/(loss) of equity-accounted associate 0 0 1,176 620 0 Current Current Assets / Current Liabilities Yes 0.9 Yes 0.9
Dilution of investment in equity-accounted associate 0 0 1,338 0 0 Quick Current Assets - Inventory / Current Liabilities Yes 0.9 Yes 0.9
Restructuring & store exit costs 12,458 541,190 65,615 18,250 61,687
Earnings before interest and tax 46,040 -485,763 41,036 95,236 71,769
Finance revenue 556 397 436 906 753 Solvency
Finance costs 12,081 9,471 11,259 15,447 23,488 Debt Ratio Total Liabilities / Total Assets Yes 0.9 Yes 0.9
Net finance costs 11,525 9,074 10,823 14,541 22,735 Debt to Equity Total Liabilities / Total Equity Yes 0.9 Yes 0.9
Profit before income tax 34,515 -494,837 30,213 80,695 49,034 Interest Coverage EBIT / Interest Expense Yes 0.9 Yes 0.9
Income tax expense 10,041 8,835 18,274 20,152 19,208 Total Marks 9 Total Marks 9
Total Profit for the period 24,474 -486,002 11,939 60,543 29,826
`

Cashflow

Summary of Consolidated Cash Flow
for the period 2015 - 2019
2019 2018 2017 2016 2015
$’000 $’000 $’000 $’000 $’000
Cash flows from operating activities
Receipts from customers (inclusive of goods and services tax) 2,769,783 2,857,300 2,931,853 3,101,149 3,096,099
Payments to suppliers and employees (inclusive of goods and services tax) -2,631,291 -2,726,701 -2,744,651 -2,915,467 -2,946,252
138,492 130,599 187,202 185,682 149,847
Other income - - - 71 108
Interest paid -9,815 -9,097 -10,165 -15,894 -22,601
Tax paid -13,598 -12,301 -27,759 -20,369 -30,439
Net cash inflow from operating activities 115,079 109,201 149,278 149,490 96,915
Cash flows from investing activities
Payments for property, plant and equipment -35,647 -42,990 -88,452 -40,479 -63,099
Payments for intangible assets -17,020 -48,313 -24,217 -11,891 -17,276
Payment for brands acquisition - - - - -1,000
Payment for acquisition of assets, under business combination - - -13,000 - -
Payment for acquisition of non-controlling interests - - - - -
Lease incentives and contributions received 7,971 4,455 16,758 1,856 18,225
Net investment in associate - - -966 -8,680 -
Interest received 556 411 421 943 800
Net cash outflow from investing activities -44,140 -86,437 -109,456 -58,251 -62,350
Cash flows from financing activities
Repayment of borrowings, net of transaction costs -64,915 - -5,000 -295,000 17,927
Dividends paid to equity holders of the parent - -16,426 -49,276 -16,426 -17,211
Dividends paid to non-controlling interests - - - - -
Loans to/repayment of loans by employees - - - - -
Payment for acquisition of treasury shares -261 -309 -196 - -
Proceeds from the issue of shares, net of transaction costs - 5,000 - 212,011 23
Other -106 173 34 60 455
Net cash outflow from financing activities -65,282 -11,562 -54,438 -99,355 -54,806
Net decrease/increase in cash and cash equivalents 5,657 11,202 -14,616 -8,116 -20,241
Cash and cash equivalents at the beginning of the financial period 41,793 30,591 45,207 53,323 73,564
Cash and cash equivalents at end of period 47,450 41,793 30,591 45,207 53,323