Lab. YOU ARE ADDING ONTO TO YOUR EXISTING HOME AND ARE TRYING TO GET AN ESTIMATE OF WHAT IT WILL COST YOU TO BUILD YOUR KITCHEN, EXCLUDING APPLIANCES. YOU HAVE SEVERAL DIFFERENT OPTIONS A, B AND C-DEPENDING ON HOW MUCH SPACE YOU ALLOCATE. YOU HAVE COMPILED DATA ON THE COST OF FLOORING AND CABINETS AS FOLLOWS KITCHEN PROBLEM 1 Unit Conversion 2 yards (SF/SY) 123456 PENS 3 plywood conv! square feet to square 2 Item 7 square feet per sheet of 3 3/4 inch plywood 4 vinyl flooring 8 9 10 11 12 prices! factor B Misc. Pricing Units sheet SY B Price 9 32 $25.75 $37.50 D FT $/FT 1 2 Option 3 A 4 B 5 C 6 7 Outputs 8 Option 9 A 10 B 11 C 12 A calc! $ FT E Kitchen Dimensions & Pricing cabinet length Kitchen Kitchen (ft) length (ft) 15 $ $ $ F E cabinetry pricing Price/FT 0 B 10 20 35 50 10 20 15 825 725 700 675 650 trim pricing 0 10 3.00 $ 2.85 $ G 20 18 subfloor trim vinyl cabinetry total cost 241.41 $1,250.00 $ 196.00 $ 7,250.00 $ 482.81 $2,500.00 $275.00 $ 14,000.00 $ 362.11 $1,875.00 $240.80 $ 10,875.00 $ width (ft) perimeter(ft) 20 30 25 50 2.80 $ H 100 70 100 86 2.75 F area(sf) 300 600 450 8,937.41 17,257.81 13,352.91 Page 1 of 3 X Aa Po A A. 2 == | AaBbCcDdE AABBC firstlevel_bull... labtitle AaBbCcDdEe 0 AaBbCcDi Normal | AaBbCcDdEe secondlevel..... List Paragraph Book Title Kitchen Estimating Problem in the file kitchen.xlsx: Floor: The kitchen floor will consist of a subfloor and vinyl floor cover and trim. The subfloor is composed of %4 inch plywood sold in sheets of 32 square feet (SF) at a price of $25.75 per sheet. The vinyl floor covering you have chosen is $37.50 per square yard (SY) The baseboard trim is used around the entire perimeter of the kitchen. Its price varies with the amount of trim used, as indicated by the table on the prices worksheet. Pricing for the cabinetry also varies with the number of linear feet of cabinetry required. An appropriate table is also on the prices worksheet. For estimating purposes you have assumed that the contractor will only charge you for the exact amount used- so you do not have to assume any additional costs or round up to the nearest unit. Cabinets: The cabinets pricing varies based on the number of linear feet (FT) of cabinet space as listed on the price worksheet. 1. In cell calc!E3, write an Excel formula, which can be copied down the column, to determine the perimeter of the corresponding kitchen. 2. In cell calc!F3, write an Excel formula, which can be copied down the column, to determine the area of the corresponding kitchen. 3. In cell calc!B9, write an Excel formula, which can be copied down the column, to determine the cost of the subfloor for the corresponding kitchen. (Hint: components of the subfloor are described in the problem description) 4. In cell calc!C9, write an Excel formula, which can be copied down the column, to determine the cost of the vinyl floor covering for the corresponding kitchen. This formula should work even if the kitchen dimensions are later updated. 5. In cell calc!D9, write an Excel formula, which can be copied down the column, to determine the cost of the floor trim for the corresponding kitchen. 6. In cell calc!E9, write an Excel formula, which can be copied down the column, to determine the cost of the cabinetry for the corresponding kitchen-remember the cost per FT varies based on the total FT of cabinets. Again, this formula should work even if the kitchen dimensions are later updated. 7. In cell calc!F9, write an Excel formula, which can be copied down the column, to determine the total cost of the kitchen for this option. 8. You have decided to choose the option Ckitchen and now want to explore some financing scenarios. A table has been setup on worksheet loan for this purpose. Insert the appropriate data inputs (using cell referencing to kitchen option C as needed) and write the appropriate Excel formulas needed to complete. Highlight the answer to each loan option in yellow. Loan 1 - Assume that you are making a down payment of 10% of the cost of the kitchen and borrowing the rest from the bank at 4.25% annual interest rate compounded monthly. The loan will be entirely paid off in 3 years. Determine your monthly payment. AaBbCcDdEe 11 =x A A Aa Po x A A EVE a AaBbCcDdE firstlevel_bull... AABBC AaBbCcDdEe labtitle Normal o AaBbCcDi AaBbCcDdEe List Paragraph secondlevel Loan 2 - The bank has offered a loan for the entire value of the kitchen that you can pay off over a 3 year period with quarterly payments of $1500. What is the annual interest rate being charged for this loan? Loan 3 - The contracting firm also has financing terms available. They will lend you 80% of the cost of the kitchen with a 3.5% annual interest rate and a $500 balloon payment due at the end of the loan. You will make payments of $300/month (interest is compounded monthly). How many years will it take to pay off this loan? Which do you think is the best loan and why? Place answer below data on loan worksheet. Save and close the workbook. Then Upload and save it to SIMnet under Resources Prelab 6 Kitchen and then Submit it. You will get 3 attempts to submit your file. Be sure to use only the file you downloaded from SIMnet and made changes to, that you submit. You must submit your file to SIMnet to get credit for Inlab. It is your responsibility to check that the correct completed file has been submitted. You can click on the submitted file and open it to check that the correct file was submitted. | AaBbCcDdEn Book Title A Home E3 1 2 3 A 4 23+56OTOOING 5 C 7 8 6 7 B Option 9 A 0 B 1 C 9 20 21 22 23 24 25 26 27 28 29 10 1 2 13 14 15 16 17 18 19 -0 SAWNO 1 Paste -2 3 -4 Option -5 BC A Outputs Insert Draw Page Layout xe + Arial BIU x fx B cabinet length (ft) subfloor 10 20 1812 15 Kitchen length (ft) Kitchen Dimensions & Pricing Kitchen width (ft) perimeter(ft) 20 15 20 18 D vinyl trim 10 Formulas 30 25 A^ E A A Data Review F area(sf) cabinetry total cost G View H (0 Wrap Text Merge & Center K General $ % L H18 7 8 9 10 11 12 13 14 15 16 17 18 19 20 1 2 Item 3 3/4 inch plywood 4 vinyl flooring 5 6 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 Paste 43 44 45 46 47 48 49 50 LA A X calc BIU fx B Misc. Pricing Units sheet square yard (SY) prices C Price $25.75 $37.50 conv A D FT $/FT FT === = loan E F cabinetry pricing 0 10 20 35 50 Price/FT trim pricing 0 10 $ 3.00 $ 2.85 $ + 825 725 700 675 650 G 50 2.80 $ H 100 2.75 Merge & Center J $ % 9 K L 5.00 9.00 M N Conditional Format Formatting as Table O P Home Insert Draw Page Layout D29 2 3 4 1 Unit Conversion 5 6 7 8 9 10 11 12 13 14 15 Paste 16 17 18 19 20 21 22 23 24 25 26 27 A Arial BIU fx square feet to square yards. (SF/SY) square feet per sheet of plywood factor B 10 Formulas Data Review View A A 9 32 C ll D |||||||| E F ab Wrap Text HI G Merge & Center H I General $ % 9 J K .00 .00 -0 L Cond Form M Home Insert Draw Page Layout 2. Paste A13 A 1 Kitchen Option C 2 ANNUAL RATE% 3 DURATION (YRS) 4 PERIODS/YR 5 PAYMENT 6 7 LOAN VALUE 11 12 13 14 15 16 17 18 X 19 20 21 22 23 24 25 26 27 28 29 30 31 32 8 BALOON PAYMENT 9 10 33 34 Arial DOWNPAYMENT% BIU fx Loan1 B V V Loan2 10 Formulas Data Review A ' ' A D Loan3 E F View G a H Wrap Text Merge & Center I J General $ % 9 V K L .0 M V .00 Conditional Format Formatting as Table N O P C St