Using the income and expenditure information, in EXCEL, develop a cash flow statement. Show monthly and yearly.
No CREDIT CARDS - January 1, Current Year Annual Interest Credit Outstanding Min. Grace Calculation Fee Rate Limit Balance Payment Period Method Discover $0 14.5% $2,500 $2,304 20% of Yes Av. Daily Balance or $50 Balance Visa $0 22.9% $10,000 $441 5% of Balance Yes Av. Daily or $25 Balance American $35 12.0% $10,000 $6,481 10% of No Av. Daily Express Balance or $20 Balance Beall's $0 18.0% $500 SO 3% of Balance Av. Daily or $40 Balance Dillard's $0 24.5% $3,000 $696 10% of Yes Av. Daily balance or $50 Balance Only minimum payments are made each month on all credit cards CURRENT YEAR INCOME Gross Income Chips Salary $72,000 Joanna's Salary $208,000 Interest $175 Dividends $260 Capital gains distributions $169 Sale of Securities Amana Trust Growth Mutual Fund (AMAGX) $5,456 1. Checking ($7/year), savings ($27 a year), money market account ($141 a year, paid in August). 2. Fidelity Puritan ($232) and T. Rowe Price ($28 a year). All dividends paid in December. 3. Microsoft ($83) and Apple ($190). Distributions paid in December. 4. 200 shares purchased May 16 at $17.06/share and sold December at $27.28/share. CURRENT EXPENSES Cash Flow Cash Flow Monthly Annually House Payment 1,160 13,920 Home repairs and maintenance 3,000 Homeowner's insurance Property Taxes 300 3,600 Utilities 350 4200 Cell Phones 150 1,800 Cable TV and Internet 205 2,460 Groceries 475 5,700 Food away from home 600 7,200 Education loan payment (Wells Fargo) Education loan payment (Dept. of Educ.) Auto loan payment (Nissan) Auto loan payment (Mercedes) Auto maintenance 1,500 Medical/dental expenses (not covered by insurance) 625 Health insurance Disability insurance Auto insurance (paid semi-annually) 2,500 Life insurance Federal Income Tax withholding 69,976 Social Security Tax withholding 12,722 Credit Card Payments 940 11,280 Clothing 300 3,600 Personal Care 175 2,100 Appliance, Furniture and Equipment 1,500 Entertainment 275 3,300 Vacation (taken in July) 5,000 Children's activities 200 2,400 Childcare 1,500 18,000 Charitable contributions 750 9,000 Gifts 200 2,400 Miscellaneous 150 1,800 Maid and Lawn Service 300 3,600 Joanna's contribution to her 401(k) *Calculate the blank fields using TVM calculations or find within the case study document. No CREDIT CARDS - January 1, Current Year Annual Interest Credit Outstanding Min. Grace Calculation Fee Rate Limit Balance Payment Period Method Discover $0 14.5% $2,500 $2,304 20% of Yes Av. Daily Balance or $50 Balance Visa $0 22.9% $10,000 $441 5% of Balance Yes Av. Daily or $25 Balance American $35 12.0% $10,000 $6,481 10% of No Av. Daily Express Balance or $20 Balance Beall's $0 18.0% $500 SO 3% of Balance Av. Daily or $40 Balance Dillard's $0 24.5% $3,000 $696 10% of Yes Av. Daily balance or $50 Balance Only minimum payments are made each month on all credit cards CURRENT YEAR INCOME Gross Income Chips Salary $72,000 Joanna's Salary $208,000 Interest $175 Dividends $260 Capital gains distributions $169 Sale of Securities Amana Trust Growth Mutual Fund (AMAGX) $5,456 1. Checking ($7/year), savings ($27 a year), money market account ($141 a year, paid in August). 2. Fidelity Puritan ($232) and T. Rowe Price ($28 a year). All dividends paid in December. 3. Microsoft ($83) and Apple ($190). Distributions paid in December. 4. 200 shares purchased May 16 at $17.06/share and sold December at $27.28/share. CURRENT EXPENSES Cash Flow Cash Flow Monthly Annually House Payment 1,160 13,920 Home repairs and maintenance 3,000 Homeowner's insurance Property Taxes 300 3,600 Utilities 350 4200 Cell Phones 150 1,800 Cable TV and Internet 205 2,460 Groceries 475 5,700 Food away from home 600 7,200 Education loan payment (Wells Fargo) Education loan payment (Dept. of Educ.) Auto loan payment (Nissan) Auto loan payment (Mercedes) Auto maintenance 1,500 Medical/dental expenses (not covered by insurance) 625 Health insurance Disability insurance Auto insurance (paid semi-annually) 2,500 Life insurance Federal Income Tax withholding 69,976 Social Security Tax withholding 12,722 Credit Card Payments 940 11,280 Clothing 300 3,600 Personal Care 175 2,100 Appliance, Furniture and Equipment 1,500 Entertainment 275 3,300 Vacation (taken in July) 5,000 Children's activities 200 2,400 Childcare 1,500 18,000 Charitable contributions 750 9,000 Gifts 200 2,400 Miscellaneous 150 1,800 Maid and Lawn Service 300 3,600 Joanna's contribution to her 401(k) *Calculate the blank fields using TVM calculations or find within the case study document
