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.00S of additional purchases were made in 2014 (Paid for with Retained Earnings), with another 230,545.00 planned to be purchased in the 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 in 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,0005 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.3%. 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 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) ii) 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). Back - Eco AutoSave Insert Page Layout Formulas Dat Review View Help X CU Calib te wrap Text CD Copy Format Painter Card SIGN IN TO OFFICE look like your stored credente out of Design Inn Motel.conn FO 171 212 O O Remindering Other comprehensive incomo( ISO) Total squity attributable to owner Torroneurrenetiabilities 150 196 237 90 130 100 Current liabilities Trade and other payables short-terre borrowings Current portion of long-term cob Als Orher current tality Total current liabilities 43 237 27 Totaltabilities 100 Total equity and liabilios iii) 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 Statement of Income Statement of Financial Position Statement of Income 2011 2012 2013 2014(Current) 2015(Projected) Revenue 1,083 1,058 1,564 Cost of sales Purchases Freight in Labour Depreciation/amortization Other charges Total cost of sales 369 30 380 3 5 787 355 29 366 4 0 630 44 529 4 0 754 1,207 Gross profit 296 304 357 Other income 0 0 0 Distribution costs Salaries Commissions Travelling Advertising Depreciation/amortization Other charges Total distribution costs 34 33 12 7 2 10 98 37 32 11 9 2 0 41 47 16 10 3 0 91 117 94 Administrative expenses Salaries Leasing Depreciation/amortization Other charges Total administrative expenses 20 13 20 102 20 9 11 142 107 26 24 16 147 173 Financing costs 31 7 14 Total expenses less other income 248 240 304 Profit before taxes 48 64 53 Income taxe expense 10 18 12 Profit for the year 38 46 41 Statement of Income Statement of Financial Position Statement of Financial Position 2012 2013 |2014(Current) 2011 2015(Projected) 156 238 Assets Non-current assets Property, plant and equipment Goodwill Investments Due to shareholders Sub-total Accumulated depreciation amortization 14 0 90 14 0 24 128 47 14 0 50 220 62 0 252 93 Total non-current assets 81 158 159 114 241 Current assets Inventories Trade receivables Prepaid expenses Cash and cash equivalents Other current asset Other current asset 2 109 247 3 10 210 298 4 13 4 9 0 0 0 0 0 0 Total current assets 369 368 $25 Total assets 450 526 684 Equity and liabilities Share capital Retained earnings Other comprehensive income (oss) 25 125 0 25 171 25 212 0 0 Total equity attributable to owners 150 196 237 Total non-current liabilities 51 74 94 130 106 85 Current liabilities Trade and other payables Short-term borrowings Current portion of long-term debt Accruals Other current liability Total current liabilities 137 198 0 38 0 0 58 0 237 43 0 279 0 373 Total labilities 300 330 447 Total equity and liabilities 450 $26 684 Net: Assets Equity + Liabilities 0 0 0 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.00S of additional purchases were made in 2014 (Paid for with Retained Earnings), with another 230,545.00 planned to be purchased in the 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 in 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,0005 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.3%. 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 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) ii) 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). Back - Eco AutoSave Insert Page Layout Formulas Dat Review View Help X CU Calib te wrap Text CD Copy Format Painter Card SIGN IN TO OFFICE look like your stored credente out of Design Inn Motel.conn FO 171 212 O O Remindering Other comprehensive incomo( ISO) Total squity attributable to owner Torroneurrenetiabilities 150 196 237 90 130 100 Current liabilities Trade and other payables short-terre borrowings Current portion of long-term cob Als Orher current tality Total current liabilities 43 237 27 Totaltabilities 100 Total equity and liabilios iii) 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 Statement of Income Statement of Financial Position Statement of Income 2011 2012 2013 2014(Current) 2015(Projected) Revenue 1,083 1,058 1,564 Cost of sales Purchases Freight in Labour Depreciation/amortization Other charges Total cost of sales 369 30 380 3 5 787 355 29 366 4 0 630 44 529 4 0 754 1,207 Gross profit 296 304 357 Other income 0 0 0 Distribution costs Salaries Commissions Travelling Advertising Depreciation/amortization Other charges Total distribution costs 34 33 12 7 2 10 98 37 32 11 9 2 0 41 47 16 10 3 0 91 117 94 Administrative expenses Salaries Leasing Depreciation/amortization Other charges Total administrative expenses 20 13 20 102 20 9 11 142 107 26 24 16 147 173 Financing costs 31 7 14 Total expenses less other income 248 240 304 Profit before taxes 48 64 53 Income taxe expense 10 18 12 Profit for the year 38 46 41 Statement of Income Statement of Financial Position Statement of Financial Position 2012 2013 |2014(Current) 2011 2015(Projected) 156 238 Assets Non-current assets Property, plant and equipment Goodwill Investments Due to shareholders Sub-total Accumulated depreciation amortization 14 0 90 14 0 24 128 47 14 0 50 220 62 0 252 93 Total non-current assets 81 158 159 114 241 Current assets Inventories Trade receivables Prepaid expenses Cash and cash equivalents Other current asset Other current asset 2 109 247 3 10 210 298 4 13 4 9 0 0 0 0 0 0 Total current assets 369 368 $25 Total assets 450 526 684 Equity and liabilities Share capital Retained earnings Other comprehensive income (oss) 25 125 0 25 171 25 212 0 0 Total equity attributable to owners 150 196 237 Total non-current liabilities 51 74 94 130 106 85 Current liabilities Trade and other payables Short-term borrowings Current portion of long-term debt Accruals Other current liability Total current liabilities 137 198 0 38 0 0 58 0 237 43 0 279 0 373 Total labilities 300 330 447 Total equity and liabilities 450 $26 684 Net: Assets Equity + Liabilities 0 0 0