Write an Excel formula in cell Models!H3 to determine the base price for the Classic European...

      

Transcribed Image Text:

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. ($/SF for all combinations of stories) Square footage can be found in the Base worksheet. Model Classic European New American CSE Favorite Country Charmer Contemporary Model Square # Letter Footage Levels A B C Standard Features Basement Type Crawlspace D E 1,750 2 2,370 2 1,220- 1 1,860 1 3,150 2 Full Full Crawlspace Partial # # Bedrm Bathrm Base Price 3 2 3 4 3 3 4 2 1.5 3.5 # Levels 0 85.00 $ 1 $ 2 $ 105.00 $ 1,500 83.00 $ 102.00 $ Square Footage 2,000 82.00 $ 99.00 $ 2,500 78.00 $ 94.00 $ 3,000 75.00 $ 90.00 $ 3,500 72.00 90.00 Unit Conversion and Plot Details Total development area in square meters Feet/meter Square Meters/acre Square Yards/acre #plots/acre Lake Center Road Location Details Location Surcharge % 50% 25% 0% Percent Allocation of land 15% 66% 19% 750,000 3.3 4047 48.4 4 # Plots Available 111 489 141 Customers Cline White Walters Flint Jones Phillips 1 Stevens 2 Thomas 3 Jacuzzi Tub Walkout Larger Bay Options Base Model Location (Master) Basement. Plot Window Total Price E Road A с C B A D 10 D Center Center Lake Road Lake Center Road $4,500 TRUE TRUE TRUE FALSE TRUE FALSE FALSE TRUE $7,999 $11,000 TRUE TRUE FALSE TRUE FALSE FALSE FALSE TRUE TRUE FALSE TRUE FALSE TRUE FALSE FALSE TRUE $3,900 TRUE FALSE FALSE $ 4,500 FALSE $ 11,000. TRUE $ 27,399 FALSE $ TRUE $ 11,899 TRUE $19,400 $ 27,399 $ 15,500 Location Charges Total Price after Options Reduction 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.) 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. ($/SF for all combinations of stories) Square footage can be found in the Base worksheet. Model Classic European New American CSE Favorite Country Charmer Contemporary Model Square # Letter Footage Levels A B C Standard Features Basement Type Crawlspace D E 1,750 2 2,370 2 1,220- 1 1,860 1 3,150 2 Full Full Crawlspace Partial # # Bedrm Bathrm Base Price 3 2 3 4 3 3 4 2 1.5 3.5 # Levels 0 85.00 $ 1 $ 2 $ 105.00 $ 1,500 83.00 $ 102.00 $ Square Footage 2,000 82.00 $ 99.00 $ 2,500 78.00 $ 94.00 $ 3,000 75.00 $ 90.00 $ 3,500 72.00 90.00 Unit Conversion and Plot Details Total development area in square meters Feet/meter Square Meters/acre Square Yards/acre #plots/acre Lake Center Road Location Details Location Surcharge % 50% 25% 0% Percent Allocation of land 15% 66% 19% 750,000 3.3 4047 48.4 4 # Plots Available 111 489 141 Customers Cline White Walters Flint Jones Phillips 1 Stevens 2 Thomas 3 Jacuzzi Tub Walkout Larger Bay Options Base Model Location (Master) Basement. Plot Window Total Price E Road A с C B A D 10 D Center Center Lake Road Lake Center Road $4,500 TRUE TRUE TRUE FALSE TRUE FALSE FALSE TRUE $7,999 $11,000 TRUE TRUE FALSE TRUE FALSE FALSE FALSE TRUE TRUE FALSE TRUE FALSE TRUE FALSE FALSE TRUE $3,900 TRUE FALSE FALSE $ 4,500 FALSE $ 11,000. TRUE $ 27,399 FALSE $ TRUE $ 11,899 TRUE $19,400 $ 27,399 $ 15,500 Location Charges Total Price after Options Reduction 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.)

Answer rating: 100% (QA)

According to question it is a conditional type of question so we have to write conditiona... View the full answer