View the step-by-step solution to:

Question

can you please provide the answer for the following worksheet?

Auto Sales sheet: 1. Move the contents of the

cells in the range L1-T15 over to A5-I19 (Under the column headings). 2. Using the "Fill Handle", copy the formula in G5 down through G20. (Note: error will be solved in next step). 3. Use a function to calculate the totals of the Invoice Price and Sales Price columns. 4. Center the sub-title across the worksheet columns and change the size to 14. 5. Format the two currency values in row 5 and the Total's row to "Accounting" style. 6. Format all other numbers in those two columns to comma "," style. 7. Reduce the values in the % column to two decimal places. 8. Re-size each column to the "Best fit" for the column. February Inventory sheet: 1. In cell D4, use a function that will count the cars in column A. 2. In cell D5, use a function to calculate the total of all invoice prices. 3. In cell D6, use a function to calculate the average of the invoice prices. 4. In cell F4, use a function to identify the highest invoice price. 5. In cell F5, use a function to identify the lowest invoice price. Income & Expenses sheet: 1. Calculate January's Car expenses (B10) by multiplying the Sales Income (B6) by the Cars % (B18) under the assumptions heading. Remember to use Absolute and Relative referencing. Copy this function across row 10 for February through December. (If you end up with any 0's, you didn't use the correct absolute and relative referencing in your formula). 2. Calculate January's Sales Force expenses (B11) using the above directions using the Sales Force % (B19) under assumptions and copy across row 11 through December. https://www.coursehero.com/file/34121396/Excel-Comprehensive-Exam-Instructions-Fall-17pdf/ This study resource was shared via CourseHero.com Comprehensive Exam, P a g e | 2 3. To calculate January's Bonuses (B12), use the IF function to compare January's sales income (B6) with the Target for bonus value in assumptions (B20). If they have met or exceeded the target, then they will get a bonus (multiplying the Bonus % under assumptions (B21) with the sales income (B6)). Be sure to use absolute and relative referencing. Copy this function across through December. Note, you will get some cells with 0's. Payment Calculator sheet 1. In cell D7, use the function which will calculate the payment the customer will owe each month. 2. Complete the two variable data table in cells F5-K20 which shows the payment based upon the number of months by interest rate (Note, you must use the Data Table tool in Excel - there will be no duplicated values in the Data Table. If there are then your data table is incorrect. You can highlight cells G6 - K20 and press the Delete key to try it again. If it isn't responding, press the Esc key once or twice, then try it again). 3. In cell D7, perform "Goal Seeking" to keep the monthly payment at $550 by changing the Sales Price. Accept the goal seeking changes. Copy and paste the new Sales Price in cell B5 to cell B12 (to store for later). 4. Change the Sales Price (B5) back to $32,000. (Note, when you do this, cell B12 should not change to $32,000) Employee Sales Summary sheet (Note: Do not make any changes to the Employee Sales January, February, and March sheets). 1. Replace the Sales value for Jeff Smith (E5) with a 3D cell reference function that will add together all his sales on the January, February, March sheets. (Be sure you are completing this formula on the Summary sheet and not on the other sheets. Use the other sheets in your 3D function). 2. Take this function (E5) and copy it down for all the other employees. Resize columns if necessary. 3. Create a Total row by first typing in the word "Total" in cell A17, then use a function(SUM) to total up columns E and G (The functions should be in cells E17 and G17 respectively). 4. Re-insert bottom border in E16 if it is missing. Instructions Complete.

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
Ask Expert Tutors You can ask 0 bonus questions You can ask 0 questions (0 expire soon) You can ask 0 questions (will expire )
Answers in as fast as 15 minutes