Advanced Excel Skill Test Page 1 -Page 2

12. Create a Best Case and a Worst Case scenario for the car payment.
The Best Case changes the Present Value to \$20,000 and the number of payments to 48.
The Worst Case changes the Present Value to \$16,000 and the number of payments to 60.

13. Create a new spreadsheet in this workbook.

Add the Label: Sales Rep, Total Sold, Sales Goals and Bonus.

Add the names of three Sales reps

Add the amount they sold: \$1,000, \$2,500, and \$4,600.

14. Create a Lookup table with two columns: Sales Goals and Bonus.

The Sales Goals is formatted in currency.

The Bonus is formatted in percent.

The Sales Goals are \$500, \$1,000, \$2,500, and \$4,000.

The Bonuses are 5%, 10%, 15% and 20% of the Total Sold.

15. Calculate each Sales Rep's Bonus.

8. Make a copy of the Budgie spreadsheet and name it Summary.
Delete Columns C and D
Change the Product in Cell B4=All Computers

Create an equation in Cell C4 that adds the Total from the Budgie spreadsheet and the Total from the Expert Spreadsheet.

9. Start a new spreadsheet in this workbook.

Create the following labels:
Enter the Labels
In Cell A1 type: Present Value
In Cell A2 type: Interest
In Cell A3 type: Months
In Cell A4 type: Payment

In Cell B1=\$20,000
Cell B2=4%
Cell B3=48

10. Select Cell B4 and use the Function Wizard (fX) to calculate the Payment using the PMT function. Remember, the Rate (Cell B2) is divided by 12 to get a monthly payment.

11. Select Cell B4 and Goal seek to get the payment under \$400 by changing the number of payments in Cell B3. Remember the payment in the Goal Seek is a NEGATIVE number.