Excel Online Structured Activity: Statement of cash flows You have just been hired as a financial analyst for Barrington Industries. Unfortunately, company headquarters (where all of the firm's records are kept) has been destroyed by fire. So, your first job will be to recreate the firm's cash flow statement for the year just ended. The firm had $100,000 in the bank at the end of the prior year, and its working capital accounts except cash remained constant during the year. It earned $5 million in net income during the year but paid $700,000 in dividends to common shareholders. Throughout the year, the firm purchased $5.4 million of machinery that was needed for a new project. You have just spoken to the firm's accountants and learned that annual depreciation expense for the year is $440,000; however, the purchase price for the machinery represents additions to property, plant, and equipment before depreciation. Finally, you have determined that the only financing done by the firm was to issue long-term debt of $1 million at a 5% interest rate. The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the question below. Open spreadsheet What was the firm's end-of-year cash balance? Recreate the firm's cash flow statement to arrive at your answer. Write out your answer completely. For example, 5 million should be entered as 5,000,000. Round your answer to the nearest dollar, if necessary. $ 2. Future value functions How Excel can help you find the future value of your investments? One of the most commonly applied concepts in finance is the determination of the value of an investment in future. Future value calculations can help us understand the financial implications of our decisions-such as the value of my retirement investments in 40 years if it earns a certain rate of return. Or, how much will I have when my friend repays the loan he made from me and promised to pay x% interest? Excel can be a handy tool to derive the future value of investments, loans, etc. Consider this example: You have just started college and you are having a discussion with your parents about how your college will be funded. Your parents explain that they have been investing in a college savings plan for the past 15 years that has earned them an average of 5.0% per year. They opened the account 15 years ago and since then have been investing 3000 every year at the end of the year. They ask you to do two things based on the information they provided: FIRST TASK: Calculate the amount of money that the college savings account should have accumulated by the end of 15 years. SECOND TASK: Calculate the annual savings that they should have made if they wanted to save at least $100,000 for your college education. You can use Excel to compute these values quickly and accurately. Enter the values into their respective cells to and calculate the correct answer. You can use Excel to compute these values quickly and accurately. Enter the values into their respective cells to and calculate the correct answer. A B E 1 Enter the given values here: FIRST TASK SECOND TASK 2 Interest rate per period % = B2 3 Number of years of investment = B3 4 Initial deposit = B4 5 Annual savings deposit ???? 6 Type = B6 Future Value ???? $100,000 Calculate future value FV =FV(rate, nper, pmt, [pv], [type]) 10 11 Calculate annual payment PMT =PMT(rate, nper, pv, [fv], [type]) 12 which would result in the future of If the payments were made at the beginning of each year, you would change the value of type in cell B6 to value of Your parents also tell you that they had considered investing a lumpsum amount of $25,000 for five years. Their financial advisors had given some predictive interest rates that might have likely applied to the investment during the investment period. The interest rate data is given below. What would have been the future value of the investment amount based on the data your parents had at that point? In this case you would use the function in Excel to derive the future value. 789 $ $ Excel Online Structured Activity: Statement of cash flows You have just been hired as a financial analyst for Barrington Industries. Unfortunately, company headquarters (where all of the firm's records are kept) has been destroyed by fire. So, your first job will be to recreate the firm's cash flow statement for the year just ended. The firm had $100,000 in the bank at the end of the prior year, and its working capital accounts except cash remained constant during the year. It earned $5 million in net income during the year but paid $700,000 in dividends to common shareholders. Throughout the year, the firm purchased $5.4 million of machinery that was needed for a new project. You have just spoken to the firm's accountants and learned that annual depreciation expense for the year is $440,000; however, the purchase price for the machinery represents additions to property, plant, and equipment before depreciation. Finally, you have determined that the only financing done by the firm was to issue long-term debt of $1 million at a 5% interest rate. The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the question below. Open spreadsheet What was the firm's end-of-year cash balance? Recreate the firm's cash flow statement to arrive at your answer. Write out your answer completely. For example, 5 million should be entered as 5,000,000. Round your answer to the nearest dollar, if necessary. $ 2. Future value functions How Excel can help you find the future value of your investments? One of the most commonly applied concepts in finance is the determination of the value of an investment in future. Future value calculations can help us understand the financial implications of our decisions-such as the value of my retirement investments in 40 years if it earns a certain rate of return. Or, how much will I have when my friend repays the loan he made from me and promised to pay x% interest? Excel can be a handy tool to derive the future value of investments, loans, etc. Consider this example: You have just started college and you are having a discussion with your parents about how your college will be funded. Your parents explain that they have been investing in a college savings plan for the past 15 years that has earned them an average of 5.0% per year. They opened the account 15 years ago and since then have been investing 3000 every year at the end of the year. They ask you to do two things based on the information they provided: FIRST TASK: Calculate the amount of money that the college savings account should have accumulated by the end of 15 years. SECOND TASK: Calculate the annual savings that they should have made if they wanted to save at least $100,000 for your college education. You can use Excel to compute these values quickly and accurately. Enter the values into their respective cells to and calculate the correct answer. You can use Excel to compute these values quickly and accurately. Enter the values into their respective cells to and calculate the correct answer. A B E 1 Enter the given values here: FIRST TASK SECOND TASK 2 Interest rate per period % = B2 3 Number of years of investment = B3 4 Initial deposit = B4 5 Annual savings deposit ???? 6 Type = B6 Future Value ???? $100,000 Calculate future value FV =FV(rate, nper, pmt, [pv], [type]) 10 11 Calculate annual payment PMT =PMT(rate, nper, pv, [fv], [type]) 12 which would result in the future of If the payments were made at the beginning of each year, you would change the value of type in cell B6 to value of Your parents also tell you that they had considered investing a lumpsum amount of $25,000 for five years. Their financial advisors had given some predictive interest rates that might have likely applied to the investment during the investment period. The interest rate data is given below. What would have been the future value of the investment amount based on the data your parents had at that point? In this case you would use the function in Excel to derive the future value. 789 $ $