VLOOKUP Excel Task: The Flower Shop (includes Excel File)

An ideal task to teach students how to use the VLOOKUP function in Excel and other essential skills.

Makes learning these skills simple and easy for students. Tasks include:

1. Use the VLOOKUP function to find the Markup percentage to be added to each flower arrangement.

2. Format the cells in Column B to Currency.

3. In the sale price column use a formula to calculate the sale price. Sale price is calculated by adding the cost to the markup percentage of the cost.

Example = cost + (cost *markup)

4. You now need to calculate the payroll for your employees. Each employee’s income tax rate is based on their monthly gross pay.

Open the Employees worksheet. Use the VLOOKUP formula to find the tax rate in the Tax Bracket table, for each employee.

5. Calculate each employees final pay after taxes are taken out. This is calculated by finding the amount of taxes and then subtracting that from the gross pay.

Tax amount is figured by multiplying the tax rate by the gross pay.

Example =GrossPay-(GrossPay*TaxRate)

6. Sort the data in Column B "Gross Pay" in Ascending order. (lowest to highest pays)

7. Insert a PIVOT CHART to display the employees wages on a new worksheet.

Select the data you want to put into a chart.

Go to INSERT - PIVOT CHART - CHOOSE NEW SHEET -CHOOSE the FIELDS_ you want to display in your table and chart.

Your data should now be displayed in a new table & chart on a new worksheet.

Note: May need to update prices to reflect local currencies and current prices.