Hello, I have the following questions below. Question 1 is to produce a personal balance sheet for financial planning purposes. Question 2 is to create a cash flow for the same purpose. The information is given in the subsequent pages. Please help me I am particularly struggling with the cash flow.
Andrew and Cynthia Bates would like your help in starting their financial plan. Review their financial and personal information before answering the following questions. 1. 2. 3. Using the January 1, 2022 asset and liability information, develop a balance sheet for Andrew and Cynthia Bates. Assume that Andrew and Cynthia have no unpaid monthly bills (but have not yet made their education, car, or house loans, but will pay them on 1/2). Assets on a balance sheet should be listed at the market current value, not the purchase price. Liabilities are listed at the current outstanding balance. What is their net worth? (calculate the value of the bolded liabilities BEFORE the January payment is made) Using the income and expenditure information for 2021, complete a cash flow statement for Andrew and Cynthia. Date this statement January through December of 2021. Divide the mortgage payment into its parts PI (principal & interest) is the mortgage payment, taxes are property taxes, and home insurance is homeowners insurance. Be sure to total your entries for each row in the third column of the cash flow statement. (Be aware that some income and payments (car insurance) is irregular). Did Andrea and Cynthia have a cash surplus or a cash deficit in 2021? What impact would the 2021 cash surplus (deficit) have on their January 1, 2021 balance sheet? (generally speaking). Based on Andrew and Cynthia's financial statements constructed in questions 1 and 2, calculate the following ratios: -- Liquidity Ratio -- Savings Ratio -- Debt Service Ratio Solvency Ratio ASSETS - January 1, 2022 All assets are community property. CHECKING MONEY MARKET ACCOUNT CDS CASH ON HAND # Shares 25 50 50 400 SECURITY INVESTMENTS Security LOCATION AUTOMOBILES Compass Bank American State Bank Year 2016 2018 E-Trade Bank E-Trade Bank Make Jeep Nissan Cracker Barrel (CBRL) Disney (DIS) Starbucks Corp. (SBUX) T. Rowe Price Equity Income (PRFDX) REAL & PERSONAL PROPERTY BALANCE House Furniture and Stereo and TVs Clothing and Jewelry Computers $1,929 $5,453 $10,000 $5,000 $430 Model Liberty Titan RATE OF INTEREST 0.01% 0.50% 2.9% 2.6% Cost of Basis Date Acquired 1/2019 4/2019 7/2017 7/2015 Market Value Cost $28,000 $30,000 $160,000 $ 22,000 $ 26,000 $ 2,000 MATURITY N/A N/A 5/1/2022 5/1/2023 Total Per Share 160. 108.80 4,000 98.12 4906 116.75 60.00 3000 78.54 18.80 7,520 24.18 Current Value Per Share Current Value $14,000 $22,000 Replacement Value $225,000 $ 45,000 $ 35,000 $ 5,000 Total 2720 5,838 3,927 9,672 Andrew's Salary Cynthia's Salary Bonuses Interest Dividends Capital gains distributions Sale of Securities4 2021 INCOME Fidelity Magellan Fund (FMAGX) Gross Income $54,000 66,000 5,700 760 388 272 7,754 1. Checking ($36 a year, paid monthly), money market account ($84 a year, paid monthly), Etrade bank CDs ($640 a year, paid in May). 2. Cracker Barrel ($130 a year), Disney ($176 a year) & Starbux ($82 a year) 3. T. Rowe Price Equity Income ($272 a year) Distributions paid quarterly in March, June, September and December. 4. 100 shares purchases Jan 16, 2015 at $63.37/share and sold March 20, 2021 at $77.54/share. All liabilities are community property debt To whom owed Texas Tech Credit Union Nationwide mortgage Co. Wells Fargo Wells Fargo Master Card Original Property or Amount Service of Purchased Account 25,000 Visa 112,000 Dillard's 28,000 20,000 Credit Cards - January 1, 2022 (all joint accounts) Annual Fee Texaco 1765421 $0 1755 Education 421246 11232 House Nissan Titan Jeep Liberty 128466 76337 $35 2418178 $0 1554 $0 LIABILITIES Interest Rate 16.0% 6.5% 5.0% 6.0% Interest Rate 14.0% 6.0% Current Balance Credit Limit $1,500 8.8% $15,000 $5,000 21.0% $1,000 $ $ $ $ Payment How Amount often paid $ $ $0 $ $ Outstanding Balance $120 $10,292 $358 Monthly Monthly Monthly 60 Monthly 60 Balance or $50 5% of Balance or $25 5% of Balance or $25 10% of Total # of Payments Balance or $20 Min. Payment 20% of yes * All credit cards except for the MasterCard are paid in full every month * No new charges were made on MasterCard in 2022 120 360 Grace Period yes yes yes Date of First Payment 11/1/14 6/1/2018 1/1/2019 4/1/2018 Calculation Method Av. Daily Balance Av. Daily Balance Av. Daily Balance Av. Daily Balance Federal Income Tax withholding Social Security Tax withholding Contributions to 401(k) pension (Cynthia) Home Mortgage Property Taxes 2021 EXPENSES Home Insurance Home repairs and maintenance Utilities Telephone Cable TV & Internet Groceries Food away from home Education Loan payment Auto Loan Payment (Nissan) Auto Loan Payment (Jeep) Auto maintenance Auto Insurance Minimum Payments on MasterCard Medical/Dental Expenses Clothing Personal Care Appliance, Furniture and Equipment Entertainment & Vacations Charitable contributions Gifts Reinvested Interest, Dividends and Capital Gains Cash Flow Monthly 1170 786 275 Calculate PI- don't forget taxes and insurance 150 90 200 400 200 415 350 65 Cash Flow Annually 14,040 9,432 3,300 300 20 40 3,550 See insurance information Calculate Calculate Calculate Calculate Calculate Calculate 1,300 1,300 1,800 1,080 2400 4,800 2,400 See insurance information 4,975 540 4,200 780 750 3,600 240 480 1,200 AUTO INSURANCE *Description Company Policy Number Liability Personal Injury Protection Uninsured Motorist Collision Deductible Comprehensive Deductible Annual Premium Comments HOMEOWNERS INSURANCE INSURANCE INFORMATION (1) Nissan Titan Insure Auto 134-234-456-AOB6 25/50/25 $5,000 25/50/25 Actual Cash Value $250 Actual Cash Value $100 $960 Paid semi annually (Feb 1, Aug 1) Company Dwelling Other Structures Persona Property on Premises (market value) Personal Property off Premises Additional Living Expenses (annual limit) Comprehensive Liability (per occurrence) Medical Payments (per person) Property Damage to Others (per occurrence) Deductible Annual Premium Comments (2) Jeep Liberty Insure Auto 786-675-345-AOB6 25/50/25 $5,000 25/50/25 Actual Cash Value $250 Actual Cash Value $100 $720 Paid Monthly Liberty Mutual $ 160,000 $ 16,000 $ 64,000 $ 4,500 $ 40,000 $ 160,000 $ 5,000 $ 1,000 $ 800 (1/2% of dwelling coverage) $ 1,800 Paid as part of monthly mortgage payment Andrew and Cynthia Bates would like your help in starting their financial plan. Review their financial and personal information before answering the following questions. 1. 2. 3. Using the January 1, 2022 asset and liability information, develop a balance sheet for Andrew and Cynthia Bates. Assume that Andrew and Cynthia have no unpaid monthly bills (but have not yet made their education, car, or house loans, but will pay them on 1/2). Assets on a balance sheet should be listed at the market current value, not the purchase price. Liabilities are listed at the current outstanding balance. What is their net worth? (calculate the value of the bolded liabilities BEFORE the January payment is made) Using the income and expenditure information for 2021, complete a cash flow statement for Andrew and Cynthia. Date this statement January through December of 2021. Divide the mortgage payment into its parts PI (principal & interest) is the mortgage payment, taxes are property taxes, and home insurance is homeowners insurance. Be sure to total your entries for each row in the third column of the cash flow statement. (Be aware that some income and payments (car insurance) is irregular). Did Andrea and Cynthia have a cash surplus or a cash deficit in 2021? What impact would the 2021 cash surplus (deficit) have on their January 1, 2021 balance sheet? (generally speaking). Based on Andrew and Cynthia's financial statements constructed in questions 1 and 2, calculate the following ratios: -- Liquidity Ratio -- Savings Ratio -- Debt Service Ratio Solvency Ratio ASSETS - January 1, 2022 All assets are community property. CHECKING MONEY MARKET ACCOUNT CDS CASH ON HAND # Shares 25 50 50 400 SECURITY INVESTMENTS Security LOCATION AUTOMOBILES Compass Bank American State Bank Year 2016 2018 E-Trade Bank E-Trade Bank Make Jeep Nissan Cracker Barrel (CBRL) Disney (DIS) Starbucks Corp. (SBUX) T. Rowe Price Equity Income (PRFDX) REAL & PERSONAL PROPERTY BALANCE House Furniture and Stereo and TVs Clothing and Jewelry Computers $1,929 $5,453 $10,000 $5,000 $430 Model Liberty Titan RATE OF INTEREST 0.01% 0.50% 2.9% 2.6% Cost of Basis Date Acquired 1/2019 4/2019 7/2017 7/2015 Market Value Cost $28,000 $30,000 $160,000 $ 22,000 $ 26,000 $ 2,000 MATURITY N/A N/A 5/1/2022 5/1/2023 Total Per Share 160. 108.80 4,000 98.12 4906 116.75 60.00 3000 78.54 18.80 7,520 24.18 Current Value Per Share Current Value $14,000 $22,000 Replacement Value $225,000 $ 45,000 $ 35,000 $ 5,000 Total 2720 5,838 3,927 9,672 Andrew's Salary Cynthia's Salary Bonuses Interest Dividends Capital gains distributions Sale of Securities4 2021 INCOME Fidelity Magellan Fund (FMAGX) Gross Income $54,000 66,000 5,700 760 388 272 7,754 1. Checking ($36 a year, paid monthly), money market account ($84 a year, paid monthly), Etrade bank CDs ($640 a year, paid in May). 2. Cracker Barrel ($130 a year), Disney ($176 a year) & Starbux ($82 a year) 3. T. Rowe Price Equity Income ($272 a year) Distributions paid quarterly in March, June, September and December. 4. 100 shares purchases Jan 16, 2015 at $63.37/share and sold March 20, 2021 at $77.54/share. All liabilities are community property debt To whom owed Texas Tech Credit Union Nationwide mortgage Co. Wells Fargo Wells Fargo Master Card Original Property or Amount Service of Purchased Account 25,000 Visa 112,000 Dillard's 28,000 20,000 Credit Cards - January 1, 2022 (all joint accounts) Annual Fee Texaco 1765421 $0 1755 Education 421246 11232 House Nissan Titan Jeep Liberty 128466 76337 $35 2418178 $0 1554 $0 LIABILITIES Interest Rate 16.0% 6.5% 5.0% 6.0% Interest Rate 14.0% 6.0% Current Balance Credit Limit $1,500 8.8% $15,000 $5,000 21.0% $1,000 $ $ $ $ Payment How Amount often paid $ $ $0 $ $ Outstanding Balance $120 $10,292 $358 Monthly Monthly Monthly 60 Monthly 60 Balance or $50 5% of Balance or $25 5% of Balance or $25 10% of Total # of Payments Balance or $20 Min. Payment 20% of yes * All credit cards except for the MasterCard are paid in full every month * No new charges were made on MasterCard in 2022 120 360 Grace Period yes yes yes Date of First Payment 11/1/14 6/1/2018 1/1/2019 4/1/2018 Calculation Method Av. Daily Balance Av. Daily Balance Av. Daily Balance Av. Daily Balance Federal Income Tax withholding Social Security Tax withholding Contributions to 401(k) pension (Cynthia) Home Mortgage Property Taxes 2021 EXPENSES Home Insurance Home repairs and maintenance Utilities Telephone Cable TV & Internet Groceries Food away from home Education Loan payment Auto Loan Payment (Nissan) Auto Loan Payment (Jeep) Auto maintenance Auto Insurance Minimum Payments on MasterCard Medical/Dental Expenses Clothing Personal Care Appliance, Furniture and Equipment Entertainment & Vacations Charitable contributions Gifts Reinvested Interest, Dividends and Capital Gains Cash Flow Monthly 1170 786 275 Calculate PI- don't forget taxes and insurance 150 90 200 400 200 415 350 65 Cash Flow Annually 14,040 9,432 3,300 300 20 40 3,550 See insurance information Calculate Calculate Calculate Calculate Calculate Calculate 1,300 1,300 1,800 1,080 2400 4,800 2,400 See insurance information 4,975 540 4,200 780 750 3,600 240 480 1,200 AUTO INSURANCE *Description Company Policy Number Liability Personal Injury Protection Uninsured Motorist Collision Deductible Comprehensive Deductible Annual Premium Comments HOMEOWNERS INSURANCE INSURANCE INFORMATION (1) Nissan Titan Insure Auto 134-234-456-AOB6 25/50/25 $5,000 25/50/25 Actual Cash Value $250 Actual Cash Value $100 $960 Paid semi annually (Feb 1, Aug 1) Company Dwelling Other Structures Persona Property on Premises (market value) Personal Property off Premises Additional Living Expenses (annual limit) Comprehensive Liability (per occurrence) Medical Payments (per person) Property Damage to Others (per occurrence) Deductible Annual Premium Comments (2) Jeep Liberty Insure Auto 786-675-345-AOB6 25/50/25 $5,000 25/50/25 Actual Cash Value $250 Actual Cash Value $100 $720 Paid Monthly Liberty Mutual $ 160,000 $ 16,000 $ 64,000 $ 4,500 $ 40,000 $ 160,000 $ 5,000 $ 1,000 $ 800 (1/2% of dwelling coverage) $ 1,800 Paid as part of monthly mortgage payment
