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 $ 3.45 per square foot and installation is $9.55 per square foot
• Natural stone tile floor costs $6.99 per square foot and installation is $7.21 per square foot
• Carpet flooring costs $ 2.99 per square foot and installation is $4.95 per square foot

3. Create an IF function to calculate delivery cost:

• 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. Create a VLookup Table using the listed data to calculate the sales and services tax. Remember that the customer purchases flooring materials and installation services. Each may involve different tax rate.

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%

Note: A 5% federal Goods and Services Tax (GST) is applicable to all transactions in Canada, with the exception of basic food items. In addition, most provinces apply a provincial sales tax.

5. Use this one for your Company.

Home Renovation Ltd.
8540 River Road
Richmond, BC
V7M 3R7

6. Customer Information: find an address in the given city/province and use it to invoice the customer.

7. You must show all your calculations in an excel workbook. You must demonstrate the use of a VLOOKUP and IF functions in your Excel Spreadsheets. Do Not use a calculator. Resize your worksheets to a legal-size paper format, landscape orientation. Organize your workbook so your data and calculations can easily be read. Apply proper formatting to your workbook.

Helpful Summary:

1. Create an invoice for your customer.
2. Include calculation sheet. Resize all worksheets to a legal-size paper format, landscape orientation.
3. Insert your student and course information in your Header.
4. Upload your completed Excel project workbook plus the Invoice (1 file) to eLearn.
5. You should have created 2 worksheets in your workbook, calculation sheet and invoice.
6. Work the problem in stages. Resolve one problem at a time.

Answer rating: 100% (QA)

Sample Invoive Sheet and Formulas used in Invoice Sheet In Invoice sheet Cell A1 Add text Home Renovation LtdIn Invoice sheet Cell A2 Add text 8540 River RoadIn Invoice sheet Cell A3 Add text Richmond ... View the full answer