Hello , Can you help me with this week assignment . Same as last ones .

Office 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 of grading the project you are required to perform the following tasks: Step 1 Points Possible Instructions 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 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. 2 5 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. 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. 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 twovariable 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. Updated: 07/18/2013 1 10 E_CH06_EXPV2_A1_Instructions.docx Office 2013 - myitlab:grader - Instructions Step Exploring Excel Ch. 06 - A1 Project Points Possible Instructions 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 Updated: 07/18/2013 2 E_CH06_EXPV2_A1_Instructions.docx Office 2013 - myitlab:grader - Instructions Step 21 Exploring Excel Ch. 06 - A1 Project Points Possible Instructions 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. Total Points Updated: 07/18/2013 3 0 100 E_CH06_EXPV2_A1_Instructions.docx Office 2013 - myitlab:grader - Instructions Exploring - Excel Chapter 7: Assessment Project 1 Specialized Functions Project Description: In the following project, you will perform sales analysis, calculate summary data using database functions, and complete an amortization table. Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Points Possible Instructions 1 Download and open the file named exploring_e07_grader_a1.xlsx, and then save the file as e02c1Sales_LastFirst, replacing LastFirst with your name. 2 Click the Sales Data by Agent worksheet and enter a nested function in cell H9 the Bonus column. If the employee is international and sold over $200,000 they receive 5% bonus, all other employees receive 3%. 3 Using the appropriate cell referencing, copy the function down the column. 7 4 Type Ron in cell B24. 4 5 Type Q1 in cell B25. 4 6 Enter a nested function in cell B26 that uses the cells B24 and B25 to return a specific sales record. 10 7 Click the Individual Awards worksheet and enter conditions in the Criteria Range for international sales reps that made $250,000 or more in sales. 10 8 Perform an advanced filter based on the criteria range. Set the filter to copy the new data into row 22. 10 9 Enter a database function to calculate the total number of international sales rep in cell J8. 12 10 Enter a database function to calculate the highest international sales dollar in cell J9. 3 Updated: 07/17/2013 1 0 10 E_CH07_EXPV2_A1_Instructions.docx Office 2013 - myitlab:grader - Instructions Step Exploring - Excel Chapter 7: Assessment Project 1 Points Possible Instructions 11 Click the Acquisition worksheet and then insert a function in cell E2 to calculate the loan amount based on the loan parameters. 4 12 Enter a formula in cell E3 to calculate the total number of periods. 2 13 Enter a formula in cell E4 to calculate the periodic monthly rate. 2 14 Enter a function in cell E5 to calculate the monthly payment. Modify the function to ensure that the result is a positive number. 2 15 Enter a function in cell E6 to calculate the total interest paid after five payments. Modify the function to ensure that the result is a positive number. 2 16 Complete the loan amortization table for the first five payments only. In cell A11, enter 1. In cell B11, create a relative reference to cell B7 and in cell C11, create a relative reference to cell E2. Use the DATE function to complete the Payment Date column and financial functions for the Interest Paid and Principal Payment columns. In cell F11, enter =C11-E11. In cell C12, create a relative reference to cell F11. Note: Be sure to only complete the table through row 15. 17 Save the file making sure the worksheets are in the following order: Sales Data By Agent, Individual Awards, and Acquisition. Close Excel. Submit the file as directed. Total Points Updated: 07/17/2013 2 18 0 100 E_CH07_EXPV2_A1_Instructions.docx Auto Loan Calculation Income Annual Income Monthly Income Income tax rate Monthly take home Auto Loan Information $ 50,000.00 $ 4,166.67 6.25% $ 3,906.25 Fixed Expenses Rent Utilities Total One-Variable Data Table: Auto Prices $ $ $ 1,200.00 125.00 1,325.00 Dinning out Entertainment Misc Total $ $ $ $ 250.00 150.00 150.00 550.00 Available capital . $ 2,031.25 Variable Expenses Purchase price Down payment Amount of loan Interest rate Periodic rate Total number of payments Loan Summary Monthly payment Total value repaid Total interest paid Percentage of available capital Insurance rate based on total car price Annual insurance premium $ 30,000.00 $ 5,000.00 $ 25,000.00 4.00% 0.33% 60 $460.41 $27,624.78 $2,624.78 23% 6% $1,500.00 Two-Variable Data Table: Purchase Price and APR Bonus Region 3% Domestic 5% International 2015 Sales Total By Quarter Sales Rep Ron Nick Sally Susan Bob Mark Swathi Mike Rick Rich Jill John Greg Look up Sales rep name Quarter Amount sold Q1 $ $ $ $ $ $ $ $ $ $ $ $ $ 29,911.00 32,752.00 36,991.00 50,087.00 52,923.00 59,678.00 66,385.00 66,936.00 74,507.00 76,889.00 90,515.00 97,426.00 98,094.00 Q2 $ 92,249.00 $ 30,222.00 $ 54,102.00 $ 25,179.00 $ 62,673.00 $ 70,934.00 $ 74,270.00 $ 72,838.00 $ 94,178.00 $ 49,266.00 $ 29,238.00 $ 43,061.00 $ 47,398.00 Q3 $ 46,475.00 $ 43,997.00 $ 63,914.00 $ 64,912.00 $ 63,635.00 $ 78,410.00 $ 36,165.00 $ 60,479.00 $ 41,391.00 $ 64,225.00 $ 30,973.00 $ 26,122.00 $ 80,755.00 Q4 $ $ $ $ $ $ $ $ $ $ $ $ $ 33,947.00 93,277.00 42,642.00 68,875.00 57,410.00 32,994.00 76,548.00 63,324.00 27,235.00 55,410.00 32,145.00 83,391.00 40,446.00 Total $ 202,582.00 $ 200,248.00 $ 197,649.00 $ 209,053.00 $ 236,641.00 $ 242,016.00 $ 253,368.00 $ 263,577.00 $ 237,311.00 $ 245,790.00 $ 182,871.00 $ 250,000.00 $ 266,693.00 Location International International Domestic Domestic Domestic Domestic Domestic Domestic Domestic Domestic Domestic Domestic International Bonus Criteria Sales Rep Q1 Q2 Q3 Q4 Total Location Sales Rep Q1 Q2 Q3 Q4 Total Location Ron $ 29,911.00 $ 92,249.00 $ 46,475.00 $ 33,947.00 $ 202,582.00 International Nick $ 32,752.00 $ 30,222.00 $ 43,997.00 $ 93,277.00 $ 200,248.00 International Sally $ 36,991.00 $ 54,102.00 $ 63,914.00 $ 42,642.00 $ 197,649.00 Domestic Susan $ 50,087.00 $ 25,179.00 $ 64,912.00 $ 68,875.00 $ 209,053.00 Domestic Bob $ 52,923.00 $ 62,673.00 $ 63,635.00 $ 57,410.00 $ 236,641.00 Domestic Mark $ 59,678.00 $ 70,934.00 $ 78,410.00 $ 32,994.00 $ 242,016.00 Domestic Swathi $ 66,385.00 $ 74,270.00 $ 36,165.00 $ 76,548.00 $ 253,368.00 Domestic Mike $ 66,936.00 $ 72,838.00 $ 60,479.00 $ 63,324.00 $ 263,577.00 Domestic Rick $ 74,507.00 $ 94,178.00 $ 41,391.00 $ 27,235.00 $ 237,311.00 Domestic Rich $ 76,889.00 $ 49,266.00 $ 64,225.00 $ 55,410.00 $ 245,790.00 Domestic Jill $ 90,515.00 $ 29,238.00 $ 30,973.00 $ 32,145.00 $ 182,871.00 Domestic John $ 97,426.00 $ 43,061.00 $ 26,122.00 $ 83,391.00 $ 250,000.00 Domestic Greg $ 98,094.00 $ 47,398.00 $ 80,755.00 $ 40,446.00 $ 266,693.00 International Sales Rep Q1 Q2 Q3 Q4 Total Location Totals Total Number of Domestic reps Total Number of International reps Max International Sales Max Domestic Sales Input Area Facility costs Down Payment # of Pmts per Year Years APR 1st Payment Date Payment # Summary Calculations Loan Amount No. Periods Monthly Rate Monthly Payment Total Interest Paid $ 720,000.00 $ 250,000.00 12 30 5.25% 3/20/2015 Payment Date Beginning Balance Interest Paid Principal Payment Ending Balance