BCPT 123 Home Renovation - Invoice

BCPT 123 Project: Home Renovation

BCPT 123 Project: Home Renovation

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 customer’s 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.

a. 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

b. 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

c. 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.

d. 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%