Question

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.
7. Good Luck

Answer 1

sol:

Sample Invoive Sheet and Formula's used in Invoice Sheet:

In "Invoice" sheet Cell A1 - Add text - "Home Renovation Ltd."
In "Invoice" sheet Cell A2 - Add text - "8540 River Road."
In "Invoice" sheet Cell A3 - Add text - "Richmond, BC"
In "Invoice" sheet Cell A4 - Add text - "V7M 3R7"
In "Invoice" sheet Cell A7 - Add text - "Bill To:"
In "Invoice" sheet Cell A8 - Add text - "University Of Manitoba Post Office"
In "Invoice" sheet Cell A9 - Add text - "111 – 66 Chancellors Cirle R3T 1E0"
In "Invoice" sheet Cell A10 - Add text - "Manitoba"
In "Invoice" sheet Cell D1 - Add text - "Invoice Details"
In "Invoice" sheet Cell D2 - Add text - "Date"
In "Invoice" sheet Cell D3 - Add text - "Invoice"
In "Invoice" sheet Cell D4 - Add text - "CustomerID"
In "Invoice" sheet Cell D5 - Add text - "Purchase Order #"
In "Invoice" sheet Cell D6 - Add text - "Payment Due By"
In "Invoice" sheet Cell D7 - Add text - "Ship To [] If different"

In "Invoice" sheet Cell A12 - Add text - "Description" and merge cells from A12,B12,C12,D12
In "Invoice" sheet Cell E12 - Add text - "Line Total"
In "Invoice" sheet Cell A13 - Add text - "Flooring materials costs"
In "Invoice" sheet Cell E13 - Use formula - ='Calculation Sheet'!B14.This gets the flooring cost from "Calculation Sheet" cell B14
In "Invoice" sheet Cell A14 - Add text - "Installation Costs"
In "Invoice" sheet Cell E14 - Use formula - ='Calculation Sheet'!B15.This gets the Installation cost from "Calculation Sheet" cell B15
In "Invoice" sheet Cell A15 - Add text - "Delivery Cost"
In "Invoice" sheet Cell E15 - Use formula - ='Calculation Sheet'!B19.This gets the Delivery cost from "Calculation Sheet" cell B19
In "Invoice" sheet Cell A16 - Add text - "Notice & Instructions"
In "Invoice" sheet Cell A17 - Add text - "Taxes for glooring rates and materials costs vary based on the Canadian province." and merge cells from A17,B18,C19,D20

In "Invoice" sheet Cell D16 - Add text - "SubTotal"
In "Invoice" sheet Cell E16 - Use formula - =SUM(E13:E15)
In "Invoice" sheet Cell D17 - Add text - "Provincial Sales Tax"
In "Invoice" sheet Cell E17 - Use formula - ='Calculation Sheet'!B17

In "Invoice" sheet Cell D18 - Add text - "Federal GST"
In "Invoice" sheet Cell E18 - Use formula - ='Calculation Sheet'!B18

In "Invoice" sheet Cell D19 - Add text - "Discount"
In "Invoice" sheet Cell E19 - Use formula - =0

In "Invoice" sheet Cell D20 - Add text - "Total"
In "Invoice" sheet Cell E20 - Use formula - =SUM(E16:E18) -E19

In "Invoice" sheet Cell A22 - Add text - "Make all checks payable to company name "Home Renovation Ltd""
In "Invoice" sheet Cell A23 - Add text - "Thank you for your business"
In "Invoice" sheet Cell A24 - Add text - "Should you have any queries with this invoice, please contact "Denning Boe" on X-XXX-XXX-XXXX"

Sample "Calculation Sheet and formula Used"

In "Calculation Sheet" cell A1 - Enter text - "Customer Details".Merge cells a1,B1,C1,D1
In "Calculation Sheet" cell E1 - Enter text - "Renovation Details".Merge cells e1,f1,g1,h1,i1
In "Calculation Sheet" cell J1 - Enter text - "Flooring Costs Per Sqaure Feet".Merge cells j1,k1,l1
In "Calculation Sheet" cell M1 - Enter text - "Installation Costs".Merge cells m1,n1,o1

In "Calculation Sheet" cell A2 - Enter text - "Customer Name"
In "Calculation Sheet" cell A3 - Enter any customer name
In "Calculation Sheet" cell B2 - Enter text - "Customer Province"
In "Calculation Sheet" cell B3 - Enter any customer province

In "Calculation Sheet" cell C2 - Enter text - "Customer Address Line"
In "Calculation Sheet" cell C3 - Enter any customer address Line

In "Calculation Sheet" cell D2 - Enter text - "Customer Postal Code"
In "Calculation Sheet" cell D3 - Enter any Customer Postal Code
In "Calculation Sheet" cell E2 - Enter text - "Total Renovation Area"
In "Calculation Sheet" cell E3 - Enter renovation Area value 205
In "Calculation Sheet" cell F2 - Enter text - "Kitchen Area"
In "Calculation Sheet" cell F3 - Enter formula - =$E$3*0.15
In "Calculation Sheet" cell G2 - Enter text - "Bathroom Area"
In "Calculation Sheet" cell G3 - Enter formula - =$E$3*0.1
In "Calculation Sheet" cell H2 - Enter text - "Living & Dining room Area"
In "Calculation Sheet" cell H3 - Enter formula - =$E$3*0.35
In "Calculation Sheet" cell I2 - Enter text - "Bedrooms"
In "Calculation Sheet" cell I3 - Enter formula - =$E$3*0.4
In "Calculation Sheet" cell J2 - Enter text - "Engineering wood flooring"
In "Calculation Sheet" cell J3 - Enter Engineering wood flooring value - 3.45
In "Calculation Sheet" cell K2 - Enter text - "Natural Stone Tile"
In "Calculation Sheet" cell K3 - Enter Natural Stone Tile value 6.99
In "Calculation Sheet" cell L2 - Enter text - "Carpet Flooring"
In "Calculation Sheet" cell L3 - Enter Carpet Flooring value 2.99
In "Calculation Sheet" cell M2 - Enter text - "Engineering wood flooring"
In "Calculation Sheet" cell M3 - Enter Engineering wood installation value - 9.55
In "Calculation Sheet" cell N2 - Enter text - "Natural Stone Tile"
In "Calculation Sheet" cell N3 - Enter Natural Stone Tile installation value 7.21
In "Calculation Sheet" cell O2 - Enter text - "Carpet Flooring"
In "Calculation Sheet" cell O3 - Enter Carpet Flooring installation value 4.95

In "Calculation Sheet" cell A5 - Enter Text - "Description"
In "Calculation Sheet" cell A6 - Enter Text - "Kitchen Flooring"
In "Calculation Sheet" cell A7 - Enter Text - "Bathroom Flooring"
In "Calculation Sheet" cell A8 - Enter Text - "Living & Dining Area Flooring"
In "Calculation Sheet" cell A9 - Enter Text - "Bedroom Flooring"
In "Calculation Sheet" cell A10 - Enter Text - "Kitchen Installation"
In "Calculation Sheet" cell A11 - Enter Text - "Bathroom Installation"
In "Calculation Sheet" cell A12 - Enter Text - "Living & Installation"
In "Calculation Sheet" cell A13 - Enter Text - "Bedroom Installation"
In "Calculation Sheet" cell A14 - Enter Text - "Total Flooring Cost"
In "Calculation Sheet" cell A15 - Enter Text - "Total Installation Cost"
In "Calculation Sheet" cell A16 - Enter Text - "Total Cost {Including Installation & Service}"
In "Calculation Sheet" cell A17 - Enter Text - "Provintial Tax"
In "Calculation Sheet" cell A18 - Enter Text - "Federal Tax"
In "Calculation Sheet" cell A19 - Enter Text - "Delivery Costs"
In "Calculation Sheet" cell A20 - Enter Text - "Final Cost"

In "Calculation Sheet" cell B5 - Enter Text - "Totals"
In "Calculation Sheet" cell B6 - Enter Formula - =F3*J3
In "Calculation Sheet" cell B7 - Enter Formula - =G3*K3
In "Calculation Sheet" cell B8 - Enter Formula - =H3*J3
In "Calculation Sheet" cell B9 - Enter Formula - =I3*L3
In "Calculation Sheet" cell B10 - Enter Formula - =F3*M3
In "Calculation Sheet" cell B11 - Enter Formula - =G3*K3
In "Calculation Sheet" cell B12 - Enter Formula - =H3*M3
In "Calculation Sheet" cell B13 - Enter Formula - =I3*O3
In "Calculation Sheet" cell B14 - Enter Formula - =SUM(B6:B9)
In "Calculation Sheet" cell B15 - Enter Formula - =SUM(B10:B13)
In "Calculation Sheet" cell B16 - Enter Formula - =SUM(B14:B15)
In "Calculation Sheet" cell B17 - Enter Formula - =VLOOKUP(TRIM($B$3),ServiceSalesTax!A1:F14,6,FALSE) * B16
In "Calculation Sheet" cell B18 - Enter Formula - =B16*0.05
In "Calculation Sheet" cell B19 - Enter Formula - =IF(B14 > 5000,300,IF(B14 < 3500,0,IF(B14> 3500,200,0)))
In "Calculation Sheet" cell B20 - Enter Formula - =SUM(B16:B19)

Sample "ServiceSalesTax" sheet

***Please Upvote......Its really usefull to us...If any querry comment below...I will resolve ASAP....Thank You....