Group#ABC CorpAssignment1 (3) [Protected View] File Home Insert Draw Page Layout Formulas Data Review View Help Tell me what you wa i PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. 35 Xfx A 8 0 D G H H K M 2011 2012 2013 2014(Current) 2015(Projected) Rever 1083 1,058 1564 690 44 529 369 30 3.80 3 3 5 787 355 29 366 4 0 754 4 0 1.207 295 304 357 0 0 o 0 Cost of sales Purchases Freight in o Labour Depreciation amortization 2 Other charges 3 Total cost of sales 4 15 Gross profil 16 17 Other income 18 19 Distribution costs 20 Salaries 21 Commissions 22 Traveling 23 Advertising 24 Depreciation amortization 25 Other charges 26 Total distribution costs 27 28 Administrative expenses 29 Salaries 30 Leasing 31 Depreciation amortization 2 Other ciates 33 Total administrative expenses 34 35 Financing costs 36 37 Total expenses less other income 38 39 Profit before tares 40 41 Income tax expense 34 33 12 7 2 10 98 37 92 11 9 2 0 91 47 16 10 3 3 0 117 94 20 13 20 147 102 20 9 9 11 142 107 26 24 16 173 3 17 14 248 240 304 48 64 53 10 18 12 46 41 43 Profit for the year 38 44 Statement of Income Statement of Financial Position 4 Type here to search O i 0 50 ? * File Home Insert Draw Page Layout Formulas Data Review View Help PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safe A1 Statement of Financial Position B G H 90 14 col 24 128 47 156 14 ol 50 220 62 238 14 0 0 252 93 81 158 159 A 3 Assets 4. Non-current assets 5 Property plant and copment S Goodwill 7 Investments 8 Due to thareholders 3 Sub tota 10 Accumulated depreciatioremortization 11 12 Total non current assets 13 14 Current assets 1 Irwentones Trade recevables 19 Prepaid expenses Cathed casheuvent 19 Che current 20 Other current the 21 22 Total current assets 23 24 Total assets 109 247 3 3 10 0 114 241 4 19 0 0 27 299 14 13 0 0 369 368 525 450 526 684 26 Equity and liabilities 27 Share Capital 29 Flanedbringe 29 Other comprehensive incomilos 30 31 Total equly attributable to owners 25 125 0 25 121 10 25 212 0 50 196 237 Total non-current liabilities 63 51 74 94 85 180 108 35 Current liabilities Trade and other parts 17 Short-borrowing 38 Cure portion of long term 33 AOCHU Dinar Cutraliasily 41 Total current liabilities 12 13 Total abilities 132 162 158 HO 20 23 59 0 207 279 373 300 330 447 528 684 45 Total equity and liabilities 48 Net Assets Equity Uabilities 0 19 Statement of Income 0 0 Statement of Financial Position F Type here to search O RE O Chip esc ? 13 4 15 1 7 @ 2 # 3 $ 4. % 5 E C 0 bottom, what does this signify? (Located on sheet 2 Row 47 in the excel file). AutoSave COT C Book 1 Excel File Home insert Page LINYout Formulas C Review View Help X. Cut Calibri 11 - A be, Wrap Tex US Copy B ED Format Painter A Merge Clipboard For Altonment SIGN IN TO OFFICE It looks like your stored credentials are out of date. Please Sign in Julian.Miletic ES2 B 171 125 O 212 O 150 196 237 63 51 A 28 Retained earnings 20 Other comprehensive incomel(los) 30 31 Total oquity attributable to owners 32 33 Total non-current liabilities 34 35 Current liabilities 36 Trade and other payables Short-term borrowings 38 Current portion of long-term debt 30 Accruals 30 Other current liability Total current Habilities 42 Total labilities 94 85 o 130 106 O 137 198 O 38 O 373 o 237 O 279 300 330 447 460 626 684 45 Total equity and liabilities 26 Net: Assets = Equity Liabilities O Explain your team's plan to finance the purchase of the additional inventory a warehouse? What is the total cost of the expansion? What is the total cost of financing for the expansion? (Hint: The company has been preapproved for a torm loan newall or harmoon to retninadmorninae from the nravioure unor O W ? Assignment 1 FIN 1013 Case study analysis. Read the Case below regarding ABC Company. Use the information contained in the Case to fill out the Excel Document found in Moodle. (ABCCompAssignment1.xlsx) Work in your Group to follow the steps below for Assignment 1. I Steps: 1) Construct Financial Statements using Excel. Your group's Job is to fill out the values for the years 2014 (current) and the 2015 (projected) in the Statement of Income and Statement of Financial Position (fill all the green cells). If no information is given about a particular value assume it is the same as last years. Pay close attention to the potential sources of financing, adjust the Statement of Income and Statement of Financial Position accordingly in excel. When you are finished with the Excel Document save it as Group#ABC CorpA1. All Values shown in Excel are in thousands of dollars. 2) Ratio Analysis (Read the CASE and complete step 1 FIRST) Calculate and analyze the following Ratios for the years 2014 and 2015. What do you notice about the change in these Ratios? Provide a brief explanation for the reason of the change, or whether the Ratios below should be any cause for concern. Current Ratio, Return on Assets, Quick Ratio, Net Working Captial 3) Discussion Questions (Read the CASE and complete step 1 FIRST) i) Did you decide to Rent or Buy the Warehouse? Why? (Hint: Compare the cost of financing) In the Statement of Financial Position explain why there are only zero's at the bottom, what does this signify? (Located on sheet 2 Row 47 in the excel file). FOR X SOOTH or Some Yoyote Wervers Cervera Ghe SONO os Explain your team's plan to finance the purchase of the additional inventory and warehouse? What is the total cost of the expansion? What is the total cost of financing for the expansion? (Hint: The company has been preapproved for a short- term loan, as well as has access to retained earnings from the previous year THE CASE: It is currently Q4 in 2014 and ABC corporation has placed your team in charge of preparing the company's Income Statement and Balance Sheet (Statement of Financial Position) for 2014, as well as the projected statements for 2015. You are told that sale revenue has increased 6% from the 2013 values. ABC Company also plans to expand their inventory to meet growing demand and they forecast sales revenue to increase by 35% in 2015 (from current values). 40,045.00$ of additional purchases wer made in 2014 (Paid for with Retained Earnings), with another 230,545.00 planned to be purchased in th future to expand inventory in 2015. Along with the increase in inventory, Freight expenses are expected to double. ABC has been preapproved a short-term loan in the amount of $100,000 today to be repaid ir full at the end of 2015 to finance this expansion, the rest will be paid for using the companies Retained Earnings. The total financing cost of the loan will be $9,500. Labour cost is expected to be the same as last year and are expected to increase by 15% in 2015. ABC is also planning to hire 2 new employees on salary, one for Distribution and one as Administrative staff in 2015, they will be paid $40,000/year each. Commissions are expected to increase by $30,000 in 2015. Plant Property and equipment are also the same as last year (2013), however, in the coming year (2015) the company is faced with a choice to buy or rent a warehouse to house the additional inventory. The building may be purchased outright for 350,000$ or leased on a one-year term for $90,000 spilt into 12 equal monthly payments. Assume a 15% down payment is required to buy the land, with payments made monthly amortized over 25 years, and the interest rate on the mortgage is fixed at 2.5%. Whether you decid to rent or buy the land is up to your team to decide, either way provide a rationale and adjust your financial statements for the projected year accordingly. **if you choose 'buy', add the principal payments of the loan for 12 months and include them in the "current portion of long term debt" cell in the Statement of Financial Position. Add the total financing cost of the loan (interest portion less the principal) for 12 months and add to existing financing costs (Cell A35) in the Statement of Income. ** If you choose lease, add the total monthly payments under "leasing" in the Statement of Income w Group#ABC CorpAssignment1 (3) [Protected View] File Home Insert Draw Page Layout Formulas Data Review View Help Tell me what you wa i PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. 35 Xfx A 8 0 D G H H K M 2011 2012 2013 2014(Current) 2015(Projected) Rever 1083 1,058 1564 690 44 529 369 30 3.80 3 3 5 787 355 29 366 4 0 754 4 0 1.207 295 304 357 0 0 o 0 Cost of sales Purchases Freight in o Labour Depreciation amortization 2 Other charges 3 Total cost of sales 4 15 Gross profil 16 17 Other income 18 19 Distribution costs 20 Salaries 21 Commissions 22 Traveling 23 Advertising 24 Depreciation amortization 25 Other charges 26 Total distribution costs 27 28 Administrative expenses 29 Salaries 30 Leasing 31 Depreciation amortization 2 Other ciates 33 Total administrative expenses 34 35 Financing costs 36 37 Total expenses less other income 38 39 Profit before tares 40 41 Income tax expense 34 33 12 7 2 10 98 37 92 11 9 2 0 91 47 16 10 3 3 0 117 94 20 13 20 147 102 20 9 9 11 142 107 26 24 16 173 3 17 14 248 240 304 48 64 53 10 18 12 46 41 43 Profit for the year 38 44 Statement of Income Statement of Financial Position 4 Type here to search O i 0 50 ? * File Home Insert Draw Page Layout Formulas Data Review View Help PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safe A1 Statement of Financial Position B G H 90 14 col 24 128 47 156 14 ol 50 220 62 238 14 0 0 252 93 81 158 159 A 3 Assets 4. Non-current assets 5 Property plant and copment S Goodwill 7 Investments 8 Due to thareholders 3 Sub tota 10 Accumulated depreciatioremortization 11 12 Total non current assets 13 14 Current assets 1 Irwentones Trade recevables 19 Prepaid expenses Cathed casheuvent 19 Che current 20 Other current the 21 22 Total current assets 23 24 Total assets 109 247 3 3 10 0 114 241 4 19 0 0 27 299 14 13 0 0 369 368 525 450 526 684 26 Equity and liabilities 27 Share Capital 29 Flanedbringe 29 Other comprehensive incomilos 30 31 Total equly attributable to owners 25 125 0 25 121 10 25 212 0 50 196 237 Total non-current liabilities 63 51 74 94 85 180 108 35 Current liabilities Trade and other parts 17 Short-borrowing 38 Cure portion of long term 33 AOCHU Dinar Cutraliasily 41 Total current liabilities 12 13 Total abilities 132 162 158 HO 20 23 59 0 207 279 373 300 330 447 528 684 45 Total equity and liabilities 48 Net Assets Equity Uabilities 0 19 Statement of Income 0 0 Statement of Financial Position F Type here to search O RE O Chip esc ? 13 4 15 1 7 @ 2 # 3 $ 4. % 5 E C 0 bottom, what does this signify? (Located on sheet 2 Row 47 in the excel file). AutoSave COT C Book 1 Excel File Home insert Page LINYout Formulas C Review View Help X. Cut Calibri 11 - A be, Wrap Tex US Copy B ED Format Painter A Merge Clipboard For Altonment SIGN IN TO OFFICE It looks like your stored credentials are out of date. Please Sign in Julian.Miletic ES2 B 171 125 O 212 O 150 196 237 63 51 A 28 Retained earnings 20 Other comprehensive incomel(los) 30 31 Total oquity attributable to owners 32 33 Total non-current liabilities 34 35 Current liabilities 36 Trade and other payables Short-term borrowings 38 Current portion of long-term debt 30 Accruals 30 Other current liability Total current Habilities 42 Total labilities 94 85 o 130 106 O 137 198 O 38 O 373 o 237 O 279 300 330 447 460 626 684 45 Total equity and liabilities 26 Net: Assets = Equity Liabilities O Explain your team's plan to finance the purchase of the additional inventory a warehouse? What is the total cost of the expansion? What is the total cost of financing for the expansion? (Hint: The company has been preapproved for a torm loan newall or harmoon to retninadmorninae from the nravioure unor O W ? Assignment 1 FIN 1013 Case study analysis. Read the Case below regarding ABC Company. Use the information contained in the Case to fill out the Excel Document found in Moodle. (ABCCompAssignment1.xlsx) Work in your Group to follow the steps below for Assignment 1. I Steps: 1) Construct Financial Statements using Excel. Your group's Job is to fill out the values for the years 2014 (current) and the 2015 (projected) in the Statement of Income and Statement of Financial Position (fill all the green cells). If no information is given about a particular value assume it is the same as last years. Pay close attention to the potential sources of financing, adjust the Statement of Income and Statement of Financial Position accordingly in excel. When you are finished with the Excel Document save it as Group#ABC CorpA1. All Values shown in Excel are in thousands of dollars. 2) Ratio Analysis (Read the CASE and complete step 1 FIRST) Calculate and analyze the following Ratios for the years 2014 and 2015. What do you notice about the change in these Ratios? Provide a brief explanation for the reason of the change, or whether the Ratios below should be any cause for concern. Current Ratio, Return on Assets, Quick Ratio, Net Working Captial 3) Discussion Questions (Read the CASE and complete step 1 FIRST) i) Did you decide to Rent or Buy the Warehouse? Why? (Hint: Compare the cost of financing) In the Statement of Financial Position explain why there are only zero's at the bottom, what does this signify? (Located on sheet 2 Row 47 in the excel file). FOR X SOOTH or Some Yoyote Wervers Cervera Ghe SONO os Explain your team's plan to finance the purchase of the additional inventory and warehouse? What is the total cost of the expansion? What is the total cost of financing for the expansion? (Hint: The company has been preapproved for a short- term loan, as well as has access to retained earnings from the previous year THE CASE: It is currently Q4 in 2014 and ABC corporation has placed your team in charge of preparing the company's Income Statement and Balance Sheet (Statement of Financial Position) for 2014, as well as the projected statements for 2015. You are told that sale revenue has increased 6% from the 2013 values. ABC Company also plans to expand their inventory to meet growing demand and they forecast sales revenue to increase by 35% in 2015 (from current values). 40,045.00$ of additional purchases wer made in 2014 (Paid for with Retained Earnings), with another 230,545.00 planned to be purchased in th future to expand inventory in 2015. Along with the increase in inventory, Freight expenses are expected to double. ABC has been preapproved a short-term loan in the amount of $100,000 today to be repaid ir full at the end of 2015 to finance this expansion, the rest will be paid for using the companies Retained Earnings. The total financing cost of the loan will be $9,500. Labour cost is expected to be the same as last year and are expected to increase by 15% in 2015. ABC is also planning to hire 2 new employees on salary, one for Distribution and one as Administrative staff in 2015, they will be paid $40,000/year each. Commissions are expected to increase by $30,000 in 2015. Plant Property and equipment are also the same as last year (2013), however, in the coming year (2015) the company is faced with a choice to buy or rent a warehouse to house the additional inventory. The building may be purchased outright for 350,000$ or leased on a one-year term for $90,000 spilt into 12 equal monthly payments. Assume a 15% down payment is required to buy the land, with payments made monthly amortized over 25 years, and the interest rate on the mortgage is fixed at 2.5%. Whether you decid to rent or buy the land is up to your team to decide, either way provide a rationale and adjust your financial statements for the projected year accordingly. **if you choose 'buy', add the principal payments of the loan for 12 months and include them in the "current portion of long term debt" cell in the Statement of Financial Position. Add the total financing cost of the loan (interest portion less the principal) for 12 months and add to existing financing costs (Cell A35) in the Statement of Income. ** If you choose lease, add the total monthly payments under "leasing" in the Statement of Income w