70.2K

Verified Solution

Link Copied!

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 each year?

ANSWER THESE QUESTIONS ABOVE BASED ON THE INFORMATION I PROVIDED 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 each 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. 1 2 3 4. 5 Return on Equity 14.70% 13.21% 14.64% 14.69% 17.22% Balance Sheet($000) Year 3 Year 3 1 2 4 5 1 2 4. 5 4.520 3,700 2.200 10,420 9.500 4,200 2,330 16.030 5.100 5,300 3.100 13,500 5.900 6,120 4.4001 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 3.960 4,300 2.000 10,260 5.250 4.900 2,000 12.150 6,420 5.100 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 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 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 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 Total Liabilities Shareholder's Equity Share Capital Retained Earnings Total Equity Total Liabilities and Equity 25.000 2,760 2.100 29.860 38,770 40.000 2.800 1,950 44.750 53,860 38.000 3.100 2.200 43,300 53.560 36.000 3,500 2.600 42.100 54.250 34,000 3,700 2.910 40.610 54.130 3.740 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 Income Statement ($ 000) Year 1 2 40.000 40.000 19.000 19.000 8,700 8,700 1.900 1,551 10,400 10,749 Sales Less: Cost of Goods Sold Admin. Costs Deprec. & Amortiz. Operating Profit Less: Interest Expense Taxes (30%) Net Income 3 44.000 21,500 9.100 1.160 12.240 4. 49,000 23.000 10,000 2,8401 13,160 5 55.000 24,300 11,550 2.900 16,250 Linked Excel Instructions 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 3,864 0 Cash Dividend Stock Repurchase ($25 / share) Retained Earnings Total Income 3.528 0 2,352 5,880 3,170.58 0 2,113.72 5,284.3 3.317.6 1.000 2,878.4 7.196 4,182.6 1,500 3,788.4 9.471 2.576 6,440 Shares Outstanding Freeze Pane First off, in the spreadsheet you will be using (Spreadsheet 3 - Financials) Thave used the Freeze Pane feature (8hicon 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, Thave 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. Set Up for Information Transfer 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. 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. 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 Balance Sheet ($000) Year 3 Year 3 1 2 4 4 5 1 2 4 5 4,520 3,700 2.200 10,420 9.500 4.200 2,330 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 3.960 4.300 2.000 10,260 5,250 4.900 2.000 12.150 6.420 5.100 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 38.000 3.100 118.000 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 2,352 4.465.72 7,041.72 9,920.12 13,708.52 4.4511 3,900 3,740 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 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 Total Liabilities Shareholder's Equity Share Capital Retained Earnings Total Equity Total Liabilities and Equity 25.000 2.760 2.1001 29,860 38,7701 40.000 2.800 1.950 44,750 53,860 2.200 43,300 53,560 36,000 3.500 2.600 42,100 54,250 34.000 3,700 2910 40.610 54,130 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 Sales Less: Cost of Goods Sold Admin. Costs Deprec. & Amortiz. Operating Profit Less: Interest Expense Taxes (30%) Net Income Income Statement ($ 000) Year 1 2 40.000 40.000 19.000 19.000 8,700 8,700 1.900 1,551 10.400 10,749 3 44,000 21.500 9.100 1,160 12.240 4 49,000 23,000 10.000 2.8401 13,160 5 55,000 24,300 11,550 2.900 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 0 2,352 5,880 3,170.58 0 2,113.72 5,284.3 3,864 0 2.5761 6.440 3,317.6 1,000 2,878.41 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

Answer & Explanation Solved by verified expert

See Answer