Kirk and Luann, a couple in their early 30s, have hired you to prepare financial statements for them. During your meeting on September 14, 20x1, a discussion of their financial situation reveals the following information. The Van Houtens currently have $2,500 in their checking account and $500 in cash. They live in a house they own, which is currently valued at $125,000. They pay $875 per month for mortgage payments on their house, and the principal outstanding on their house mortgage is $100,000. They also have a loan for $15,000 to renovate their kitchen. They have two cars – a Ford Pinto, valued at $650 and a Mercedes 560, valued at $30,000. They pay $450 per month to the bank towards their present $25,000 of car loans. They estimate that their household furnishings are worth $3,350, and their electronics equipment is worth $1,000 fair market value. Kirk works for the cracker factory and earns $1,346 gross biweekly pay checks (recall there are 52 weeks in a year), pays $7,000/year in taxes, and takes home $25,000/year — the remainder of his income goes toward a 401k. Luann works for a design firm and earns $1,539 gross biweekly pay checks, pays $8,000/year in taxes, and takes home $30,000/year — again, the remainder of her income goes into a 401k plan. Their combined retirement fund currently is valued at $34,500. They have a money market account with a balance of $10,000 and savings bonds valued at $1,500. They estimate their annual interest to be $500. They also own 100 shares of stock valued at $94/share. They also own a rental property valued at $60,000. The outstanding principal on this mortgage is currently at $50,000, and they pay $400 monthly in mortgage payments on this property. They also receive $6,000 per annum from their tenants. They have combined student loans of $25,000 that they make payments on, which are $300/monthly. Kirk and Luann pay $50/month for cable, $396/month for insurance, $1,200/year for club memberships, and $300/month in utilities. They estimate they spend $250/month for groceries, $3,000/year for clothing, $1,400/year for telephone, and $2,200/year for entertainment. They eat out and purchase alcohol and estimate this expense to be $3,500 per year. They typically spend $2,800 per year on vacations and buy gifts that total $2,500 per year as well. They also figure they spend $2,000 per year on miscellaneous items such as personal care items, household maintenance items, etc. They currently have $4,500 in credit card debt and make the minimum monthly payments on these credit cards that totals to $1,000 per year. Kirk and Luann also currently have $1,500 worth of bills in hand, a loan for a computer for $1,250, and a $1,000 outstanding loan for a big-screen television they purchased. They make payments on their consumer loans totaling $125/month. Kirk and Luann also discuss with you that they would like to save $20,000 more for a down payment on a house upgrade they are planning five years from now. They would also like to take a trip to Europe in two years and would like to have $5,000 available for this vacation. And, in three years they would like to have $15,000 to buy a new car. Your job is now to: • Access the Module 2 Lab Template • On the Networth tab, place assets and liabilities in the Net Worth statement, and record the appropriate date. Note: Dates are very important! • Place income sources, along with fixed and variable expenses, in the Annual Income and Expense (I/E) Statement on the Cashflow tab for the previous year. • Make an exact copy of the I/E statement (Cashflow tab), and name it "Budget." • Use the worksheet within the Budget tab to create an annual budget for the next year that incorporates the financial goals and, if necessary, modifies expenditures. For this budget, assume earned incomes, taxes, and 401k contributions next year will increase by 3%. Also assume that the annual interest rate on monthly contributions to planned savings will be 5% each year. • Calculate financial ratios on the Analysis tab, and discuss the appropriateness of each ratio given your client’s situation (We have not discussed this yet, but give it a shot based on how the ratios are calculated). Lab 2, page 2

Please help me calculate the gross income in this excel format. from the information above.

Home Insert Draw Page Layout CO- A1 18 18 20 21 22 23 AutoSave OFF A B C .... 8 Income 7 25 26 8 9 10 11 12 13 14 15 16 Fixed Expenses 17 32 33 34 35 36 37 38 39 40 41 42 Pasta 43 44 45 46 X ID 3 + 47 4A 27 28 29 30 31 Variable Expenses Arial V BIU x v x Income and Expense Statement D Income Income and Expense Statement se climt names For: Period: insa califowites Expenses SURPLUS Formulas Data Review 22 A A MA TOTAL INCOME Cashflow Total Fired Expenses Net Worth Ready Accessibility: Investigate Total Vanish Expans TOTAL EXPENSES B Analysis == = View 20% C Automate Tell me Wrap Text Module-2-Lab-Template Merge & Center v D General $ % 9 V E D Conditional Format Call Formatting as Table Styles F G H I Insert v Delete Format J K W 2 Y 28. Sart & Find & Du Filter Select L E BI M I N Comments PES Analyza Data 0 a Sansit Mity P Share Q + 100%