Create a Personal Net Worth Statement for the family in Excel. Make sure you show formula
CASE FACTS BEN AND SUE SMITH PERSONAL BACKGROUND AND INFORMATION Ben Smith Ben, age 62, has been married to Sue for 35 years. He is a highly trained engineer, a successful businessman, and the CEO of an air filtration company, Air Technologies, LLC, a Delaware Limited Liability Company. Ben owns a 90% Managing member interest, and his business associate, Hank Mitchell, owns a 10% member interest. Sue Smith Sue, age 64, works as a marketing manager for Best Advertising Agency. She has been with her current employer for 10 years and participates in the employer's Section 401(k) plan. Children Ben and Sue have two children, Mary Wilson and Scott. Mary, age 32, is married and has two children. Scott, age 28, is single and recently received an MBA from a private university. INSURANCE INFORMATION Health Insurance The Smiths maintain a high-deductible health plan. Under the Smiths' high-deductible health plan, the annual deductible is $3,000 and their annual out-of-pocket expenses are limited to $7,500. Life Insurance Ben owns a $500,000 universal life policy on his own life (Policy A). It currently has a cash value of $25,000 and has a waiver of monthly deductions and accidental death benefit riders. He also owns a $2 million, 10-year level term life insurance policy (Policy B). Sue is named as the beneficiary on both policies. Disability Insurance Neither Ben nor Sue have disability coverage Homeowner's Insurance The Smiths have a HO-3 policy through Guardsafe Insurance Co. for their personal residence and an HO-6 policy for their vacation condominium, also through Guardsafe. Automobile Insurance The Smiths have auto coverage on both of their vehicles, a 2012 Cadillac Escalade and a 2011 Lexus GX. INVESTMENT INFORMATION The Smiths have a joint checking account with a balance of $40,000 as of 10/31/2016 and a joint savings account with a balance $75,000 as of 10/31/2016. Ben also has a brokerage account at Schwab in the name of his revocable trust, the Benjamin Smith 2012 Revocable Trust. The brokerage account holds the following assets: $10,000 of cash 5,000 shares of XYZ stock valued at $50/share Ben plans to gradually phase out of the business and would like to sell his interest in the business to his business associate, Hank. As such, Hank and Ben have been discussing what it would cost Hank to buy out Ben from Air Technologies, LLC. Ben and Hank recently had a valuation done on the company, which pegged the value of the company at $10,000,000. Sue's father also left a portfolio of bonds to her when he died. The Vanguard bond portfolio consists of long-term US Treasury bonds valued at $91,000 as of 10/31/2016. RETIREMENT INFORMATON 401(k) Plans Ben is covered by a Section 401(k) plan at Air Technologies, LLC. He contributes the maximum allowable and the company matched $.50 for every dollar he defers up to the allowable limited, excluding catch-up deferrals. The account balance in the plan was $750,000 as of 10/31/2016. Sue is also covered by a Section 401(k) plan, which had an account balance of $200,000 as of 10/31/2016. INFORMATION REGARDING OTHER ASSETS AND LIABILITIES Homes The Smiths jointly purchased their illinois residence 22 years ago for $3M. The current fair market value of the residence is $5M and has a replacement value of $4.8M. The Smiths have always used the home as their personal residence. There is no mortgage on the property. Ben and Sue also own a vacation condo in Florida they inherited from Sue's aunt, who died in 2011. The aunt originally purchased the condo for $750,000 in 1997, and it was valued at $1.25M for estate tax purposes in her estate. Its fair market value is now estimated at $1.5M and recently the Smiths took out a mortgage on the condo for $500,000. Automobiles The Smiths jointly own two automobiles, a 2012 Cadillac Escalade worth $25,500 and a 2011 Lexus GX worth $19,000. Neither automobile has an outstanding loan balance. Credit Cards The Smiths pay all of their credit card balances in full each month, so they pay no interest charges. Student Loan The Smiths are paying off Scott's student loans at the rate of $12,000 per year. There is a remaining balance of $74,400 on Scott's loans. CASE FACTS BEN AND SUE SMITH PERSONAL BACKGROUND AND INFORMATION Ben Smith Ben, age 62, has been married to Sue for 35 years. He is a highly trained engineer, a successful businessman, and the CEO of an air filtration company, Air Technologies, LLC, a Delaware Limited Liability Company. Ben owns a 90% Managing member interest, and his business associate, Hank Mitchell, owns a 10% member interest. Sue Smith Sue, age 64, works as a marketing manager for Best Advertising Agency. She has been with her current employer for 10 years and participates in the employer's Section 401(k) plan. Children Ben and Sue have two children, Mary Wilson and Scott. Mary, age 32, is married and has two children. Scott, age 28, is single and recently received an MBA from a private university. INSURANCE INFORMATION Health Insurance The Smiths maintain a high-deductible health plan. Under the Smiths' high-deductible health plan, the annual deductible is $3,000 and their annual out-of-pocket expenses are limited to $7,500. Life Insurance Ben owns a $500,000 universal life policy on his own life (Policy A). It currently has a cash value of $25,000 and has a waiver of monthly deductions and accidental death benefit riders. He also owns a $2 million, 10-year level term life insurance policy (Policy B). Sue is named as the beneficiary on both policies. Disability Insurance Neither Ben nor Sue have disability coverage Homeowner's Insurance The Smiths have a HO-3 policy through Guardsafe Insurance Co. for their personal residence and an HO-6 policy for their vacation condominium, also through Guardsafe. Automobile Insurance The Smiths have auto coverage on both of their vehicles, a 2012 Cadillac Escalade and a 2011 Lexus GX. INVESTMENT INFORMATION The Smiths have a joint checking account with a balance of $40,000 as of 10/31/2016 and a joint savings account with a balance $75,000 as of 10/31/2016. Ben also has a brokerage account at Schwab in the name of his revocable trust, the Benjamin Smith 2012 Revocable Trust. The brokerage account holds the following assets: $10,000 of cash 5,000 shares of XYZ stock valued at $50/share Ben plans to gradually phase out of the business and would like to sell his interest in the business to his business associate, Hank. As such, Hank and Ben have been discussing what it would cost Hank to buy out Ben from Air Technologies, LLC. Ben and Hank recently had a valuation done on the company, which pegged the value of the company at $10,000,000. Sue's father also left a portfolio of bonds to her when he died. The Vanguard bond portfolio consists of long-term US Treasury bonds valued at $91,000 as of 10/31/2016. RETIREMENT INFORMATON 401(k) Plans Ben is covered by a Section 401(k) plan at Air Technologies, LLC. He contributes the maximum allowable and the company matched $.50 for every dollar he defers up to the allowable limited, excluding catch-up deferrals. The account balance in the plan was $750,000 as of 10/31/2016. Sue is also covered by a Section 401(k) plan, which had an account balance of $200,000 as of 10/31/2016. INFORMATION REGARDING OTHER ASSETS AND LIABILITIES Homes The Smiths jointly purchased their illinois residence 22 years ago for $3M. The current fair market value of the residence is $5M and has a replacement value of $4.8M. The Smiths have always used the home as their personal residence. There is no mortgage on the property. Ben and Sue also own a vacation condo in Florida they inherited from Sue's aunt, who died in 2011. The aunt originally purchased the condo for $750,000 in 1997, and it was valued at $1.25M for estate tax purposes in her estate. Its fair market value is now estimated at $1.5M and recently the Smiths took out a mortgage on the condo for $500,000. Automobiles The Smiths jointly own two automobiles, a 2012 Cadillac Escalade worth $25,500 and a 2011 Lexus GX worth $19,000. Neither automobile has an outstanding loan balance. Credit Cards The Smiths pay all of their credit card balances in full each month, so they pay no interest charges. Student Loan The Smiths are paying off Scott's student loans at the rate of $12,000 per year. There is a remaining balance of $74,400 on Scott's loans
