Edmund and Lydia Stefanek of Little Rock, Arkansas, are using Excel to track their family budget...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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 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: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. 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 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: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.
Expert Answer:
Answer rating: 100% (QA)
Stefanek Family Budget Mortgage Annual Interest Rate Interest Rate Per Month 285000 430 036 Years 25 ... View the full answer
Related Book For
Concepts in Federal Taxation
ISBN: 9780324379556
19th Edition
Authors: Kevin E. Murphy, Mark Higgins, Tonya K. Flesher
Posted Date:
Students also viewed these accounting questions
-
A $198 000 mortgage amortized by monthly payments over 20 years is renewable after 5 years. Interest is 4.65% compounded semi-annually. (a) What is the size of the monthly payments? (b) How much...
-
Consider a call option with the following characteristics: Stock price: $90.32 Exercise price: $74.51 Risk-free rate: 5.9%, compounded continuously Maturity: 159 days (assume there are 365 days in a...
-
Who is Adobe's target audience for marketing? How does Adobe advertise to its customers?
-
Table # 11.1.1: Autism Versus Breastfeeding Breast Feeding Timelines Autism Yes No None 241 20 Column Total 261 Less than 2 months 198 25 223 2 to 6 months 164 27 191 More than 6 months 215 44 259...
-
Who is usually responsible for sales-activity variances? Why?
-
The Hickory Company manufactures wooden desks. Management schedules overtime every weekend to reduce the backlog on the most popular models. The automatic routing machine is used to cut certain types...
-
Create a table about the password policies enforced at the following: a. Your schools network b. Your schools email system c. Your personal email account (at Yahoo, Gmail, etc.) d. Your financial...
-
To: [Your name] From: Supervising Attorney Re: Commonwealth v. Clavel; execution of search warrantunannounced entry We represent Darren Clavel in the case of Commonwealth v. Clavel. In this case,...
-
Business Model Canvas (operations of a family business) Explain different BMC segments, such as value propositions, channels, customer relationships, customer segments, revenue streams, key...
-
Consider the cylindrical shaped absorbent material designed to selectively remove solute A from solution. The uptake of solute A through the homogeneous absorbent material is limited by the molecular...
-
Based on today's quote for ABC stock and option with 6 months to expiration: Current stock price for ABC: 100 Stock pays no dividends. Strike price of call option: 97 Annual risk-free rate: .03...
-
Not-so-good social media post Why? Read more here: https://theconversation.com/companies-are-increasingly-turning-to-social- media-to-screen-potential-employees-141926
-
What is a selectively permeable membrane? Explain
-
Class Activity 10: Persuasive Sales Letter You are a marketing manager of a large size software house. Through some reliable sources, you have got information that Saputo Dairy Foods Canada is not...
-
What is the author of this text saying? - in the article Food for Thought by Marie Fitrion. Now imagine that Marie Fitrion wrote a different kind of text instead of this one: a letter, like "Dear...
-
A statement in an annual report reflecting a Non-GAAP measure where statements are made about guesses or speculations about how some unforeseen event like COVID-19 impacted sales in 2020 is calle?
-
The most restrictive access modifier that offers accessibility only within the body of the class is ___. O internal O public O protected O private A constructor and its class always have the same...
-
Explain five different cases of income exempt from tax with clear examples.
-
Are the following payments deductible? a. A contribution to a fund to finance Honest Abes campaign for mayor. b. A contribution to the Hardcore Gamblers Association to fund efforts to persuade the...
-
Twenty years ago, Consuela Guererro invented and patented a high-speed burritostuffing machine. Through the years, she has jealously guarded her invention, allowing its use only in El Consuelas, a...
-
A fire extensively damaged a small Alaska town where Intech Company had its primary plant. Intech decided to give $200 to each household that lost its residence. About 12% of the payments were made...
-
Which of the following is not an advantage of transferable pollution rights? a. They create incentives for polluters to develop cheaper ways to reduce pollution. b. They allow the greatest value of...
-
What is the relationship between externalities and property rights?
-
An advantage that corrective taxes and tradable emissions permits have over compliance standards is that the former a. works well even if pollution output cannot be accurately measured. b. results in...
Study smarter with the SolutionInn App