v File Home Insert Draw Page Layout Formulas Data Review View Help Calibri - 11 === General Conditional Formatting BIU A A E $ %) 2 Format as Table Paste a. Ar Cell Styles Clipboard Font Alignment Number Styles o Cells Editing -12 A B D G H # Levels 0 1,500 1 $ 85.00 $ 83.00 $ 21 $ 105.00 $ 102.00 $ E Square Footage 2,000 2,500 82.00 $ 78.00 $ 99.00 $ 94.00 $ 3,000 75.00 $ 90.00 $ 3,500 72.00 90.00 File Home Insert Draw Page Layout Formulas Data Review View 12 Calibri BIU = Number $ % 9 Paste A A A Conditional FC Format as Tab! Cell Styles Clipboard Font Alignment Number Styles D9 fx D E F 750,000 3.3 4047 48.4 4 B C 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 # Plots Available 17 18 19 20 Review Help Calibri 11 abe General BIU == A A E Conditional Formatting Format as Table Cell Styles Paste $ %) Cells Clipboard Font 27 Alignment Number I Styles M15 fo F G H J K M N 1 2 Options Total Base Price Location Charges Price after Options Reduction Total Larger Bay Plot Window 4 $11,000 $3,900 5 TRUE TRUE 6 TRUE FALSE 7 FALSE FALSE 8 TRUE FALSE 9 TRUE TRUE 10 FALSE FALSE 11 FALSE TRUE 12 TRUE TRUE 13 14 15 16 17 18 19 20 21 Models Base Units Customers Ready Aav Po AaB+CD AaBbCcDa . AaBbcc AaBbc. (a) AaBt AABE 1 Caption Emphasis firstlevel- Heading 1 T Heading 3 labtitle Paragraph Styles I 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:54 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 05:512). 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.) 2. Write an Excel formula in cell Models!H3 to determine the base price for the Classic European Mailings Review View Help EEA 1 AaBDCD AaBbCcDa . AaBbcc AaBbc. (a) AaBt AABB A 1 Caption Emphasis firstlevel - Heading 1 T Heading 3 labtitle Paragraph Styles 2. Write an Excel formula in cell Models!H3 to determine the base price for the Classic European home. This formula should also work when you copy it down to determine the base price for the New American Home, CSE Favorite, etc. Recall that the base price for each model depends on the number of levels and total square footage of the home. (S/SF for all combinations of stories) Square footage can be found in the Base worksheet. 3. Write an Excel formula in cell Customers!H5 to be copied down to determine the total value of the options purchased for the first customer's sale. When copied down, the formula will determine the total value of options for each corresponding customer. 4. Write an Excel formula in cell Customers!15 to be copied down to determine the Base price for the specific model home the first customer has purchased. When copied down, the formula will determine the total value of each home for the corresponding customer. (Do not use an IF function for this formula.) 5. Write an Excel formula in cell Customers!J5 to determine the location surcharge (not total cost), if applicable, for the home purchased by the first customer. Remember that the surcharge percentage is applied to the home price including options. Write the formula so that it can be copied down the column to determine the surcharge for each corresponding customer. (Do not use an IF function for this formula.) I 6. Write an Excel formula in cell Customers!K5 to be copied down to determine the total price of the home that the first person will purchase. Write the formula so that it can be copied down the column to determine the total home price for each corresponding customer. This should include options total, the base price and location charges. _Master_-SU2O - Word Search Lailings Review View Help EEEE 21 AaBDCD AaBbCcDa . AaBbcc AaBbc. (a) AaBt AABB AB 1 Caption Emphasis firstlevel. Heading 1 T Heading 3 labtitle INC Paragraph Styles 2 6. Write an Excel formula in cell Customers!KS to be copied down to determine the total price of the home that the first person will purchase. Write the formula so that it can be copied down the column to determine the total home price for each corresponding customer. This should include options total, the base price and location charges. 7. In order to increase the number of customers who are purchasing the various options (Jacuzzi, basement walkout, larger plot, bay window in dining room), you have decided to offer an options package special for a limited time- Customers who purchase all of the options listed will get the highest priced option for free. Currently the larger plot is the most expensive option at $11,000 but keep in mind that prices are always subject to change. Write an Excel formula for cell Customers!L5, which can be copied down the column, to calculate the new total price of each home, taking into consideration any discounts for the options package if applicable. Submissions SE 2111 In Lab 5 Buckeye Builders Master.xlsx v File Home Insert Draw Page Layout Formulas Data Review View Help Calibri - 11 === General Conditional Formatting BIU A A E $ %) 2 Format as Table Paste a. Ar Cell Styles Clipboard Font Alignment Number Styles o Cells Editing -12 A B D G H # Levels 0 1,500 1 $ 85.00 $ 83.00 $ 21 $ 105.00 $ 102.00 $ E Square Footage 2,000 2,500 82.00 $ 78.00 $ 99.00 $ 94.00 $ 3,000 75.00 $ 90.00 $ 3,500 72.00 90.00 File Home Insert Draw Page Layout Formulas Data Review View 12 Calibri BIU = Number $ % 9 Paste A A A Conditional FC Format as Tab! Cell Styles Clipboard Font Alignment Number Styles D9 fx D E F 750,000 3.3 4047 48.4 4 B C 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 # Plots Available 17 18 19 20 Review Help Calibri 11 abe General BIU == A A E Conditional Formatting Format as Table Cell Styles Paste $ %) Cells Clipboard Font 27 Alignment Number I Styles M15 fo F G H J K M N 1 2 Options Total Base Price Location Charges Price after Options Reduction Total Larger Bay Plot Window 4 $11,000 $3,900 5 TRUE TRUE 6 TRUE FALSE 7 FALSE FALSE 8 TRUE FALSE 9 TRUE TRUE 10 FALSE FALSE 11 FALSE TRUE 12 TRUE TRUE 13 14 15 16 17 18 19 20 21 Models Base Units Customers Ready Aav Po AaB+CD AaBbCcDa . AaBbcc AaBbc. (a) AaBt AABE 1 Caption Emphasis firstlevel- Heading 1 T Heading 3 labtitle Paragraph Styles I 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:54 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 05:512). 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.) 2. Write an Excel formula in cell Models!H3 to determine the base price for the Classic European Mailings Review View Help EEA 1 AaBDCD AaBbCcDa . AaBbcc AaBbc. (a) AaBt AABB A 1 Caption Emphasis firstlevel - Heading 1 T Heading 3 labtitle Paragraph Styles 2. Write an Excel formula in cell Models!H3 to determine the base price for the Classic European home. This formula should also work when you copy it down to determine the base price for the New American Home, CSE Favorite, etc. Recall that the base price for each model depends on the number of levels and total square footage of the home. (S/SF for all combinations of stories) Square footage can be found in the Base worksheet. 3. Write an Excel formula in cell Customers!H5 to be copied down to determine the total value of the options purchased for the first customer's sale. When copied down, the formula will determine the total value of options for each corresponding customer. 4. Write an Excel formula in cell Customers!15 to be copied down to determine the Base price for the specific model home the first customer has purchased. When copied down, the formula will determine the total value of each home for the corresponding customer. (Do not use an IF function for this formula.) 5. Write an Excel formula in cell Customers!J5 to determine the location surcharge (not total cost), if applicable, for the home purchased by the first customer. Remember that the surcharge percentage is applied to the home price including options. Write the formula so that it can be copied down the column to determine the surcharge for each corresponding customer. (Do not use an IF function for this formula.) I 6. Write an Excel formula in cell Customers!K5 to be copied down to determine the total price of the home that the first person will purchase. Write the formula so that it can be copied down the column to determine the total home price for each corresponding customer. This should include options total, the base price and location charges. _Master_-SU2O - Word Search Lailings Review View Help EEEE 21 AaBDCD AaBbCcDa . AaBbcc AaBbc. (a) AaBt AABB AB 1 Caption Emphasis firstlevel. Heading 1 T Heading 3 labtitle INC Paragraph Styles 2 6. Write an Excel formula in cell Customers!KS to be copied down to determine the total price of the home that the first person will purchase. Write the formula so that it can be copied down the column to determine the total home price for each corresponding customer. This should include options total, the base price and location charges. 7. In order to increase the number of customers who are purchasing the various options (Jacuzzi, basement walkout, larger plot, bay window in dining room), you have decided to offer an options package special for a limited time- Customers who purchase all of the options listed will get the highest priced option for free. Currently the larger plot is the most expensive option at $11,000 but keep in mind that prices are always subject to change. Write an Excel formula for cell Customers!L5, which can be copied down the column, to calculate the new total price of each home, taking into consideration any discounts for the options package if applicable. Submissions SE 2111 In Lab 5 Buckeye Builders Master.xlsx