BCPT 123 Home Renovation Excel Project No.2

Youareassigned to help a customer who is interested inhomeflooring renovation.The customer has requested a quote for his renovation project. Make aninvoiceandnotice thatthe salesand services tax rates aredifferent in eachCanadianprovince.See the excel file titledArea-Cityto find out the area of flooring, and the city and province that the home is located.

PLEASE READ the instructions carefully:

1. Assume that

Area of kitchen is 15% of the totalrenovationarea

Area of bathrooms is10% of the totalrenovationarea

Area of living room and dining room is35% of the totalrenovationarea

Area of bedrooms is40% of the totalrenovationarea

2.Kitchen, living room and dining roomflooring would beengineered wood flooring. Bathrooms wouldbenatural stonetile floor and bedrooms wouldbecarpet. 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. make an IF function to calculate delivery cost:

Delivery is free of charge on the order of flooring materialsbelow$3500

A $200 flat delivery rate applies on the order of flooring materialsabove$3500and below $5000.

A $300 flatdelivery rate applies on the order of flooring materialsabove$5000.

4. make aVLookupTable using the listed data to calculate thesales 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: A5% federal Goods and Services Tax (GST)is applicable toall transactions in Canada, with the exception of basic food items. In addition, most provinces apply aprovincial sales tax.

5.Use this one for your Company.

Home RenovationLtd.

8540 River Road

Richmond, BC

V7M 3R7

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

7. You must showall your calculationsin an excel workbook. Youmustdemonstrate the use of a VLOOKUPand IFfunctionsin your Excel Spreadsheets.Do Notuse acalculator.Resize your worksheets to alegal-sizepaper format,landscapeorientation.Organize your workbook so your data and calculations can easily be read.Applyproperformattingtoyour workbook.

HelpfulSummary:

1.make aninvoicefor your customer.

2.Include calculation sheet.Resizeallworksheets to a legal-size paper format, landscape orientation.

3.Insert your student and course information in your Header.

4.Upload your completed Excel project workbookplus theInvoice (1 file) toeLearn.

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.

Student ID Renovation Area (square meters City/Province 100142438 300 Abbotsford/BC 100142133 315 Bay Bulls/NL 100081278 195 Bedford/QC 100137271 430 Berwick/NS 100146215 180 Botwood/NL 100153094 430 Brandon/MB 100141829 235 Brant/ON 100144314 330 Brockville/ON 100136863 285 Brooks/AB 100132665 370 Burin/NL 100130905 265 Burnaby/BC 100142382 295 Calgary/AB 100130033 270 Campbell River/BC 100153721 195 Campbellton/NB 100147808 315 Cold lake/AB 100144124 330 Colliners/NL 100134922 310 Conception Bay South/NL 100132274 370 Cornwall/ON 100139658 470 Dauphin/MB 100145864 410 Delson/QC 100106600 265 Delta/BC 100110887 320 Dieppe/NB 100142852 470 Dorval/QC 100109817 300 Edmonton/AB 100137624 310 Elliot Lake/ON 100087029 205 Fermont/QC 100145335 410 Fredericton/NB 100137697 295 Gander/NL 100153117 430 Gaspe/QC 100149274 270 Grande/Ab 100139069 205 Greenwood/BC 100141347 470 Guelph/ON 100145044 300 Hamilton/ON 100149245 295 Happy Valley-Goose Bay/ NL 100146365 235 Hudson/QC 100132050 370 Kamloops/BC 100131254 470 Kelowna/BC 100123837 180 Kenora/ON 100143549 195 Kingston/PE 100147482 295 Kitchener/ON 983001569 220 Lacombe/AB 100131163 220 Markham/On 100145753 330 Merrit/BC 100116647 370 Mirabel/QC 100147788 285 Mississauga/ON 100143496 320 Moncton/NB 100149862 265 Montreal/QC 100129803 205 Mulgrave/NS 100146246 410 Nanaimo/BC 100139440 235 New Westminester/BC 100142703 430 Niagara Falls/ ON 100144606 270 North Bay/ON 100146659 220 North Vancouver/ BC 100137991 220 Northport/PE 100140688 410 Oshawa/ON 100131271 320 Ottawa/ON 100139697 235 Oxford/NS 100138421 285 Paradise/NL 100129510 310 Penticton/BC 100126509 470 Pictou/NS 100146177 285 Pitt Meadows/BC 100138759 205 Port Colborne/ON 100139862 180 Powell River/BC 100139957 320 Prevost/QC 100142620 295 Prince George/BC 100140690 205 Princeville/QC 100125501 205 Red Deer/ AB 100148365 220 Repentigny/QC 100139088 315 Revelstoke/BC 100140943 265 Richmond Hill/ON 100139566 205 Richmond/BC 100141893 300 Richmond/QC 100143560 270 Roberval/QC 100138178 315 Saint-Lazare/QC 100138403 330 Saint-Pie/QC 100140509 285 Scotstown/QC 100143487 410 Shelburne/NS 100124162 370 Shelburne/NS 100144485 265 Sherbrook/PE 100010300 180 Sherbrooke/QC 100142571 205 St. Louis/PE 100113024 310 Sutton/QC 100124154 205 Terrebonne/QC 100138183 235 Thompson/MB 100141416 195 TorontorOf