+1 (315) 557-6473 

Prescriptive analytics homework solution

In this solution, we have used prescriptive analytics to check the costs and incomes that arise from investing in the real estate sector in New York. 

Analyzing the real estate sector in New York

Here, we have analyzed all the factors that might affect the real estate industry including, the number of employees in the city of New York and constraints as well.


Use the Optimization Model to Analyze NYC Real Estate


Executive Summary

We'll use Excel's "Solver" to find the best solution for calculating the maximum benefit of the New York real estate business over the next eight quarters. Benefit, as we all know, is the difference between sales income and costs. To measure profit in this situation, we must consider two dimensions: specific sales income over the next eight quarters and the expense of the office over the next eight quarters. To begin, rent, equipment costs, and employee wages should all be factored into the cost of opening an office. Second, the real estate company's sales income is quite confusing. To measure the company's more accurate profit for the next two years, we must also weigh sales fees, market penetration, and the discount rate.

Decision Variables

                  The value of the decision variable affects the objective function in optimal analysis. What factors, specifically, would affect the company's earnings for the next eight quarters? We continue to factor them into sales revenue and expense based on current circumstances.

Sales Commission and Market Penetration

                    Sales commission and market penetration affect revenue. The compensation that a company receives in the sales market is referred to as sales commission. The company's actual sales volume as a percentage of the overall amount of products that the customer may buy is referred to as the market penetration rate. As a result, actual sales generated revenue in the next eight quarters is determined by multiplying sales revenue per quarter by sales commission and market penetration.

Estimated Earned Revenue = Estimated Revenue x Comission x Market Penetration
                    It's also worth noting that sales commission affects market penetration. The market penetration rate will rise by 0.15 percent for every 0.1 percent reduction in commission.

Square Feet of Office

                    As an office, the organization would rent commercial real estate in my neighborhood. The size of the office then has a direct impact on the rent that the business would pay for the next eight quarters: the higher the company's expense, the greater the office space. As a result, the size of the office can be used to influence the expense of the operation.

Number of Employees

                     Employees who contribute to the company's sales often add to the expense. The market penetration rate of the organization would increase by 0.5 percent for every new employee recruited. The corporation must also pay more than one person's salary at the same time. As a result, the company's earnings would be influenced by the number of workers.

Conclusion of Decision Variables
                   In conclusion, the decision factors are market penetration and sales commissions, which affect sales revenue, and the size of the office, which affects costs. Furthermore, both sales and expenses would be affected by the number of workers. Appendix A lists the decision variables


                         Since there are more real estate companies in New York, and competition among them is fierce, a company's market penetration will not exceed 35 percent and will not fall below 13 percent. The commission will not be less than 2% and will not be more than 10% as a key measure of the company's earnings. Other real estate firms would be taken away with price advantages, reducing this company's sales volume.

                          In terms of expenditures, the office's annual budget does not exceed $300,000. The more people are working, the higher the cost of the office space. On the other hand, the office must have at least three employees, one for greeting customers at the front desk, one for induction houses, and one for back-office operations. Furthermore, the office should not be too small, as this may lead consumers to believe that the company's scale is too small, prompting them to seek out a more reliable company with a larger scale. Furthermore, a smaller workplace cannot build a positive working environment in which workers can perform better.

Conclusion of Constraints

                         The limitations may bring the model closer to reality. Indeed, the company does not rent a big office space and does not hire staff without restrictions. A real estate business would not be able to monopolize the entire sector due to fierce market competition. The constraints in Appendix B are divided into six categories: market penetration, commission, number of workers, annual budget, office location, and average office space per employee.

The Objective Function

                         The company's goal, in this case, is to make the most profit in the next eight quarters. The profit is calculated by subtracting the cost from the sales revenue. As shown in Appendix C, we can determine sales revenue and expense expression before determining the benefit functions.

PV of Sales Revenue

                              Even though regression analysis provided us with sales profits for the next eight quarters, we can discount potential sales revenue to the present value based on the current situation. We'll need to use the discount rate in this case. The quarterly discount rate is 1.05 percent.

PV of Revenue = Estimated Earned Revenue /(1+Discount rate)^the number of Quarters

PV of Costs

                   The sale price per square foot for commercial units was calculated using R studio, and the result is $ 360 per square foot, as shown in Appendix D. We may assume that the employee's annual salary is $60,000. The cost of electricity per square foot per month is $ 1. The total cost includes leases, employee wages, and equipment expenses. We must also ensure that the unit of measurement per fifth is consistent in estimating each form of cost.

PV of costs = Costs/ (1+Discount rate)^the number of Quarters

Profits of Next 8 Quarters

                           Since the difference between the current value of potential cash inflows and outflows is the net present value (NPV). The NPV for the next eight quarters can be calculated using the difference between the current value of future sales income and future sales costs.

NPV = PV of Sales Revenue – PV of Costs

Conclusion of Objective Function

                         As shown in Appendix E, the number of the NPV for each quarter is the company's profit for the next two years. The discount rate will help us better measure the present value of potential profits and expenses and get closer to the real profit value based on future sales revenue and costs. In total, the company's maximum profit is $ 2,018,667, with a market penetration of 14 percent and a fee of 9.33 percent. In a 300-square-foot office, three workers will work. Given the expected increase in earnings over the next two years, the organization should consider opening a community office.

                       Overall, the Excel "Solver" will assist us in developing an optimization model and obtaining the best solution for businesses. We need to identify variables that have a significant effect on the objective value and apply constraints based on real-world constraints.