Please work using excel spreadsheet, and providing functions for review. Please and thank you, can you provide this quickly, greatly appreciate it. **The problems have already been worked, just need how to enter into Excel spreadsheet**Please use one of the functions for each**

FV, PV, PMT, RATE, NPER, NPV, AVERAGE, STDEV, and IRR.

a. Create a personal income and expense statement for the Adams family. It should be similar to a corporate income statement.

Complete the income part of the personal income and expense statement:(Round to the nearest dollar.)

Complete the October cash budget below: (Round to the nearest dollar.)

Sam and Suzy Sizeman

Personal Budget

for the Period October-December

Complete the November Cash Budget Below: (Round to the nearest dollar.)

Sam and Suzy Sizeman

Personal Budget

for the Period October-December

Sam and Suzy Sizeman

Personal Budget

for the Period October-December

Cash flows It is typical for Jane to plan, monitor, and assess her financial position using cash flows over a given period, typically a month. Jane has a savings account and her bank loans money at 6% per year while it offers short-term investment rates of 5%. Jane's cash flows during August were as follows: a. Determine Jane's total cash inflows and cash outflows. b. Determine the net cash flow for the month of August. c. If there is a shortage, what are a few options open to Jane? d. If there is a surplus, what would be a prudent strategy for her to follow? \begin{tabular}{lcc} \hline Item & Cash inflow & Cash outflow \\ \hline Clothes & & $1,200 \\ Interest received & $430 & \\ Dining out & & $540 \\ Groceries & $4,300 & $830 \\ Salary & & \\ Auto payment & & $357 \\ Utilities & & $280 \\ Mortgage & & $1,190 \\ Gas & & $202 \\ \hline \end{tabular} a. Jane's total cash inflows are $ (Round to the nearest dollar.) Jane's total cash outflows are $ (Round to the nearest dollar.) b. Jane's net cash flow for the month of August is $ (Round to the nearest dollar. Remember that a positive number indicates a surplus and a negative number indicates a shortage.) Income statement preparation Adam and Arin Adams have collected their personal income and expense information and have asked you to put together an income and expense statement for the year. The following information is received from the Adams family a. Create a personal income and expense statement for the Adams family. It should be similar to a corporate income statement. b. Did the Adams family have a cash surplus or cash deficit? c. If the result is a surplus, how can the Adams family use that surplus? \begin{tabular}{lrlr} Adam's salary & $44,900 & Utilities & $3,150 \\ Arin's salary & 29,500 & Groceries & 2,230 \\ Interest received & 470 & Medical & 1,530 \\ Dividends received & 130 & Property taxes & 1,661 \\ Auto insurance & 580 & Income tax, Social Security & 13,400 \\ Home insurance & 740 & Clothing and accessories & 1,970 \\ Auto loan payment & 3,320 & Gas and auto repair & 2,150 \\ Mortgage payment & 14,300 & Entertainment & 1,950 \end{tabular} Personal Income and Expense Statement Period Ended December 31, 2021 \begin{tabular}{|c|c|c|c|} \hline \\ \hline \multicolumn{4}{|l|}{\begin{tabular}{l} Income \\ Adam's salary \end{tabular}} \\ \hline Arin's salary & & 29,500 & \\ \hline Interest received & & 470 & \\ \hline Dividends received & & 130 & \\ \hline Total Income & & & 75,000 \\ \hline \end{tabular} Complete the expenses part of the personal income and expense statement: (Round to the nearest dollar.) Personal Income and Expense Statement (Cont'd) \begin{tabular}{|c|c|c|} \hline Entertainment & 1,950 & \\ \hline Total Expenses & \$ & 46,981 \\ \hline Cash Surplus or (Deficit) & \$ & 28,019 \\ \hline \end{tabular} Preparation of cash budget. Sam and Suzy Sizeman need to prepare a cash budget for the last quarter of the year to make sure they can cover their expenditures during the period. Sam and Suzy have been preparing budgets for the past several years and have been able to identify the percentage of their income that they pay for most of for their cash outflows. These percentages are based on their take-home pay (e.g., monthly utilities normally run 4.8% of monthly take-home pay). The information here can be used to create their fourth-quarter budget for this year. a. Prepare a quarterly cash budget for Sam and Suzy covering the months October through December. b. Are there individual months that incur a deficit? c. What is the cumulative cash surplus or deficit by the end of December? \begin{tabular}{lr} Income & \\ Monthly take-home pay & $4,886 \\ Expenses & \\ Housing & 29.9% \\ Utilities & 4.8% \\ Food & 9.8% \\ Transportation & 7.4% \\ Medical/dental & 0.5% \\ Clothing for October \& November & 2.7% \\ Clothing for December & $435 \\ Property taxes (November only) & 11.3% \\ Appliances & 1.3% \\ Personal Care & 1.8% \\ Entertainment for October \& November & 5.9% \\ Entertainment for December & $1,495 \\ Savings & 7.5% \\ Other & 5.3% \\ Excess in cash & 4.8% \end{tabular} \begin{tabular}{lrlr} \hline & \multicolumn{2}{c}{ October } \\ \hline Income & & & \\ \hline Take-home pay & \multicolumn{1}{c}{$} & 4,886 \\ \hline Expenses & Percent & & \\ \hline Housing & 29.9% & $ & 1,461 \\ Utilities & 4.8% & $ & 235 \\ Food & 9.8% & $ & 479 \\ Transportation & 7.4% & $ & 362 \\ Medical/Dental & 0.5% & $ & 24 \\ Clothing & 2.7% & $ & 132 \end{tabular} \begin{tabular}{|c|c|c|} \hline Property taxes & 11.3%$ & 0 \\ \hline Appliances & 1.3%$ & 64 \\ \hline Personal care & 1.8%$ & 88 \\ \hline Entertainment & 5.9%$ & 288 \\ \hline Savings & 7.5%$ & 366 \\ \hline Other & 5.3%$ & 259 \\ \hline Excess cash & 4.8%$ & 235 \\ \hline Total expenses & $ & 3,993 \\ \hline Cash surplus or (deficit) & $ & 893 \\ \hline Cumulative cash surplus or (deficit) & $ & 893 \\ \hline \end{tabular} Complete the Uecember cash budget below: Kound to the nearest dollar. Time value Personal Finance Problem You have $2,100 to invest today at 12% interest compounded annually. a. Find how much you will have accumulated in the account at the end of (1) 5 years, (2) 10 years, and (3) 15 years. b. Use your findings in part a to calculate the amount of interest earned in (1) the first 5 years (years 1 to 5 ), (2) the second 5 years (years 6 to 10), and (3) the third 5 years (years 11 t 15). c. Compare and contrast your findings in part b. Explain why the amount of interest earned increases in each succeeding 5-year period. a. (1) At the end of 5 years, the amount you will have accumulated is $3700.92. (Round to the nearest cent.) (2) At the end of 10 years, the amount you will have accumulated is $ (Round to the nearest cent.) (3) At the end of 15 years, the amount you will have accumulated is $ (Round to the nearest cent.) b. (1) From your findings in part a, the amount of interest earned in the first 5 years (years 1 to 5) is $ (Round to the nearest cent.) (2) From your findings in part a, the amount of interest earned in the second 5 years (years 6 to 10) is $ (Round to the nearest cent.) (3) From your findings in part a, the amount of interest earned in the third 5 years (years 11 to 15 ) is $4972.22. (Round to the nearest cent.) Time value Personal Finance Problem You can deposit $10,000 into an account paying 14% annual interest either today or exactly 5 years from today. How much better off will you be 40 years from now if you decide to make the initial deposit today rather than 5 years from today? The future value at the end of 40 years if you deposit $10,000 at 14% today is $ (Round to the nearest dollar.) The future value at the end of 40 years if you deposit $10,000 at 14%5 years from today is $ (Round to the nearest dollar.) If you invest the $10,000 now instead of waiting for 5 years to make the investment, you would be better off by $907833. (Round to the nearest dollar.) Time value Personal Finance Problem Jim Nance has been offered an investment that will pay him $480 three years from today. a. If his opportunity cost is 6% compounded annually, what value should he place on this opportunity today? b. What is the most he should pay to purchase this payment today? c. If Jim can purchase this investment for less than the amount calculated in part (a), what does that imply about the rate of return that he will earn on the investment? a. The value Jim should place on this opportunity today is $ b. The most he should pay to purchase this payment today is $ nearest cent.) (Round to the nearest cent.) (Round to the c. If Jim pays less than $403.02 computed in part a for the investment, his rate of return will be the opportunity cost of 6%. (Select from the drop-down menu.) Cash flow investment decision Personal Finance Problem Tom Alexander has an opportunity to purchase any of the investments shown in the following table, The purchase price, the amount of the single cash inflow, and its year of receipt are given for each investment. Which purchase recommendations would you make, assuming that Tom can earn 10% on his investments? Click the icon to see the Worked Solution (Formula Solution). Click the icon to see the Worked Solution (Financial Calculator Solution). Click the icon to see the Worked Solution (Spreadsheet Solution). The present value of Investment A is \$ (Round to the nearest cent.) The present value of Investment B is $166.33. (Round to the nearest cent.) The present value of Investment C is \$ 1275 . (Round to the nearest cent.) The present value of Investment D is $277.27. (Round to the nearest cent.) \begin{tabular}{crrr} \hline Investment & Price & Single cash inflow & Year of receipt \\ \hline A & $6,100 & $10,807 & 5 \\ B & $183 & $1,119 & 20 \\ C & $1,159 & $3,307 & 10 \\ D & $305 & $12,549 & 40 \\ \hline \end{tabular} Value of a retirement annuity Personal Finance Problem An insurance agent is trying to sell you an annuity, that will provide you with $9,400 at the end of each year for the next 15 years. If you don't purchase this annuity, you can invest your money and earn a return of 8%. What is the present value of the annuity? Ignoring taxes, the present value of the annuity is $80459.10. (Round to the nearest cent.) Monthly loan payments Personal Finance Problem Tim Smith is shopping for a used luxury car. He has found one priced at $32,000. The dealer has told Tim that if he can come up with a down payment of $7,500, the dealer will finance the balance of the price at a 5% annual rate over 3 years (36 months). a. Assuming that Tim accepts the dealer's offer, what will his monthly (end-of-month) payment amount be? b. Use a financial calculator or spreadsheet to help you figure out what Tim's monthly payment would be if the dealer were willing to finance the balance of the car price at an annual rate of 3.3% ? a. Tim's monthly (end-of-month) payment amount is $ (Round to the nearest cent.) b. Tim's monthly payment, if the dealer were willing to finance the balance of the car price at an annual rate of 3.3%, would be $715.73. (Round to the nearest cent.) Rate of return Personal Finance Problem Rishi Singh has $1,000 to invest. His investment counselor suggests that Rishi should buy an investment that pays no interest but will be worth $1,300 after 3 years. What average annual rate of return will Rishi earn with this investment? o. Rishi is considering another investment, of equal risk, that earns an annual return of 7.14%. Which investment should he make, and why? . The annual rate of return Rishi will earn with this investment, r, is %. (Round to two decimal places.) Time to repay installment loan Personal Finance Problem Mia Salto wishes to determine how ong it will take to repay a $12,000 loan given that the lender requires her to make annual end-of-year installment payments of $2,882. a. If the interest rate on the loan is 15%, how long will it take for her to repay the loan fully? b. How long will it take if the interest rate is 12% ? c. How long will it take if she has to pay 19% annual interest? d. Reviewing your answers in parts a,b, and c, describe the general relationship between the interest rate and the amount of time it will take Mia to repay the loan fully. a. If Mia can borrow at an annual interest rate of 15%, the amount of time it will take for her to repay the loan fully is years. (Round to two decimal places.) b. If Mia can borrow at an annual interest rate of 12%, the amount of time it will take for her to repay the loan fully is years. (Round to two decimal places.) c. If Mia can borrow at an annual interest rate of 19%, the amount of time it will take for her to repay the loan fully is 9.00 years. (Round to two decimal places.) Asset valuation and risk Personal Finance Problem Laura Drake wishes to estimate the value of an asset expected to provide cash inflows of $2,900 for each of the next 4 years and $13,353i 5 years. Her research indicates that she must earn 4% on low-risk assets, 9% on average-risk assets, and 13% on high-risk assets. a. Determine what is the most Laura should pay for the asset if it is classified as (1) low-risk, (2) average-risk, and (3) high-risk. b. Suppose Laura is unable to assess the risk of the asset and wants to be certain she's making good deal. On the basis of your findings in part a, what is the most she should pay? Why? c. All else being the same, what effect does increasing risk have on the value of an asset? Explai in light of your findings in part a. a. (1) The most Laura should pay for the asset if it is classified as low-risk is $ (Round to the nearest cent.) (2) The most Laura should pay for the asset if it is classified as average-risk is $ (Round to the nearest cent.) (3) The most Laura should pay for the asset if it is classified as high-risk is $ (Round to the nearest cent.) b. Suppose Laura is unable to assess the risk of the asset and wants to be certain she's making a good deal. On the basis of your findings in part a, the most she should pay is $15873.44. (Round to the nearest cent.) Bond value and time-Changing required returns Personal Finance Problem Lynn Parsons is considering investing in either of two outstanding bonds. The bonds both have $1,000 par values and 13% coupon interest rates and pay annual interest. Bond A has exactly 10 years to maturity, and bond B has 20 years to maturity. a. Calculate the present value of bond A if the required rate of return is: (1) 10%, (2) 13%, and (3) 16%. b. Calculate the present value of bond B if the required rate of return is: (1) 10%, (2) 13%, and (3) 16%. c. From your findings in parts a and b, discuss the relationship between time to maturity and changing required returns. d. If Lynn wanted to minimize interest rate risk, which bond should she purchase? Why? a. (1) The value of bond A, if the required return is 10%, is S (Round to the nearest cent.) (2) The value of bond A, if the required return is 13%, is $ (Round to the nearest cent.) (3) The value of bond A, if the required return is 16% is $ (Round to the nearest cent.) b. (1) The value of bond B, if the required return is 10%, is $ (Round to the nearest cent.) (2) The value of bond B, if the required return is 13%, is $ (Round to the nearest cent.) (3) The value of bond B, if the required return is 16%, is $ (Round to the nearest cent.) Portfolio return and standard deviation Jamie Wong is thinking of building an investment portfolio containing two exchange traded funds (ETFs). Jamie plans to invest $4,000 in Vanguard S\&P 500 ETF (VOO) and \$6,000 in Invesco QQQ Trust (QQQ). Jamie has decided to analyze some historical returns to get a sense for her portfolio's possible future risk and return. Six years of historical annual returns for each ETF are shown in the following table: a. Calculate the portfolio return, rp, for each of the 6 years assuming that 40% is invested in VOO and 60% is invested in QQQ. b. Calculate the average annual return for each ETF and the portfolio over the six-year period. c. Calculate the standard deviation of annual returns for each ETF and the portfolio. How does the portfolio standard deviation compare to the standard deviations of the individual ETFs? d. Calculate the correlation coefficient for the two ETFs. How would you characterize the correlation of returns of the two ETFs? e. Discuss any likely benefits of diversification achieved by Jamie through creation of the portfolio. a. I he porttolio return for year 2014 is 16.50%. (Round to two decimal places.) The portfolio return for year 2015 is 5.96%. (Round to two decimal places.) The portfolio return for year 2016 is 9.07%. (Round to two decimal places.) The portfolio return for year 2017 is 29.05%. (Round to two decimal places.) The portfolio return for year 2018 is 2.24%. (Round to two decimal places.) The portfolio return for year 2019 is %. (Round to two decimal places.) b. The average return of VOO over the 6-year period is %. (Round to two decimal places.) The average return of QQQ over the 6-year period is %. (Round to two decimal places.) The average return of the portfolio over the 6 -year period is %. (Round to two decimal places.) c. The standard deviation of VOO returns over the 6-year period is %. (Round to two decimal places.) Beta coefficients and the capital asset pricing model Personal Finance Problem Katherine Wilson is wondering how much risk she must undertake to generate an acceptable return on her porfolio. The risk-free return currently is 3%. The return on the overall stock market is 15%. Use the CAPM to calculate how high the beta coefficient of Katherine's portfolio would have to be to achieve a portfolio return of 18%. The beta of the portfolio is (Round to four decimal places.) Problems with the IRR method Acme Oscillators is considering an investment project that has the following rather unusual cash flow pattern: a. Calculate the project's NPV at each of the following discount rates: 0%,5%,10%,20%,30%, 40%,50%. b. What do the calculations tell you about this project's IRR? The IRR rule tells managers to invest if a project's IRR is greater than the cost of capital. If Acme Oscillators' cost of capital is 8%, should the company accept or reject this investment? c. Notice that this project's greatest NPVs come at very high discount rates. Can you provide an intuitive explanation for that pattern? d. If Acme Oscillators' cost of capital is 8%, should the company accept or reject this investment based on MIRR? a. Calculate the NPV at the following discount rates for this investment: 0%,5%,10%,20%,30%, 40%,50%. The NPV at 0% is $. (Round to the nearest dollar.) The NPV at 5% is $. (Round to the nearest dollar.) \begin{tabular}{cr} Year & \multicolumn{1}{c}{CFt} \\ \hline 0 & $100,000,000 \\ 1 & 460,300,000 \\ 2 & 790,500,000 \\ 3 & 601,600,000 \\ 4 & 171,400,000 \end{tabular}