Hello, Please help in creating a balance sheet ( question 1) and a cash flow (question 2). This is for a couple and the purpose is for financial planning and budgeting. The information, their salaries, expense, assets, and liabilities are given in the subsequent pages below. Most of the information is given below but I know that some of the payments need to be calculated such as auto payments and home mortgage payments. I am 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 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 Texaco Master Card Visa Original Amount of Account 25,000 Dillard's 112,000 28,000 20,000 Credit Cards - January 1, 2022 (all joint accounts) 421246 11232 Property or Interest Service Rate Purchased Education 6.5% House 128466 76337 Nissan Titan Jeep Liberty Annual Fee 1765421 $0 1755 $35 2418178 $0 1554 $0 LIABILITIES 16.0% 5.0% Interest Rate 14.0% 21.0% 6.0% 6.0% Current Payment Balance Amount Credit Limit $1,500 $5,000 $ $1,000 $ $ $ 8.8% $15,000 $10,292 $ $0 $ $358 $ Outstanding Balance $120 $ How often paid Monthly Monthly Monthly 60 Monthly Balance or $25 5% of Balance or $25 10% of Total # of Payments Balance or $20 Min. Grace Payment Period 20% of yes Balance or $50 5% of * All credit cards except for the MasterCard are paid in full every month * No new charges were made on MasterCard in 2022 120 360 60 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 0 AUTO INSURANCE *Description Company Policy Number Liability Personal Injury Protection Uninsured Motorist Collision Deductible Comprehensive Deductible Annual Premium Comments HOMEOWNERS INSURANCE Company Dwelling 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) 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 $ $ 1,800 Paid as part of monthly mortgage payment 800 (1/2% of dwelling coverage) + 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 Per Acquired Share 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 4,000 108.80 160. 98.12 4906 116.75 60.00 3000 78.54 18.80 7,520 24.18 Current Value $14,000 $22,000 Current Value Per Share 0 Replacement Value $225,000 $ 45,000 $ 35,000 $ 5,000 Total 2720 5,838 3,927 9,672 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 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 Texaco Master Card Visa Original Amount of Account 25,000 Dillard's 112,000 28,000 20,000 Credit Cards - January 1, 2022 (all joint accounts) 421246 11232 Property or Interest Service Rate Purchased Education 6.5% House 128466 76337 Nissan Titan Jeep Liberty Annual Fee 1765421 $0 1755 $35 2418178 $0 1554 $0 LIABILITIES 16.0% 5.0% Interest Rate 14.0% 21.0% 6.0% 6.0% Current Payment Balance Amount Credit Limit $1,500 $5,000 $ $1,000 $ $ $ 8.8% $15,000 $10,292 $ $0 $ $358 $ Outstanding Balance $120 $ How often paid Monthly Monthly Monthly 60 Monthly Balance or $25 5% of Balance or $25 10% of Total # of Payments Balance or $20 Min. Grace Payment Period 20% of yes Balance or $50 5% of * All credit cards except for the MasterCard are paid in full every month * No new charges were made on MasterCard in 2022 120 360 60 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 0 AUTO INSURANCE *Description Company Policy Number Liability Personal Injury Protection Uninsured Motorist Collision Deductible Comprehensive Deductible Annual Premium Comments HOMEOWNERS INSURANCE Company Dwelling 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) 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 $ $ 1,800 Paid as part of monthly mortgage payment 800 (1/2% of dwelling coverage) + 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 Per Acquired Share 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 4,000 108.80 160. 98.12 4906 116.75 60.00 3000 78.54 18.80 7,520 24.18 Current Value $14,000 $22,000 Current Value Per Share 0 Replacement Value $225,000 $ 45,000 $ 35,000 $ 5,000 Total 2720 5,838 3,927 9,672
