Sample Spreadsheet (Refer to Figure 1.2 or 1.4 in the book). A B D E F G H 1 K L 1 2 3 4 Net Worth as of 1/1/2015 ASSETS Cash Checking Account Savings & CDs Investment Accounts Car House $ $ $ $ $ $ 225 2,345 8,765 12,345 48,600 426,500 TOTAL ASSETS $ 498,780 Assets, $498,780 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 LIABILITIES Credit Cards Car Loan Balance Student Loan Balance Mortgage Balance $ $ $ $ (3,657) (46,170) (180,210) (332,670) Liabilities, $562,707 TOTAL LIABILITIES $ (562,707) NET WORTH $ S- $300,000 $100,000 $200,000 $400,000 $500,000 (63,927) $600,000 NET MONEY $ 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 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). A B D E F G H 1 K L 1 2 3 4 Net Worth as of 1/1/2015 ASSETS Cash Checking Account Savings & CDs Investment Accounts Car House $ $ $ $ $ $ 225 2,345 8,765 12,345 48,600 426,500 TOTAL ASSETS $ 498,780 Assets, $498,780 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 LIABILITIES Credit Cards Car Loan Balance Student Loan Balance Mortgage Balance $ $ $ $ (3,657) (46,170) (180,210) (332,670) Liabilities, $562,707 TOTAL LIABILITIES $ (562,707) NET WORTH $ S- $300,000 $100,000 $200,000 $400,000 $500,000 (63,927) $600,000 NET MONEY $ 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 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