Answer with Excel spreadsheet . You must also turn in a word or pdf document with responses to only questions f and g

Problem 1: Lifetime Earnings with On-the-Job Human Capital Accumulation Monica, Nathaniel, and Oscar all start school at age 6. They are equally able, but face different costs of schooling for each year they stay in school. They leave school at different times, meaning that they take different levels of human capital with them when they enter the labor market. They have different starting salaries at the beginning of their career, as well as different growth rates of earnings over their career. They all retire at age 65. The cost of schooling is $400 per year for Monica, $600 per year for Nathaniel, and $800 per year for Oscar. Monica leaves school at age 22 and starts work at age 23. Her starting salary is $45,000, and her earnings grow at 2% per year. Nathaniel leaves school at age 18 and starts work at age 19. His starting salary is $30,000, and his earnings grow at 1% per year. Oscar leaves school at a ye 26 and starts work at age 27. His starting salary is $60,000, and his earnings grow at 5% per year. Download the Excel spreadsheet from D2L under Problem Set 5. There should be pre-filled options for you in blue. Do not modify these values until part g. Follow the instructions below to calculate the net income for Monica, Nathaniel, and Oscar. Round your answers to the nearest hundredth. a) We will first fill in the cost of schooling for each worker. i) In cell B2 (Monica at age 6), type in =-$F$2 This will input the value in cell F2 into cell B2. Click on cell B2. On the bottom right corner, there should be a small green box. Click on this box and drag it all the way to cell B18 (Monica at age 22). This should copy the value in F2 to cells B2-B18. The value represents the amount Monica pays for schooling from age 6 until age 22. ii) In cell C2 (Nathaniel at age 6), type in --$G$2 This will input the value in cell G2 into cell C2. Click on cell C2. On the bottom right corner, there should be a small green box. Click on this box, hold down, and drag it all the way to cell C14 (Nathaniel at age 18). This should copy the value in G2 to cells C2-C14. The value represents the amount Nathaniel pays for schooling from age 6 until age 18. iii) In cell D2 (Oscar at age 6), type in =-$H$2 This will input the value in cell H2 into cell D2. Click on cell D2. On the bottom right corner, there should be a small green box. Click on this box, hold down, and drag it all the way to cell D22 (Oscar at age 26). This should copy the value in H2 to cells D2-D22. The value represents the amount Oscar pays for schooling from age 6 until age 26. b) Monica's salary each year i) In cell B19 (Monica at age 23), type in the following =$F$5*(1+$F$8) (A19-$A$19) This is Monica's starting salary ($F$5) multiplied by the growth rate of her salary (1+SF$8), which is in turn raised to the number of years since the start of her career (A19-$A$19). Be very careful about where you put the $ symbol - this is important for what we will do next, and for programming in Excel in general! (The $ symbol fixes the location of the reference cell.) ii) Click on cell B19. Drag the lower right corner from B19 to 361 (Monica at age 65). The cells B19-B61 should populate with Monica's salary for each year. Check that the earnings are growing each year from age 22 to 65 for Monica c) Nathaniel's salary each year i) In cell C15, type in the following =$G$5*(1+$G$8) * (A15-$A$15) This is Nathaniel's starting salary ($G$5) multiplied by the growth rate each year (1+$G$8), which is in turn raised to the number of years since the start of his career (A15-$A$15). Again, be careful about where you put the $ symbol. ii) Click on cell C15. Drag the lower right corner from C15 to C61 (Nathaniel at age 65). The cells C15-C61 should populate with Nathaniel's salary for each year. Check that the earnings are growing each year from age 19 to 65 for Nathaniel. d) Oscar's salary each year i) In cell D23, type =$H$5*(1+$H$8) (A23-$A$23) This is Oscar's starting salary ($H$5) multiplied by the growth rate each year (1+$H$8), which is in turn raised to the number of years since the start of his career (A23-$A$23). 2 ii) Click on cell D23. Drag the lower right corner from D23 to D61 (Oscar at age 65). The cells D23-D61 should populate with Oscar's salary for each year. Check that the earnings are growing each year from age 27 to 65 for Oscar. e) Net Lifetime Wealth i) In cell B63 (lifetime earnings for Monica), type the following SUM(B2:B61) This calculates the total amount of earnings and costs for Monica from age 6 to 65. This is her net lifetime wealth. ii) Click on cell B63. Click on the small green box in the lower right corner and drag it from cell B63 to cell D63. This should compute the net lifetime wealth for Nathaniel (C63) and Oscar (D63). f) Use the spreadsheet to answer the following questions. Round your answers to the nearest cent. (-5 more points if cannot follow work on spreadsheet) i) How much does Monica earn at age 34? ii) How much does Oscar earn at age 50? iii) How much does Nathaniel earn at age 58? iv) What is Monica's net lifetime wealth? What is Nathaniel's net lifetime wealth? What is Oscar's net lifetime wealth? g) For the questions below, we will change the parameters of the problem. In your answers, round your answers to the nearest cent. i) Now, suppose that everyone's growth rate of earnings double. Replace cell F8 with 0.04, cell G8 with 0.02, and cell H8 with 0.10. What is the net lifetime wealth for Monica, Nathaniel, and Oscar now? ii) In addition to the changes in i), now suppose that Oscar's starting salary is $80,000. Replace H5 with 80,000. What is Oscar's net lifetime wealth now? iii) In addition to the changes in i) and ii), now suppose that Nathaniel's cost of education is $1200 per year. Replace G2 with 1200. What is Nathaniel's net lifetime wealth now? 3 Problem 1: Lifetime Earnings with On-the-Job Human Capital Accumulation Monica, Nathaniel, and Oscar all start school at age 6. They are equally able, but face different costs of schooling for each year they stay in school. They leave school at different times, meaning that they take different levels of human capital with them when they enter the labor market. They have different starting salaries at the beginning of their career, as well as different growth rates of earnings over their career. They all retire at age 65. The cost of schooling is $400 per year for Monica, $600 per year for Nathaniel, and $800 per year for Oscar. Monica leaves school at age 22 and starts work at age 23. Her starting salary is $45,000, and her earnings grow at 2% per year. Nathaniel leaves school at age 18 and starts work at age 19. His starting salary is $30,000, and his earnings grow at 1% per year. Oscar leaves school at a ye 26 and starts work at age 27. His starting salary is $60,000, and his earnings grow at 5% per year. Download the Excel spreadsheet from D2L under Problem Set 5. There should be pre-filled options for you in blue. Do not modify these values until part g. Follow the instructions below to calculate the net income for Monica, Nathaniel, and Oscar. Round your answers to the nearest hundredth. a) We will first fill in the cost of schooling for each worker. i) In cell B2 (Monica at age 6), type in =-$F$2 This will input the value in cell F2 into cell B2. Click on cell B2. On the bottom right corner, there should be a small green box. Click on this box and drag it all the way to cell B18 (Monica at age 22). This should copy the value in F2 to cells B2-B18. The value represents the amount Monica pays for schooling from age 6 until age 22. ii) In cell C2 (Nathaniel at age 6), type in --$G$2 This will input the value in cell G2 into cell C2. Click on cell C2. On the bottom right corner, there should be a small green box. Click on this box, hold down, and drag it all the way to cell C14 (Nathaniel at age 18). This should copy the value in G2 to cells C2-C14. The value represents the amount Nathaniel pays for schooling from age 6 until age 18. iii) In cell D2 (Oscar at age 6), type in =-$H$2 This will input the value in cell H2 into cell D2. Click on cell D2. On the bottom right corner, there should be a small green box. Click on this box, hold down, and drag it all the way to cell D22 (Oscar at age 26). This should copy the value in H2 to cells D2-D22. The value represents the amount Oscar pays for schooling from age 6 until age 26. b) Monica's salary each year i) In cell B19 (Monica at age 23), type in the following =$F$5*(1+$F$8) (A19-$A$19) This is Monica's starting salary ($F$5) multiplied by the growth rate of her salary (1+SF$8), which is in turn raised to the number of years since the start of her career (A19-$A$19). Be very careful about where you put the $ symbol - this is important for what we will do next, and for programming in Excel in general! (The $ symbol fixes the location of the reference cell.) ii) Click on cell B19. Drag the lower right corner from B19 to 361 (Monica at age 65). The cells B19-B61 should populate with Monica's salary for each year. Check that the earnings are growing each year from age 22 to 65 for Monica c) Nathaniel's salary each year i) In cell C15, type in the following =$G$5*(1+$G$8) * (A15-$A$15) This is Nathaniel's starting salary ($G$5) multiplied by the growth rate each year (1+$G$8), which is in turn raised to the number of years since the start of his career (A15-$A$15). Again, be careful about where you put the $ symbol. ii) Click on cell C15. Drag the lower right corner from C15 to C61 (Nathaniel at age 65). The cells C15-C61 should populate with Nathaniel's salary for each year. Check that the earnings are growing each year from age 19 to 65 for Nathaniel. d) Oscar's salary each year i) In cell D23, type =$H$5*(1+$H$8) (A23-$A$23) This is Oscar's starting salary ($H$5) multiplied by the growth rate each year (1+$H$8), which is in turn raised to the number of years since the start of his career (A23-$A$23). 2 ii) Click on cell D23. Drag the lower right corner from D23 to D61 (Oscar at age 65). The cells D23-D61 should populate with Oscar's salary for each year. Check that the earnings are growing each year from age 27 to 65 for Oscar. e) Net Lifetime Wealth i) In cell B63 (lifetime earnings for Monica), type the following SUM(B2:B61) This calculates the total amount of earnings and costs for Monica from age 6 to 65. This is her net lifetime wealth. ii) Click on cell B63. Click on the small green box in the lower right corner and drag it from cell B63 to cell D63. This should compute the net lifetime wealth for Nathaniel (C63) and Oscar (D63). f) Use the spreadsheet to answer the following questions. Round your answers to the nearest cent. (-5 more points if cannot follow work on spreadsheet) i) How much does Monica earn at age 34? ii) How much does Oscar earn at age 50? iii) How much does Nathaniel earn at age 58? iv) What is Monica's net lifetime wealth? What is Nathaniel's net lifetime wealth? What is Oscar's net lifetime wealth? g) For the questions below, we will change the parameters of the problem. In your answers, round your answers to the nearest cent. i) Now, suppose that everyone's growth rate of earnings double. Replace cell F8 with 0.04, cell G8 with 0.02, and cell H8 with 0.10. What is the net lifetime wealth for Monica, Nathaniel, and Oscar now? ii) In addition to the changes in i), now suppose that Oscar's starting salary is $80,000. Replace H5 with 80,000. What is Oscar's net lifetime wealth now? iii) In addition to the changes in i) and ii), now suppose that Nathaniel's cost of education is $1200 per year. Replace G2 with 1200. What is Nathaniel's net lifetime wealth now? 3