{"id":52914,"date":"2022-04-16T04:02:42","date_gmt":"2022-04-16T04:02:42","guid":{"rendered":"https:\/\/assignmenttutors.net\/blog\/excel-2019-in-practice-ch-9-independent-project-9-5\/"},"modified":"2022-04-16T04:02:42","modified_gmt":"2022-04-16T04:02:42","slug":"excel-2019-in-practice-ch-9-independent-project-9-5","status":"publish","type":"post","link":"https:\/\/assignmenttutors.net\/blog\/excel-2019-in-practice-ch-9-independent-project-9-5\/","title":{"rendered":"Excel 2019 in practice – ch 9 independent project 9-5"},"content":{"rendered":"
\n

\u00a0These instructions are compatible with both Microsoft Windows and Mac operating systems.<\/p>\n

At Placer Hills Real Estate, commission is split with other agencies based on price groups. You create a one-variable data table to display results for various split rates. Additionally, you create scenarios for selling price and commission and create a histogram about sales.<\/p>\n

[Student Learning Outcomes 9.1, 9.3, 9.4, 9.6]<\/strong><\/p>\n

File Needed: PlacerHills-09.xlsx<\/strong><\/em> (Available from the<\/em> Start File link.)<\/em><\/p>\n

Completed Project File Name: [your name]-PlacerHills-09.xlsx<\/strong><\/em><\/p>\n

Skills Covered in This Project<\/strong><\/em><\/h3>\n
    \n
  • Build a one-variable data table.<\/li>\n
  • Use Solver<\/em>.<\/li>\n
  • Create and manage scenarios.<\/li>\n
  • Create a histogram with a chart.<\/li>\n<\/ul>\n
      \n
    1. Open the PlacerHills-09<\/strong><\/em> start file. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor<\/li>\n
    2. Enable the content security warning.<\/li>\n
    3. Review formulas.\n
        \n
      1. Select cell C14<\/strong> on the Calculator<\/strong> worksheet. The total commission is calculated by multiplying the selling price by the commission rate.<\/li>\n
      2. Select cell C15<\/strong>. The IFS<\/em> function checks the selling price (C12) to determine the split percentage (column D) based on the price group.<\/li>\n
      3. Select cell C16<\/strong>. The IFS<\/em> function checks the selling price (C12) to determine the administrative fee percentage (column E) and multiplies that percentage by the value in cell C15 to calculate the fee in dollars.<\/li>\n
      4. Select cell C17<\/strong>. The net commission is calculated by subtracting the fees from the PHRE amount.<\/li>\n<\/ol>\n<\/li>\n
      5. Build one-variable data tables.\n
          \n
        1. Select cell C20<\/strong> and create a reference to cell C15<\/strong>.<\/li>\n
        2. Select cell D20<\/strong> and create a reference to cell C17<\/strong>. Both formulas depend on cell C12,<\/strong> the one variable.<\/li>\n
        3. Use cell D8<\/strong> as the column input for the data table (Figure 9-87). (You can use any percentage from column D because its value is replaced by the proposed rates in column B in the data table.)\"Images\"Figure 9-87 Data table setup for commission rates<\/strong><\/li>\n
        4. Decrease the decimal two times for all values in the data table.<\/li>\n<\/ol>\n<\/li>\n
        5. Name cell ranges.\n
            \n
          1. Click the Price Solver<\/strong> worksheet tab.<\/li>\n
          2. Click cell C12<\/strong> and name the range Selling_Price<\/strong>. You cannot use spaces in a range name.<\/li>\n
          3. Name cell C14<\/strong> as Total_Commission<\/strong> and cell C17<\/strong> as PHRE_Commission<\/strong>.<\/li>\n<\/ol>\n<\/li>\n
          4. Install the Solver Add-in<\/em> and the Analysis ToolPak<\/em>.<\/li>\n
          5. Use Solver<\/em> to find target PHRE net commission amounts.\n
              \n
            1. Build a Solver<\/em> problem with cell C17<\/strong> as the objective cell. For the first solution, set the objective to a value of 50000<\/strong> by changing cell C12<\/strong>. Use the GRG Nonlinear<\/strong> solving method. Save the results as a scenario named $50,000<\/strong>.<\/li>\n
            2. Restore the original values and run another Solver<\/em> problem to find a selling price for a PHRE commission of 75000. Save these results as a scenario named $75,000<\/strong>.<\/li>\n
            3. Restore the original values and run a third Solver<\/em> problem to find a selling price for a net commission of $100,000. Save these results as a scenario and restore the original values.<\/li>\n<\/ol>\n<\/li>\n
            4. Manage scenarios.\n
                \n
              1. Show the $50,000<\/strong> scenario in the worksheet.<\/li>\n
              2. Create a Scenario summary<\/strong> report for cells C12<\/strong>, C14<\/strong>, and C17<\/strong>.<\/li>\n<\/ol>\n<\/li>\n
              3. Create a histogram for recent sales.\n
                  \n
                1. Click the Sales Forecast<\/strong> sheet tab and select cell G13<\/strong>.<\/li>\n
                2. Create a bin range of 10 values starting at 350,000 with intervals of 50,000, ending at 800,000 in cell G22.<\/li>\n
                3. Use the Analysis ToolPak<\/em> to create a histogram for cells E5:E26<\/strong>. Do not check the Labels<\/em> box and select the bin range in your worksheet.<\/li>\n
                4. Select cell H3<\/strong> for the Output Range<\/em> and include a chart.<\/li>\n
                5. Position and size the chart from cell K3<\/strong> to cell V19<\/strong>.<\/li>\n
                6. Edit the horizontal axis title to display Selling Price<\/strong> and edit the vertical axis title to Number of Sales<\/strong>.<\/li>\n
                7. Edit the chart title to display Sales by Price Group<\/strong>.<\/li>\n
                8. Select and delete the legend.<\/li>\n
                9. Delete cells G13:G22<\/strong> (Figure 9-88).\"Images\"Figure 9-88 Histogram and chart for sales data<\/strong><\/li>\n<\/ol>\n<\/li>\n
                10. Save and close the workbook (Figure 9-89).<\/li>\n
                11. Upload and save your project file.<\/li>\n
                12. Submit project for grading.<\/li>\n<\/ol>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

                  \u00a0These instructions are compatible with both Microsoft Windows and Mac operating systems. At Placer Hills Real Estate, commission is split with other agencies based on price groups. You create a one-variable data table to display results for various split rates. Additionally, you create scenarios for selling price and commission and create a histogram about sales. […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"class_list":["post-52914","post","type-post","status-publish","format-standard","hentry","category-education"],"_links":{"self":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/posts\/52914","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/comments?post=52914"}],"version-history":[{"count":0,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/posts\/52914\/revisions"}],"wp:attachment":[{"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/media?parent=52914"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/categories?post=52914"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/assignmenttutors.net\/blog\/wp-json\/wp\/v2\/tags?post=52914"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}