Each model home has a specific base price which is determined by the number of levels in the home and the total square footage category of the home. The cost of the home includes the price of the base model chosen, the plot location, and the options included. The workbook, CE 2111 In Lab 5 Buckeye Builders Master.xlsx contains four worksheets as follows: Models: This worksheet outlines the specifications for the base model homes offered by the builder. It includes all the standard features for each given model. Base: This worksheet outlines the price in $/square foot by category for each home based on the variables just described. Homes with less than 1,500 square feet and 1 level are priced at $85 per square foot, and $105 per square foot for 2 levels. Homes with at least 1,500 square feet but less than 2,000 square feet are priced at $83 per square foot for 1 level and $102 per square foot for 2 levels. Units: This worksheet contains the location of the plot, the surcharge associated with the plot, the percentage of land allocation per plot, unit conversions used for calculations, and the size of the development area (750,000 square meters). Home buyers may be charged an additional amount depending on where on the development they would like their home to be built. Since lake plots are the most desirable, a 50% surcharge will be added to the total costs (base price of the home and the options total. Options explained below). So a house with a base price + options price of $100,000 on a lake plot will cost $150,000 with the surcharge. The surcharge for center plots is 25% and there is no surcharge for road locations. To determine the number of each plot type, you will need the allocation percentages - the percent of the development land that is in a specific category. For example according to the Units worksheet 15% of the land will be lake plots, 19% road plots etc. Customers: This worksheet describes the different options that can be added to the home, and the plot location of the home. Options include: Jacuzzi tub in the master bedroom, walkout basement, larger plot, bay window in dining room. Cells 04:G4 in the Customers worksheet contain the prices for each of the respective options, which do not vary by model. If a customer has ordered this option the value TRUE will be displayed in the corresponding cell (range DS:G12). So if a buyer chooses a Jacuzzi and bay window the corresponding cells in columns D and G will contain TRUE values and the costs will be $4,500 plus $3,900. There are also three different plot locations types within the development: Lake Plots (directly next to a small body of water within the area), Center Plots (within the development away from the outer most roads surrounding the development) and Road Plots (near the outlying boundaries of the division). There are a different numbers of plots and different costs associated with each plot type as listed on the Units worksheet. Now it is time to complete your analysis. 1. Based on the Total development area given in the Units worksheet, write an Excel formula in cell Units!D9 to calculate the number of Lake plots you will build per the allocation described in the Units worksheet. This formula should also work when you copy it down to determine the number of Center plots you will have and finally, the number of Road plots. (Use the ROUND function to round the number down to the nearest whole number.) F G H # Bedrm # Bathrm Base Price 3 2 4 3 3 2 3 1.5 4 3.5 B2 x fx Model B D E 1 Standard Features Model Square # Basement 2 Model Letter Footage Levels Type 3 Classic European 1,750 2 Crawlspace 4 New American B 2,370 2 Full 5 CSE Favorite C 1,220 1 Crawlspace 6 Country Charmer D 1,860 1 Partial 7 Contemporary E 3,150 2 Full 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Models Base Units Customers + A B C D E F G H w NP 0 # Levels Square Footage 2,000 2,500 82.00 $ 78.00 $ 99.00 $ 94.00 $ 3 1,500 83.00 $ 102.00 $ 3,500 72.00 11 $ 85.00 $ 21 $ 105.00 $ 3,000 75.00 $ 90.00 $ 4 90.00 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Models Base Units Customers + A B C D E 750,000 3.3 4047 48.4 4 # Plots Available 1 Unit Conversion and Plot Details 2 Total development area in square meters 3 Feet/meter 4 Square Meters/acre 5 Square Yards/acre 6 #plots/acre 7 Location Details Location Percent Surcharge Allocation 8 % of land 9 Lake 50% 15% 10 Center 25% 66% 11 Road 0% 19% 12 Total 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Models Base Units Customers Each model home has a specific base price which is determined by the number of levels in the home and the total square footage category of the home. The cost of the home includes the price of the base model chosen, the plot location, and the options included. The workbook, CE 2111 In Lab 5 Buckeye Builders Master.xlsx contains four worksheets as follows: Models: This worksheet outlines the specifications for the base model homes offered by the builder. It includes all the standard features for each given model. Base: This worksheet outlines the price in $/square foot by category for each home based on the variables just described. Homes with less than 1,500 square feet and 1 level are priced at $85 per square foot, and $105 per square foot for 2 levels. Homes with at least 1,500 square feet but less than 2,000 square feet are priced at $83 per square foot for 1 level and $102 per square foot for 2 levels. Units: This worksheet contains the location of the plot, the surcharge associated with the plot, the percentage of land allocation per plot, unit conversions used for calculations, and the size of the development area (750,000 square meters). Home buyers may be charged an additional amount depending on where on the development they would like their home to be built. Since lake plots are the most desirable, a 50% surcharge will be added to the total costs (base price of the home and the options total. Options explained below). So a house with a base price + options price of $100,000 on a lake plot will cost $150,000 with the surcharge. The surcharge for center plots is 25% and there is no surcharge for road locations. To determine the number of each plot type, you will need the allocation percentages - the percent of the development land that is in a specific category. For example according to the Units worksheet 15% of the land will be lake plots, 19% road plots etc. Customers: This worksheet describes the different options that can be added to the home, and the plot location of the home. Options include: Jacuzzi tub in the master bedroom, walkout basement, larger plot, bay window in dining room. Cells 04:G4 in the Customers worksheet contain the prices for each of the respective options, which do not vary by model. If a customer has ordered this option the value TRUE will be displayed in the corresponding cell (range DS:G12). So if a buyer chooses a Jacuzzi and bay window the corresponding cells in columns D and G will contain TRUE values and the costs will be $4,500 plus $3,900. There are also three different plot locations types within the development: Lake Plots (directly next to a small body of water within the area), Center Plots (within the development away from the outer most roads surrounding the development) and Road Plots (near the outlying boundaries of the division). There are a different numbers of plots and different costs associated with each plot type as listed on the Units worksheet. Now it is time to complete your analysis. 1. Based on the Total development area given in the Units worksheet, write an Excel formula in cell Units!D9 to calculate the number of Lake plots you will build per the allocation described in the Units worksheet. This formula should also work when you copy it down to determine the number of Center plots you will have and finally, the number of Road plots. (Use the ROUND function to round the number down to the nearest whole number.) F G H # Bedrm # Bathrm Base Price 3 2 4 3 3 2 3 1.5 4 3.5 B2 x fx Model B D E 1 Standard Features Model Square # Basement 2 Model Letter Footage Levels Type 3 Classic European 1,750 2 Crawlspace 4 New American B 2,370 2 Full 5 CSE Favorite C 1,220 1 Crawlspace 6 Country Charmer D 1,860 1 Partial 7 Contemporary E 3,150 2 Full 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Models Base Units Customers + A B C D E F G H w NP 0 # Levels Square Footage 2,000 2,500 82.00 $ 78.00 $ 99.00 $ 94.00 $ 3 1,500 83.00 $ 102.00 $ 3,500 72.00 11 $ 85.00 $ 21 $ 105.00 $ 3,000 75.00 $ 90.00 $ 4 90.00 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Models Base Units Customers + A B C D E 750,000 3.3 4047 48.4 4 # Plots Available 1 Unit Conversion and Plot Details 2 Total development area in square meters 3 Feet/meter 4 Square Meters/acre 5 Square Yards/acre 6 #plots/acre 7 Location Details Location Percent Surcharge Allocation 8 % of land 9 Lake 50% 15% 10 Center 25% 66% 11 Road 0% 19% 12 Total 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Models Base Units Customers