Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step	Instructions	Points Possible
1	Download and open the file named exploring_e06_grader_a1.xlsx, and then save the file as exploring_e06_grader_a1_LastFirst, replacing LastFirst with your name.	0
2	Use Goal Seek to set the monthly payment of the auto loan to $304.69 (15% of available capital) by changing the purchase price in cell E4. Beginning in cell G3, complete the series of loan amount values ranging from $16,000 to $30,000 in $500 increments in cells G3:G31.	5
3	Enter references for monthly payment, percent of total, and insurance premium in the cell H2, cell I2, cell J2 respectively for a one-variable data table.	7
4	Complete the one-variable data table using Amount of Loan as the column input cell, and then ensure the results for monthly payment and insurance premium are formatted as Accounting Number Format with two decimal places. Format percent of available income as Percentage Number Format with zero decimal places.	4
5	Apply custom number formats to make the formula references appear as the following descriptive column headings: H2-Monthly Payment, I2-% of Available Income, J2-Insurance Premium.	4
6	Copy the loan amount values from 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 from 3.00% to 4.25% using .25% increments through R2.	5
8	Enter the reference to the monthly payment formula in the cell L2 for 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 format the results with Accounting Number Format with two decimal places.	10
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 first column, first 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
21	Ensure that the worksheets are correctly named and placed in the following order in the workbook: Scenario Summary, Answer Report 1, Auto Loan. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.	0
	Total Points	100

H _exploring_e06_grader_a1 - Excel AutoSave Of File Home b Insert elv- Page Layout D x Comments Formulas Data Review View Help Search Calibri 11 A A === PAD SEX DO E 5 a Wrap Text Merge & Center * E - Share Er 49 0 Sort & Find & Ideas Filter Select Editing Ideas General $ -% , Paste LE BI UP A Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles Clipboard Font Alignment Number Cells A1 x for A Auto Loan Calculation B C | Auto Loan Calculation D E F G H I One-Variable Data Table: Auto Prices 2 $50,000.00 $ 4,166.67 6.25% $ 3,906.25 Auto Loan Information Purchase price Down payment Amount of loan Interest rate Periodic rate Total number of payments $ 30,000.00 $ 5,000.00 $ 25,000.00 4.00% 0.33% 60 9 Income 4 Annual Income 5 Monthly Income 6 Income tax rate 7 Monthly take home 8 Fixed Expenses 10 Rent 11 Utilities 12 Total 13 Variable Expenses 15 Dinning out 16 Entertainment 17 Misc 18 Total $ 1,200.00 $ 125.00 $ 1,325.00 14 Loan Summary Monthly payment Total value repaid Total interest paid Percentage of available capital Insurance rate based on total car price Annual insurance premium $460.41 $27,624.78 $2,624.78 23% 6% $1,500.00 $ $ $ $ 250.00 150.00 150.00 550.00 19 20 Available capital $ 2,031.25 22 Auto Loan @ -- O Type here to search SP ^ 0 1 5 10:59 AM 11/2/2019 F AutoSave Of H b elv- _exploring_e06_grader_a1 - Excel - o X Comments File Home Insert Page Layout Formulas Data Review View Help Search X Calibri 11 A A 5 === S LI DO E a Wrap Text 3 Merge & Center - Share E' 47 0 Sort & Find & Ideas Filter Select Editing Ideas General $ -% Paste BIU - D A Idees Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles Delete Format Sort & Find & Clipboard Font Alignment Number Cells A1 v x F fac G Auto Loan Calculation H E I One-Variable Data Table: Auto Prices J K M N O P Two-Variable Data Table: Purchase Price and APR Q R S T 2 3 4 5 6 7 8 $30,000.00 $ 5,000.00 $ 25,000.00 4.00% 0.33% 60 13 14 15 16 17 $460.41 $27,624.78 $2,624.78 23% 6% $1,500.00 Auto Loan @ 100% O Type here to search 4 | Bi e a a = 3 x x -- - + 10:55 AM a 1) 11/2/2019 pl ^ H _exploring_e06_grader_a1 - Excel AutoSave Of File Home b Insert elv- Page Layout D x Comments Formulas Data Review View Help Search Calibri 11 A A === PAD SEX DO E 5 a Wrap Text Merge & Center * E - Share Er 49 0 Sort & Find & Ideas Filter Select Editing Ideas General $ -% , Paste LE BI UP A Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles Clipboard Font Alignment Number Cells A1 x for A Auto Loan Calculation B C | Auto Loan Calculation D E F G H I One-Variable Data Table: Auto Prices 2 $50,000.00 $ 4,166.67 6.25% $ 3,906.25 Auto Loan Information Purchase price Down payment Amount of loan Interest rate Periodic rate Total number of payments $ 30,000.00 $ 5,000.00 $ 25,000.00 4.00% 0.33% 60 9 Income 4 Annual Income 5 Monthly Income 6 Income tax rate 7 Monthly take home 8 Fixed Expenses 10 Rent 11 Utilities 12 Total 13 Variable Expenses 15 Dinning out 16 Entertainment 17 Misc 18 Total $ 1,200.00 $ 125.00 $ 1,325.00 14 Loan Summary Monthly payment Total value repaid Total interest paid Percentage of available capital Insurance rate based on total car price Annual insurance premium $460.41 $27,624.78 $2,624.78 23% 6% $1,500.00 $ $ $ $ 250.00 150.00 150.00 550.00 19 20 Available capital $ 2,031.25 22 Auto Loan @ -- O Type here to search SP ^ 0 1 5 10:59 AM 11/2/2019 F AutoSave Of H b elv- _exploring_e06_grader_a1 - Excel - o X Comments File Home Insert Page Layout Formulas Data Review View Help Search X Calibri 11 A A 5 === S LI DO E a Wrap Text 3 Merge & Center - Share E' 47 0 Sort & Find & Ideas Filter Select Editing Ideas General $ -% Paste BIU - D A Idees Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles Delete Format Sort & Find & Clipboard Font Alignment Number Cells A1 v x F fac G Auto Loan Calculation H E I One-Variable Data Table: Auto Prices J K M N O P Two-Variable Data Table: Purchase Price and APR Q R S T 2 3 4 5 6 7 8 $30,000.00 $ 5,000.00 $ 25,000.00 4.00% 0.33% 60 13 14 15 16 17 $460.41 $27,624.78 $2,624.78 23% 6% $1,500.00 Auto Loan @ 100% O Type here to search 4 | Bi e a a = 3 x x -- - + 10:55 AM a 1) 11/2/2019 pl ^