Imaging you are assigned to help a customer who is interested in home flooring renovation. The customer has requested a quote for his renovation project. You would need to prepare an invoice for your customer. To find out about your customers home location (City and Province) and the area of flooring see the excel file titled Home Renovation. The Area-City worksheet lists your customer information based on your Banner ID.

PLEASE READ the instructions carefully:

1. Create a VLOOKUP function to obtain your customer information from the table given in Area-City worksheet. You only create an invoice for your own customer.
2. Prepare an invoice for your customer. See the Invoice worksheet for a sample of invoice. You are welcome to use that sample format.
   1. Assume that

• Area of kitchen is 15% of the total renovation area
• Area of bathrooms is 10% of the total renovation area
• Area of living room and dining room is 35% of the total renovation area
• Area of bedrooms is 40% of the total renovation area
  2. Kitchen, living room and dining room flooring would be engineered wood flooring. Bathrooms would be natural stone tile floor and bedrooms would be carpet. Calculate the cost of flooring materials for kitchen, bathrooms, living room, dining room and bedrooms.

• Engineered wood flooring costs $ 4.45 per square foot and installation is $9.55 per square foot
• Natural stone tile floor costs $7.99 per square foot and installation is $7.25 per square foot
• Carpet flooring costs $ 3.99 per square foot and installation is $4.95 per square foot
  3. Create an IF function to calculate delivery cost (delivery only applies to the materials and assume that materials will be delivered all together at the same time):

• Delivery is free of charge on the order of flooring materials below $3500
• A $200 flat delivery rate applies on the order of flooring materials above $3500 and below $5000.
• A $300 flat delivery rate applies on the order of flooring materials above $5000.
  4. Notice that the sales and services tax rates are different in each Canadian province. Create a VLOOKUP function using the tax table below to calculate the sales and services tax.

Province	TYPE	PST	GST	HST	Total Tax Rate
Alberta	GST		5%		5%
British Columbia	GST + PST	7%	5%		12%
Manitoba	GST + PST	7%	5%		12%
New Brunswick	HST			15%	15%
Newfoundland and Labrador	HST			15%	15%
Northwest Territories	GST		5%		5%
Nova Scotia	HST			15%	15%
Nunavut	GST		5%		5%
Ontario	HST			13%	13%
Prince Edward Island	HST			15%	15%
Quebec	GST + QST	9.975%	5%		14.975%
Saskatchewan	GST + PST	6%	5%		11%
Yukon	GST		5%		5%

5. Use this address for your Company.

Home Renovation Ltd. 8540 River Road Surrey, BC V1M 3G5

2. 6. Customer Information: find an address in the given city/province and use it to invoice the customer.
3. You must show all your calculations in Calculations worksheet. Do Not use a calculator. All calculations must be completed using excel functions. Resize your Calculations worksheet to a legal-size paper format, landscape orientation. Apply proper formatting to your worksheets. Use cell references and make links through worksheets.
4. Create two charts for your compony on Charts worksheet.

• A chart to illustrate the number of renovation projects per province. This chart is for your company to keep a record of their customers in different provinces. Use the chart to answer: which province had the greatest number of projects?
• A chart to illustrate the total area of renovation per province. This chart is for your company to keep a record of their projects. Use the chart to answer: which province had the largest total renovated area completed by Home Renovation Ltd.?

5. Insert your student and course information in the Header of your workbook. Name the file HomeRenovation_LastFirstName (put your last and first name) and upload to the drop box on eLearn.