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

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.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    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