er EM4, Problem 1CP 1 Bookmark Show all steps: ON SK AK Data File needed for this Case Problem: Stefanek.xlsx Stefanek Budget Edmund and Lydia Stefanek of Little Rock, Arkansas, are using Excel to track their family budget to determine whether they can afford the monthly loan payments that would come with the purchase of a new house. The couple is considering a $285,000 mortgage at a 4.30 percent interest rate to be paid back over 25 years. They want to know the impact that this mortgage will have on their budget. Complete the following 1. Open the Stefanek workbook located in the Excel4 > Case1 folder Included with your Data Files, and then save the workbook as Stefanek Budget in the location specified by your instructor. 2. In the Documentation worksheet, enter your name in cell B3 and the date in cell B4. 3. In the Budget worksheet, in the range B3 B8. enter the parameters for a $285,000 mortgage at 4.3% annual interest paid back over 25 years. Calculate the interest rate per month and the total number of payments 4. In cell B10, calculate the amount of the monthly payment needed to pay back the mortgage 5 in the range C15 N15, calculate the total income from Edmund and Lydia's monthly salaries 6. in the range C22 N22 use an absolute reference to insert the monthly mortgage payment you calculated in CAR10 1559ps jamaica ping Zoom on fron ** 6. In the range C22 N22, use an absolute reference to insert the monthly mortgage payment you calculated in cell B10. > 7. In the range C24 N24, calculate Edmund and Lydia's total expenses per month. 8. In the range C25:N25, calculate the couple's monthly net income by adding their income and their expenses. (Note that expenses are entered as negative values.) 9. In the range C28:C40, calculate the averages for the income and expenses from the 12-month budget. 10. In the range C28 C40, add data bars to the values. Note that negative data bars are displayed to the left of the center point in the cell, whereas positive data bars are displayed to the right 11. In the range D28:040, insert line sparklines using the values from the range C13 N25 to show how the different budget entries change throughout the year 12 Create a pie chart of the income values in the range B28.C29 to show the breakdown of the family income between Edmund and Lydia Resize the chart to cover the range E27:140 Change the chart title to Income and apply the Style3 chart style to chart 1159ps-Jamaica Ppng Zoom cm fonn29...exe oter EM4, Problem 1CP Dooger 1 Bookmark Show all steps ON ak 12-month are 10. In the range C28:C40, add data bars to the values. Note that negative data bars a displayed to the left of the center point in the cell, whereas positive data bars are displayed to the right 11. In the range D28:D40, insert line sparklines using the values from the range C13:N25 to show how the different budget entries change throughout the year. 12. Create a pie chart of the income values in the range B28 C29 to show the breakdown of the family income between Edmund and Lydia. Resize the chart to cover the range E27:140. Change the chart title to Income and apply the Style3 chart style to chart. 13. Create a pie chart of the expenses values in the range B31:C38. Resize the chart to cover the range J27 N40. Change the chart title to Expenses and apply the Style3 chart style to the chart. Change the position of the data labels to data callouts. If any data labels appear to overlap, select one of the overlapping data labels, and drag it to another position. 14. Save the workbook, and then close it. Step-by-step solution Stefanek Budget Saved Search File Home Insert Draw Page Layout Formulas Data Review View Help Corbel 11 A A Wrap Text General Paste BIU A Merge & Center $ % 99 Conditional Format as Formatting Cell Insert Table Styles- Clipboard F Font Alignment Number Styles 83 3 Sx Megan Harbaugh A B D E F H 1 2 Stefanek Family Budget 3 Author 4 Date Purpose Megan Harbaugh To provide an overview of the monthly budget of Edmund and Lydia Stefanek 56989 10 11 12 13 24 15 16 17 Fead Documentation Budget Type here to search 99+ 2 C Clipboard C8 L2 x Font fx B Stefanek Family Budget Mortage Anaval Interest Rus 285,000 4.30% 5 Interest Rate per Month 6 1 Yours 8 Total Payments 10 Monthly Payment 25 E F Merge & Center $-%9 468-98 Alignment E Number 12 Income & Expenses Jun Feb Mar May Jus 15 Ednand 4,750 4,750 4,750 4,750 4,750 14 Income Lydia 1,500 1,200 1500 1400 1,100 2,000 4,750 4,750 2.200 2,400 Aug 4150 Sep Oct Nov Dec 4,750 4,750 4,750 4,750 1,500 1000 1300 1500 Total Income 16 Food -460 450 575 615 545615 Rest -015 515 605 -63 115 UNMA 135 -115 -145 -125 130 -135 -155 -60 -125 -135 -105 -125 16 Phons 85 -85 -105 110 120 -115 135 85 85 -125 -105 15 Isurance -410 -410 410 -475 -475 525 525 -525 -525 -515 515 575 Exptores Car -325 325 325 325 -325 -325 325 325 1325 325 025 Travel -105 55 135 275 -1100 -225 -235 -1,750 -75 -125 -715 -725 Mortgage Misc 400 -350 300 075 5.1750 -215 100 2,420 210 315 415 835 Team Expences 25 NCT Net Income 21 Monthly Average Trend Edmund fecome Total Incone Food Utilities Phone versace Exponitz Cy 136 Tran 40 41 Ready Monge Misc Total Expense Not Income Documentation Budget O