problem solving cases in Microsoft access and excel cse 113/cse 2111

Can someone help with how/ what function to input please?

110 Tutorial C PRACTICE EXERCISE- TED AND ALICE'S HOUSE PURCHASE DECISION Ted and Alioe are a young couple who have been living in an apartment for the first two years of thei marrisge. They would like to buy their arst house, but do not know whether they can afford it. Ted a carpenter's apprentice, and Alice is a customer service specialist at a local bank. In 201 wages were 824,000 after taxes and deductions, and Alice's take-home salary was 830,000 2% raise every year, and Alice gets a 3% raise. Their apartment rent is 81,200 per month (814.40 but the lease is up for renewal and the landlord sald he needs to increase the rent for the next lea bt. ear) to Your sp Ted and Alioe have been looking at houses and have found one they ean buy, but they will nced borrow 8200,000 for a morsdage. Their parents are belping them with the down payment and After talking to several lenders, Ted and Alice have learned that the state legislature its voting on a t e buyers mortgage bond. If the bill passes, they will be able to get a 30-year fixed marteide at 3%time interest. Otherwise, they will have to pay 6% interest on the morta. Because of the depressed housing market, Ted and Alice are not figuring equity value into their calculations. In addition, although the mortgage interest and real estate taxes will be deductible on their income taxes, these deductions will not be higher than the standard allowable tax deduction, so they are figuring on any savings there either. Ted and Alice's other living expenses (such as car payments, food, and medical bills), the utilities expenses for either renting or buying, and estimated house maintenance are listed in the Constants section (sce Figure C32) FIGURE Ted and Alice's primary eoncern is their cash on hand at the end of years 2014 and 2015. They are thinking of starting a family, but they know it will be difficult without adequate savings Getting Started on the Practice Exercise If you closed Excel after the first tutorial exercise, start Excel again-it will automatically open a new workbook for you If your Exeel workbook from the first tutorial is still open, you may find it useful to start a new worksheet in the same workbook. Then you can refer back to the first tutorial when you need to structure or format the spreadsheet, the formatting of both exercises in this tutorial is similar. Set up your new worksheet as explained in the following sections. Sum Figure on Ha house Constants Section Your spreadsheet should have the constants shown in Figure C-32. An explanation of the line items follows the figure. Tutorial Exercise Skeleton -Ted and Alice's House Decision Sourc FIGUR $3.160 5002,600 Source: Used with permission from Microsoft Corporation FIGURE C-32 Constants section Stater . Non-Housing Living Expenses-This value represents Ted and Alice's estimate of all their ocher Real Estate Taxes and Insurance on llome-A lender has given Ted and Alice estimates for tys . Udlities Expense-Apartment-This value is Ted and Alice's estimate for 2014 and 2015asd Calc living expenses for 2014 and 2015. Mortgage Amount for Home Purchase values; they are usually paid monthly with the house mortgage payment. The money is placed ia an escrow account and then paid by the mortgage company to the state or county on their 2013 bills. 110 Tutorial C PRACTICE EXERCISE- TED AND ALICE'S HOUSE PURCHASE DECISION Ted and Alioe are a young couple who have been living in an apartment for the first two years of thei marrisge. They would like to buy their arst house, but do not know whether they can afford it. Ted a carpenter's apprentice, and Alice is a customer service specialist at a local bank. In 201 wages were 824,000 after taxes and deductions, and Alice's take-home salary was 830,000 2% raise every year, and Alice gets a 3% raise. Their apartment rent is 81,200 per month (814.40 but the lease is up for renewal and the landlord sald he needs to increase the rent for the next lea bt. ear) to Your sp Ted and Alioe have been looking at houses and have found one they ean buy, but they will nced borrow 8200,000 for a morsdage. Their parents are belping them with the down payment and After talking to several lenders, Ted and Alice have learned that the state legislature its voting on a t e buyers mortgage bond. If the bill passes, they will be able to get a 30-year fixed marteide at 3%time interest. Otherwise, they will have to pay 6% interest on the morta. Because of the depressed housing market, Ted and Alice are not figuring equity value into their calculations. In addition, although the mortgage interest and real estate taxes will be deductible on their income taxes, these deductions will not be higher than the standard allowable tax deduction, so they are figuring on any savings there either. Ted and Alice's other living expenses (such as car payments, food, and medical bills), the utilities expenses for either renting or buying, and estimated house maintenance are listed in the Constants section (sce Figure C32) FIGURE Ted and Alice's primary eoncern is their cash on hand at the end of years 2014 and 2015. They are thinking of starting a family, but they know it will be difficult without adequate savings Getting Started on the Practice Exercise If you closed Excel after the first tutorial exercise, start Excel again-it will automatically open a new workbook for you If your Exeel workbook from the first tutorial is still open, you may find it useful to start a new worksheet in the same workbook. Then you can refer back to the first tutorial when you need to structure or format the spreadsheet, the formatting of both exercises in this tutorial is similar. Set up your new worksheet as explained in the following sections. Sum Figure on Ha house Constants Section Your spreadsheet should have the constants shown in Figure C-32. An explanation of the line items follows the figure. Tutorial Exercise Skeleton -Ted and Alice's House Decision Sourc FIGUR $3.160 5002,600 Source: Used with permission from Microsoft Corporation FIGURE C-32 Constants section Stater . Non-Housing Living Expenses-This value represents Ted and Alice's estimate of all their ocher Real Estate Taxes and Insurance on llome-A lender has given Ted and Alice estimates for tys . Udlities Expense-Apartment-This value is Ted and Alice's estimate for 2014 and 2015asd Calc living expenses for 2014 and 2015. Mortgage Amount for Home Purchase values; they are usually paid monthly with the house mortgage payment. The money is placed ia an escrow account and then paid by the mortgage company to the state or county on their 2013 bills