Part A . You will make a spreadsheet that calculates the assets, liabilities, and net worth for a fictional family (see sample below). It is extremely important that the totals recalculate as you change any of the inputs. You must not "hard-code" the results of any numeric calculations, as this misses the objective of the assignment. Your spreadsheet will contain two sections, for assets and liabilities. In each section, you will create columns for asset (liability) name/description and value. Calculate the subtotal for each using the spreadsheet's SUM function. Below the assets and liabilities, calculate the family's net worth (assets minus liabilities) and net money (cash and bank deposits minus credit cards). Case Study Tommy, 32, and Gina, 28, with kid age 3, and live in New Jersey. Tommy is a longshoreman and earns $55,000 per year, and Gina manages a diner and earns $40,000 per year. Both expect to work until age 65 and currently 22% of their total income goes to taxes. Banking: They keep $200 in cash at their house and have $2,500 in their checking account. They also have $3,700 in a savings account. They have three credit cards, with a total spending limit of $25,000, and altogether they have a balance of $8,400. The minimum monthly payment on the credit card is $80 per month. Investments: They have several US Savings Bonds worth a total of $3,200, which they only want to use in case of emergencies. Tommy's 401k retirement account has a balance of $42,700, and Gina has an IRA (retirement account) with a balance of $7,300. Housing: Tommy and Gina own a house worth $300,000 with a mortgage of $178,000 on it. The principal and interest on their mortgage is $1,000 per month, and with taxes and insurance their total housing payment is $1,400 per month. Utilities: Tommy and Gina spend $100 per month on their phone bills, $75 per month for their internet, $40 per month for video streaming services, $250 per month on gas for their cars, $120 per month on their electric bill, $1,100 per month on daycare for their 3-year- old, and $750 per month on groceries. Cars: Tommy drives a 2008 Ford pickup truck, worth $4,500 and has no car loan on it. Gina drives a 2017 Hyundai Elantra worth $7,900. She has a car loan with a balance of $3,600 and payments of $300 per month. Sample Spreadsheet (Refer to Figure 1.2 or 1.4 in the book). 1 225 2.145 3 4 5 6 7 $ $ $ Net Worth as of 1/1/2015 ASSETS Cash Checking Account Savings & CDs Investment Accounts Car House $ $ 12345 48.600 426.500 TOTAL ASSETS $ 49,780 $498780 9 10 11 12 13 15 15 16 17 LIABILITIES Credit Cards $ 3,6571 Car Loan Balance $ (46,170) Student Loan Balance 5 (210) Mortgage Balance $ (332,670) Llabildes 5562,707 TOTAL LIABILITIES $($62,707) NET WORTH $ 19 20 21 22 23 24 (63,927) $100.00 500.000 NET MONEY 5 7.678 Note: make the graph show the absolute value (i.e, positive numbers) of the assets and liabilities. Create another cell in which you negate the value of liabilities to make it appear as a positive value. Part B You will calculate the Economic Net worth of this couple. Below the NET MONEY cells make a row with cells labeled: Name, Wages, Age, Retirement Age, Years of Work, Human Capital. Below that row, enter the data for Gina and Tommy and be sure to use formulas whenever appropriate. Below these rows create a new cell called Economic Net Worth, which consists of the family's total assets (both tangible and intangible) minus liabilities. Part You will make a monthly spending worksheet for this couple similar to the table below. Assume Tommy and Gina get the same income each month so you will need to figure out their after-tax monthly income. How much money can they save per month? Tommy wants to have another kid, but Gina is worried they don't have enough money. What would you suggest? 22.27% 77.73% B 1 Annual Amounts 2 Before-tax Income $ 40,000.00 3 Taxes $ 8,908.75 4 After-tax Income $ 31,091.25 5 6 Monthy Amounts 7 After-tax Income $ 2,590.94 8 9 10 Expenses Dollars 11 apartment $ 1,000.00 12 grocery $ 250.00 13 utilities (elect, heat, cell) $ 250.00 14 clothing 250.00 15 entertainment $ 250.00 16 transportation $ 100.00 17 health insurance $ 200.00 18 incidentals $ 50.00 19 20 21 Unallocated/Savings $ 240.94 22 23 ANNUAL SAVINGS $ 2,891.25 Percentage 39% 10% 10% 10% 10% 4% 8% 2% 9% Part A . You will make a spreadsheet that calculates the assets, liabilities, and net worth for a fictional family (see sample below). It is extremely important that the totals recalculate as you change any of the inputs. You must not "hard-code" the results of any numeric calculations, as this misses the objective of the assignment. Your spreadsheet will contain two sections, for assets and liabilities. In each section, you will create columns for asset (liability) name/description and value. Calculate the subtotal for each using the spreadsheet's SUM function. Below the assets and liabilities, calculate the family's net worth (assets minus liabilities) and net money (cash and bank deposits minus credit cards). Case Study Tommy, 32, and Gina, 28, with kid age 3, and live in New Jersey. Tommy is a longshoreman and earns $55,000 per year, and Gina manages a diner and earns $40,000 per year. Both expect to work until age 65 and currently 22% of their total income goes to taxes. Banking: They keep $200 in cash at their house and have $2,500 in their checking account. They also have $3,700 in a savings account. They have three credit cards, with a total spending limit of $25,000, and altogether they have a balance of $8,400. The minimum monthly payment on the credit card is $80 per month. Investments: They have several US Savings Bonds worth a total of $3,200, which they only want to use in case of emergencies. Tommy's 401k retirement account has a balance of $42,700, and Gina has an IRA (retirement account) with a balance of $7,300. Housing: Tommy and Gina own a house worth $300,000 with a mortgage of $178,000 on it. The principal and interest on their mortgage is $1,000 per month, and with taxes and insurance their total housing payment is $1,400 per month. Utilities: Tommy and Gina spend $100 per month on their phone bills, $75 per month for their internet, $40 per month for video streaming services, $250 per month on gas for their cars, $120 per month on their electric bill, $1,100 per month on daycare for their 3-year- old, and $750 per month on groceries. Cars: Tommy drives a 2008 Ford pickup truck, worth $4,500 and has no car loan on it. Gina drives a 2017 Hyundai Elantra worth $7,900. She has a car loan with a balance of $3,600 and payments of $300 per month. Sample Spreadsheet (Refer to Figure 1.2 or 1.4 in the book). 1 225 2.145 3 4 5 6 7 $ $ $ Net Worth as of 1/1/2015 ASSETS Cash Checking Account Savings & CDs Investment Accounts Car House $ $ 12345 48.600 426.500 TOTAL ASSETS $ 49,780 $498780 9 10 11 12 13 15 15 16 17 LIABILITIES Credit Cards $ 3,6571 Car Loan Balance $ (46,170) Student Loan Balance 5 (210) Mortgage Balance $ (332,670) Llabildes 5562,707 TOTAL LIABILITIES $($62,707) NET WORTH $ 19 20 21 22 23 24 (63,927) $100.00 500.000 NET MONEY 5 7.678 Note: make the graph show the absolute value (i.e, positive numbers) of the assets and liabilities. Create another cell in which you negate the value of liabilities to make it appear as a positive value. Part B You will calculate the Economic Net worth of this couple. Below the NET MONEY cells make a row with cells labeled: Name, Wages, Age, Retirement Age, Years of Work, Human Capital. Below that row, enter the data for Gina and Tommy and be sure to use formulas whenever appropriate. Below these rows create a new cell called Economic Net Worth, which consists of the family's total assets (both tangible and intangible) minus liabilities. Part You will make a monthly spending worksheet for this couple similar to the table below. Assume Tommy and Gina get the same income each month so you will need to figure out their after-tax monthly income. How much money can they save per month? Tommy wants to have another kid, but Gina is worried they don't have enough money. What would you suggest? 22.27% 77.73% B 1 Annual Amounts 2 Before-tax Income $ 40,000.00 3 Taxes $ 8,908.75 4 After-tax Income $ 31,091.25 5 6 Monthy Amounts 7 After-tax Income $ 2,590.94 8 9 10 Expenses Dollars 11 apartment $ 1,000.00 12 grocery $ 250.00 13 utilities (elect, heat, cell) $ 250.00 14 clothing 250.00 15 entertainment $ 250.00 16 transportation $ 100.00 17 health insurance $ 200.00 18 incidentals $ 50.00 19 20 21 Unallocated/Savings $ 240.94 22 23 ANNUAL SAVINGS $ 2,891.25 Percentage 39% 10% 10% 10% 10% 4% 8% 2% 9%