View the step-by-step solution to:

Office 2013 - myitlab:grader - Instructions Exploring Excel Ch. 06 - A1 Project What-If Analysis Project Description: You are budgeting for the...

looking for help, someone to help me walk thru this worksheet

Ofce 2013 – myitlab:grader – Instructions Exploring Excel Ch. 06 - A1 Project What-If Analysis Project Description: You are budgeting for the purchase of a new automobile. You are evaluating your expenses to determine the monetary resources available for a monthly payment. As part of the project you will perform What-IF Analysis and complete an amortization schedule. Instructions: For the purpose o± grading the project you are required to per±orm the ±ollowing tasks: Step Instructions Points Possible 1 Download and open the ²le named exploring_e06_grader_a1.xlsx , and then save the ²le as exploring_e06_grader_a1_LastFirst , replacing LastFirst with your name. 0 2 Use Goal Seek to set the monthly payment o± the auto loan to $304.69 (15% o± available capital) by changing the purchase price in cell E4. Beginning in cell G3, complete the series o± loan amount values ranging ±rom $16,000 to $30,000 in $500 increments in cells G3:G31. 5 3 Enter re±erences ±or monthly payment, percent o± total, and insurance premium in the cell H2, cell I2, cell J2 respectively ±or a one-variable data table. 7 4 Complete the one-variable data table using Amount o± Loan as the column input cell, and then ensure the results ±or monthly payment and insurance premium are ±ormatted as Accounting Number Format with two decimal places. Format percent o± available income as Percentage Number Format with zero decimal places. 4 5 Apply custom number ±ormats to make the ±ormula re±erences appear as the ±ollowing descriptive column headings: H2- Monthly Payment , I2- % of Available Income , J2- Insurance Premium . 4 6 Copy the loan amount values ±rom the one-variable table (range G3:G31) into the two-variable table starting in cell L3. 5 7 Type 3.00% into cell M2. Complete the series ±rom 3.00% to 4.25% using .25% increments through R2. 5 8 Enter the re±erence to the monthly payment ±ormula in the cell L2 ±or a two- variable data table. 5 9 Complete the two-variable data table using cell E7 as the row input cell and E6 as the column input cell , and then ±ormat the results with Accounting Number Format with two decimal places. 10 Updated: 07/18/2013 1 E_CH06_EXPV2_A1_Instructions.docx
Background image of page 1
Ofce 2013 – myitlab:grader – Instructions Exploring Excel Ch. 06 - A1 Project Step Instructions Points Possible 10 Apply a custom number Format to cell L2 to display Amount of Payment . 5 11 Create a scenario named Current loan option , using amount oF loan and interest rate as variable cells. Enter 25000 and 4.25% as the values For the scenario. Uncheck Prevent changes to change cell values. 5 12 Create a second scenario named Best loan option , using the same changing cells. Enter 16000 and 3.00% the values For the scenario. Uncheck Prevent changes to change cell values. 5 13 Create a third scenario named Worst loan option , using the same changing cells. Enter 30000 and 4.25% the values For the scenario. Uncheck Prevent changes to change cell values. 5 14 Generate a scenario summary report using the amount oF loan, monthly payment, and insurance premium as the results. 5 15 Clean up the summary report by deleting the ±rst column, ±rst row, and inFormation in rows 11, 12 & 13 (in that order). 5 16 Load the Solver add-in iF it is not already loaded. Display the Auto Loan worksheet. Open Solver and set the objective cell For the lowest monthly payment possible. 5 17 Use the cells containing purchase price and down payment as the by changing variable cells. 5 18 Set a constraints For the purchase price and down payment. The purchase price must be at least $16000 but no more than $18000. The down payment must be at least $500 but no more than $5000. 5 19 Set a constraint For the interest rate, which must be at least .03. 5 20 Solve the problem, generate an Answer Report, but keep the original values in the Auto Loan worksheet. 5 Updated: 07/18/2013 2 E_CH06_EXPV2_A1_Instructions.docx
Background image of page 2
Show entire document

This question was asked on Jul 19, 2016.

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors and customizable flashcards—available anywhere, anytime.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access or to earn money with our Marketplace.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
  • -

    Flashcards

    Browse existing sets or create your own using our digital flashcard system. A simple yet effective studying tool to help you earn the grade that you want!

    Browse Flashcards