The Unit 11 Project is the culminating project for our three units on Microsoft Excel 2013. NOTE: You will use VLOOKUP and IF functions in this project. IF functions were covered in Excel Units 8-10 and you may need to go back into those Learning Assignments and projects to review the concept. For VLOOKUP, you will need to review Excel Chapter 2 - Working with Functions and Formulas available in Simnet. This Chapter is a learning assignment/SIMbook used in CL131 where this concept is currently taught. You can review it in Simnet by using Library and selecting the In Practice Excel 2013-Nordell and then selecting the chapter to open it.

Using Microsoft Excel 2013, create a new workbook that will be used to record the jobs performed and charges generated by a landscaping company. In the first worksheet, create columns for the employees of your landscaping business. Required fields for this worksheet include the following and you may add other fields if you wish:

Employee_ID, First_Name, Last_Name, and Hourly_Wage

You will add at least 10 records of your creation to populate this data. Sort in ascending order on Last_Name.

Create a second worksheet that will hold the fields to maintain a client list. Required fields for this table include the following and you may add other fields if you wish:

Client_ID, Client_Name, Client_ZipCode, HourlyBilling_rate (format currency with 2 decimal places)

You will add at least 10 records of your creation to populate this data. All values are fictional. Sort in ascending order by Client_ID.

Create a third worksheet to record jobs performed. Required fields for this table include the following and you may add other fields if you wish:

Job_ID, Date, Client_ID, Hours_Worked, Employee_ID, Cost, Bill_Amount, Discount (format Cost, Bill_Amount, and Discount as Currency, 2 decimal places)

You will add at least 20 records of your creation to populate this data. All values are fictional. The Employee_ID and Client_ID of each record in the Jobs worksheet must match a record in the respective Employees and Clients worksheets.

Cost is a calculated field that must include a VLOOKUP function linked to the Employee worksheet Hourly_Wage field and multiplied by the Hours_Worked field.

Bill_Amount is a calculated field that must include a VLOOKUP function linked to the Client worksheet and also multiplied by the Hours_Worked field.

Discount is a calculated field with an IF statement that will show if the Bill_Amount minus Cost is greater than $100, then the discount is 10% of the Bill_Amount.