Hi Helpers for this assignment, I need full detailed explanation at least 1-2 pages and show high level of calculations to proof your writing for this finance assignment. Please use a excel file and everything. Please make sure you read everything and show the CALCULATIONS AND 1-2 PAGES OF WRITING THANK YOU
Attached are a set of Financial Statements (Balance Sheet and Income Statement) for a sample corporation. The assigment is to analyze and document what has transpired over this period both in terms of actions taken and any resulted that have occurred. While you should examine financial ratios over the period, do not just comment on individual ratio changes. Your submission needs to critically examine the interrelationship of outcomes. Are they the result of discrete actions undertaken by management and if so, is there an indication that the intended results were achieved? For example: Were major capital investments made during the period? If so, how were they finance and how did this impact the statements ? Has the financing of capital assets introduced additional risk to any investor group? Did the investment in new capital assets generate enhanced profits and to which group of investors? What decisions did the owners (equity investors) make regarding net income generated cach year? These are examples of what you should look at and not intended as the only factors in your analysis The assignment should include technical backup using the linked excel spreadsheet I have provided. While I am sure that many of you are familiar with how to manage a linked excel file, I have included a spreadsheet offering some tips on how to manage the data. However, the submission should be centred around a written assessment of what has transpired over the period examined. The written text should be 1 to 1 1/2 pages in length and all material will be submitted to the Courselink dropbox by Wednesday June 16 at 11:59 PM. You will need to have covered the material in Chapters 3 & 4 before you begin the assignment. This can be a personal or group assignment with up to three students in a group. In either case, be sure the submission reflects your or your group's analysis of the material provided and not that of another individual or group. Balance Sheet($000) Year Year 1 2 3 4 5 1 2 5 Current Liabilities 3.960 6,420 5,100 4.520 3,700 2.200 10.420 9.500 4.200 2,3301 16.030 5.100 5.300 3.100 13,500 5.900 6.120 4.400 16,420 6,600 7.100 5.125 18,825 2,910 4.000 2.000 8.910 3,010 4,100 2.000 9.110 4,300 2.000 10,260 5.250 4.900 2,000 12.150 2.000 13,520 Current Assets Cash and Equivants Accounts Receivable Inventory Total Current Assets Long Term Assets Property Plant & Equip. Less: Depreciation Net Prop/PI. & Equip Reserves Intang. Assets & Good Will Total Long Term Assets Total Assets 25.000 40.000 38.000 36,000 3.100 2,760 2.100 Accounts Payable Taxes Payable Notes Payable Total Current Liabilities Long Term Liabilities Long Term Debt and Fin. Leases Deferred Pension Liab. Other Long Term Liab Total Long Term Liabilities Total Liabilities Shareholder's Equity Share Capital Retained Earnings Total Equity Total Liabilities and Equity 120.450 135.000 141,500 146,000 151,000 27.000 28.000 29.000 31.000 33.000 93.450 107.000 112.500 115.000 118.000 2,352 4,465.72 7,041.72 9.920.12 13,708.52 4,451 3.900 3,7401 2.900 2.000 100.253 115,365.72 123,281.72 127,820.12 133,708.52 110,673 131,395.72 136,781.72 144.240.12 152,533 52|| 3.500 2.600 2.800 1,950 44.750 53,860 34.000 3.700 2.910 40.610 54.130 2.200 43,300 53.560 29.860 42.100 38,770 54.250 30.000 68,403.52 25.000 30.000 30.000 30,000 46,903 47,535.72 53,221.72 59,990.12 71,903 77,535.72 83,221.72 89,990.12 110,673 131,395.72136,781.72144.240.12 Income Statement ($ 000) 98,403.52 152,533.52 Year 1 3 4 5 2 40.000 19.000 44.000 40,000 19.000 8,700 1,9001 10,400 8,700 Sales Less: Cost of Goods Sold Admin. Costs Deprec. & Amortiz Operating Profit Less: Interest Expense Taxes (30%) Net Income 55.000 24,300 11,550 2.900 21,500 9.100 1.160 12.240 49.000 23.000 10.000 2.8401 13,160 1,551 10,749 16,250 2.000 2.520 5,880 3,200 2,264.7 5,284.3 3,040 2.760 6,440 2.880 3,084 7.196 2,720 4.059 9.471 Cash Dividend Stock Repurchase ($25 / share) Retained Earnings Total Income 3.528 C 2.3521 5.880 3,170.58 0 2.113.721 5,284,3 3,864 0 2.576 6,440 3,317.6 1.000 2,878.4 7.196 4,182.6 1,500 3,788.41 9.471 Shares Outstanding 1.000 1.000 200 1,000 200 Initial Shares: $25,000 / $25 Add'n from Invest: $5,000 / $25 Share Buyback - Yr 4: $1,000 / $25 Yr 5: $1,500 / $25) Total 1,000 200 (40) 1.000 200 (40) (60) 1.100 1.000 1.200 1.200 1.160 1 2 3 4 5 Return on Equity 8.18% 7.07% 8.01% 8.31% 10.05% Balance Sheet($000) Year Year 1 2 3 4 5 1 2 3 4 5 Current Assets 4,520 3.010 2.910 4.000 3.700 Cash and Equivants Accounts Receivable Inventory Total Current Assets 9.500 4,200 2.330 16.030 5.100 5.300 3.100 5.900 6,120 4.400 16,420 6,600 7.100 5.125 18,825 3,960 4.300 2.000 10,260 5.250 4.900 2.000 12.150 4,100 2.000 9.110 6.420 5,100 2.000 2.200 2.000/ 8.910 10.420 13,500 13.520 Current Liabilities Accounts Payable Taxes Payable Notes Payable Total Current Liabilities Long Term Liabilities Long Term Debt and Fin. Leases Deferred Pension Liab. Other Long Term Liab. Total Long Term Liabilities 36.000 3.500 Long Term Assets Property Plant & Equip Less: Depreciation Net Prop/Pl. & Equip Reserves Intang. Assets & Good Will Total Long Term Assets Total Assets 120.450 135.000 141,500 146,000 151.000 27.000 28.000 29,000 31,000 33.000 93.450 107.000 112.500 115.000 118.000 2.352 4,465.72 7,041.72 9.920.12 13.708 52 4,451 3.900 3,7401 2.900 2.000 100.253 115,365.72 123,281.72 127,820.12 133,708.52 110,673 131,395.72 136,781.72 144.240.12 152,533 52 25.000 2,760 2.100 29,860 38,7701 40.000 2.800 1,950 44,750 53,860 38.000 3,100 2.200 43.300 53.560 2.600 42.100 54.250 34.000 3,700 2.910 40.610 54.130 Total Liabilities Sharcholder's Equity Share Capital Retained Earnings Total Equity Total Liabilities and Equity Income Statement ($ 000) 25.000 30.000 30.000 30.000 30.000 46.903 47,535.72 53,221.72 59,990.12 68.403.52 71.903 77,535.72 83,221.72 89,990.12 98,403.52 110,673 131,395.72 136,781.72 144.240.12 152, 533 52 Year 1 2 3 4 5 Sales 40.000 49,000 55,000 Less: Cost of Goods Sold Linked Excel Instructions 19.000 8,700 1.900 10.400 40.000 19.000 8,700 1,551 10.749 44.000 21,500 9.100 1.160 12.240 23.000 10,000 2.840) 24.300 11,550 2.900 16.250 Freeze Pane 13,160 Admin. Costs Deprec. & Amortiz. Operating Profit Less: Interest Expense Taxes (30%) Net Income 2.880 2.000 2.520 5.8801 3,200 2.264.7 5,284.3 3.040 2,760 6,440 3.084 7.196 2,720 4,059 9.471 First off, in the spreadsheet you will be using (Spreadsheet 3 - Financials) Thave used the Freeze Pane feature (8" icon in the Customized Ribbon) to allow for the title in line 1 to be frozen as you move down the rows of the spreadsheet. It may be useful to extend this frozen area as a place to display any of the financial ratio information you would like to use. On this demonstration spreadsheet, I have added several extra lines in the frozen area for this purpose. First, I accessed the far left cell directly under frozen line (cell A2) and hit the Freeze Pane icon to unfreeze the current setting. Still in Row 2, I inserted several additional lines to the spreadsheet using the Row Insert, the 14" icon. Now in Cell A4, I refroze the rows to give me space to insert my financial ratios. 3,864 Cash Dividend Stock Repurchase ($25 / share) Retained Earnings Total Income 3,528 3,170.58 0 0 2,352 2,113.72 5,880 5,284.3 0 2,576 6.440 3,317.6 1.000 2,878.4 7.196 4,182.6 1,500 3,788.41 9.471 Set Up for Information Transfer Shares Outstanding 1.000 in Cell E2 to 12, I have inserted the years for the information being transferred and in Cell D3 have printed the ratio to be calculated. In Cell E3, I initially insert = then go to E32 (Year 1 Net Income) then insert/then go to E24 Year 1 Sales) and press Enter. The ROE is therefore 0.147. To express this as a percent, press the % key in the top ribbon and increase the number of decimals showing to 2. 1.000 1.000 1.000 200 200 200 Initial Shares: $25,000 / $25 Add'n from Invest: $5,000 / $25 : Share Buyback - Yr 4: $1,000 / $25 Yr 5: $1,500 / $25) / Total (40) ( 1.000 200 (40) (60) 1.100 1.000 1.200 1.200 1.160 There is no need to repeat this for years 2 to 4. Simply place your cursor on the dot in the bottom right comer of Cell E3 and holding down the mouse draw the cell across to Cell 15. This will fill in the data for the complete five years. Checking Your Calculations Finally, you should check all of your links to make sure the calculations are using the correct inputs. For example, to make sure that the ROE for years 1 is calculated correctly, go to Cell E3 and hit the last icon on the custom ribbon (Trace Precedence). This will show you the inputs used in the E3 cell. To remove Trace Precedence, simply hit the second icon in from the far right. Attached are a set of Financial Statements (Balance Sheet and Income Statement) for a sample corporation. The assigment is to analyze and document what has transpired over this period both in terms of actions taken and any resulted that have occurred. While you should examine financial ratios over the period, do not just comment on individual ratio changes. Your submission needs to critically examine the interrelationship of outcomes. Are they the result of discrete actions undertaken by management and if so, is there an indication that the intended results were achieved? For example: Were major capital investments made during the period? If so, how were they finance and how did this impact the statements ? Has the financing of capital assets introduced additional risk to any investor group? Did the investment in new capital assets generate enhanced profits and to which group of investors? What decisions did the owners (equity investors) make regarding net income generated cach year? These are examples of what you should look at and not intended as the only factors in your analysis The assignment should include technical backup using the linked excel spreadsheet I have provided. While I am sure that many of you are familiar with how to manage a linked excel file, I have included a spreadsheet offering some tips on how to manage the data. However, the submission should be centred around a written assessment of what has transpired over the period examined. The written text should be 1 to 1 1/2 pages in length and all material will be submitted to the Courselink dropbox by Wednesday June 16 at 11:59 PM. You will need to have covered the material in Chapters 3 & 4 before you begin the assignment. This can be a personal or group assignment with up to three students in a group. In either case, be sure the submission reflects your or your group's analysis of the material provided and not that of another individual or group. Balance Sheet($000) Year Year 1 2 3 4 5 1 2 5 Current Liabilities 3.960 6,420 5,100 4.520 3,700 2.200 10.420 9.500 4.200 2,3301 16.030 5.100 5.300 3.100 13,500 5.900 6.120 4.400 16,420 6,600 7.100 5.125 18,825 2,910 4.000 2.000 8.910 3,010 4,100 2.000 9.110 4,300 2.000 10,260 5.250 4.900 2,000 12.150 2.000 13,520 Current Assets Cash and Equivants Accounts Receivable Inventory Total Current Assets Long Term Assets Property Plant & Equip. Less: Depreciation Net Prop/PI. & Equip Reserves Intang. Assets & Good Will Total Long Term Assets Total Assets 25.000 40.000 38.000 36,000 3.100 2,760 2.100 Accounts Payable Taxes Payable Notes Payable Total Current Liabilities Long Term Liabilities Long Term Debt and Fin. Leases Deferred Pension Liab. Other Long Term Liab Total Long Term Liabilities Total Liabilities Shareholder's Equity Share Capital Retained Earnings Total Equity Total Liabilities and Equity 120.450 135.000 141,500 146,000 151,000 27.000 28.000 29.000 31.000 33.000 93.450 107.000 112.500 115.000 118.000 2,352 4,465.72 7,041.72 9.920.12 13,708.52 4,451 3.900 3,7401 2.900 2.000 100.253 115,365.72 123,281.72 127,820.12 133,708.52 110,673 131,395.72 136,781.72 144.240.12 152,533 52|| 3.500 2.600 2.800 1,950 44.750 53,860 34.000 3.700 2.910 40.610 54.130 2.200 43,300 53.560 29.860 42.100 38,770 54.250 30.000 68,403.52 25.000 30.000 30.000 30,000 46,903 47,535.72 53,221.72 59,990.12 71,903 77,535.72 83,221.72 89,990.12 110,673 131,395.72136,781.72144.240.12 Income Statement ($ 000) 98,403.52 152,533.52 Year 1 3 4 5 2 40.000 19.000 44.000 40,000 19.000 8,700 1,9001 10,400 8,700 Sales Less: Cost of Goods Sold Admin. Costs Deprec. & Amortiz Operating Profit Less: Interest Expense Taxes (30%) Net Income 55.000 24,300 11,550 2.900 21,500 9.100 1.160 12.240 49.000 23.000 10.000 2.8401 13,160 1,551 10,749 16,250 2.000 2.520 5,880 3,200 2,264.7 5,284.3 3,040 2.760 6,440 2.880 3,084 7.196 2,720 4.059 9.471 Cash Dividend Stock Repurchase ($25 / share) Retained Earnings Total Income 3.528 C 2.3521 5.880 3,170.58 0 2.113.721 5,284,3 3,864 0 2.576 6,440 3,317.6 1.000 2,878.4 7.196 4,182.6 1,500 3,788.41 9.471 Shares Outstanding 1.000 1.000 200 1,000 200 Initial Shares: $25,000 / $25 Add'n from Invest: $5,000 / $25 Share Buyback - Yr 4: $1,000 / $25 Yr 5: $1,500 / $25) Total 1,000 200 (40) 1.000 200 (40) (60) 1.100 1.000 1.200 1.200 1.160 1 2 3 4 5 Return on Equity 8.18% 7.07% 8.01% 8.31% 10.05% Balance Sheet($000) Year Year 1 2 3 4 5 1 2 3 4 5 Current Assets 4,520 3.010 2.910 4.000 3.700 Cash and Equivants Accounts Receivable Inventory Total Current Assets 9.500 4,200 2.330 16.030 5.100 5.300 3.100 5.900 6,120 4.400 16,420 6,600 7.100 5.125 18,825 3,960 4.300 2.000 10,260 5.250 4.900 2.000 12.150 4,100 2.000 9.110 6.420 5,100 2.000 2.200 2.000/ 8.910 10.420 13,500 13.520 Current Liabilities Accounts Payable Taxes Payable Notes Payable Total Current Liabilities Long Term Liabilities Long Term Debt and Fin. Leases Deferred Pension Liab. Other Long Term Liab. Total Long Term Liabilities 36.000 3.500 Long Term Assets Property Plant & Equip Less: Depreciation Net Prop/Pl. & Equip Reserves Intang. Assets & Good Will Total Long Term Assets Total Assets 120.450 135.000 141,500 146,000 151.000 27.000 28.000 29,000 31,000 33.000 93.450 107.000 112.500 115.000 118.000 2.352 4,465.72 7,041.72 9.920.12 13.708 52 4,451 3.900 3,7401 2.900 2.000 100.253 115,365.72 123,281.72 127,820.12 133,708.52 110,673 131,395.72 136,781.72 144.240.12 152,533 52 25.000 2,760 2.100 29,860 38,7701 40.000 2.800 1,950 44,750 53,860 38.000 3,100 2.200 43.300 53.560 2.600 42.100 54.250 34.000 3,700 2.910 40.610 54.130 Total Liabilities Sharcholder's Equity Share Capital Retained Earnings Total Equity Total Liabilities and Equity Income Statement ($ 000) 25.000 30.000 30.000 30.000 30.000 46.903 47,535.72 53,221.72 59,990.12 68.403.52 71.903 77,535.72 83,221.72 89,990.12 98,403.52 110,673 131,395.72 136,781.72 144.240.12 152, 533 52 Year 1 2 3 4 5 Sales 40.000 49,000 55,000 Less: Cost of Goods Sold Linked Excel Instructions 19.000 8,700 1.900 10.400 40.000 19.000 8,700 1,551 10.749 44.000 21,500 9.100 1.160 12.240 23.000 10,000 2.840) 24.300 11,550 2.900 16.250 Freeze Pane 13,160 Admin. Costs Deprec. & Amortiz. Operating Profit Less: Interest Expense Taxes (30%) Net Income 2.880 2.000 2.520 5.8801 3,200 2.264.7 5,284.3 3.040 2,760 6,440 3.084 7.196 2,720 4,059 9.471 First off, in the spreadsheet you will be using (Spreadsheet 3 - Financials) Thave used the Freeze Pane feature (8" icon in the Customized Ribbon) to allow for the title in line 1 to be frozen as you move down the rows of the spreadsheet. It may be useful to extend this frozen area as a place to display any of the financial ratio information you would like to use. On this demonstration spreadsheet, I have added several extra lines in the frozen area for this purpose. First, I accessed the far left cell directly under frozen line (cell A2) and hit the Freeze Pane icon to unfreeze the current setting. Still in Row 2, I inserted several additional lines to the spreadsheet using the Row Insert, the 14" icon. Now in Cell A4, I refroze the rows to give me space to insert my financial ratios. 3,864 Cash Dividend Stock Repurchase ($25 / share) Retained Earnings Total Income 3,528 3,170.58 0 0 2,352 2,113.72 5,880 5,284.3 0 2,576 6.440 3,317.6 1.000 2,878.4 7.196 4,182.6 1,500 3,788.41 9.471 Set Up for Information Transfer Shares Outstanding 1.000 in Cell E2 to 12, I have inserted the years for the information being transferred and in Cell D3 have printed the ratio to be calculated. In Cell E3, I initially insert = then go to E32 (Year 1 Net Income) then insert/then go to E24 Year 1 Sales) and press Enter. The ROE is therefore 0.147. To express this as a percent, press the % key in the top ribbon and increase the number of decimals showing to 2. 1.000 1.000 1.000 200 200 200 Initial Shares: $25,000 / $25 Add'n from Invest: $5,000 / $25 : Share Buyback - Yr 4: $1,000 / $25 Yr 5: $1,500 / $25) / Total (40) ( 1.000 200 (40) (60) 1.100 1.000 1.200 1.200 1.160 There is no need to repeat this for years 2 to 4. Simply place your cursor on the dot in the bottom right comer of Cell E3 and holding down the mouse draw the cell across to Cell 15. This will fill in the data for the complete five years. Checking Your Calculations Finally, you should check all of your links to make sure the calculations are using the correct inputs. For example, to make sure that the ROE for years 1 is calculated correctly, go to Cell E3 and hit the last icon on the custom ribbon (Trace Precedence). This will show you the inputs used in the E3 cell. To remove Trace Precedence, simply hit the second icon in from the far right
