PLEASE USE EXCEL SOLVER AND INCLUDE SCREENSHOT

Case Study 1 Giannis, a Greek entrepreneur, has plans to strike out on his own by opening a boutique consulting business. He plans to open the business in one year. In anticipation of the business opening, Giannis has four tasks to address: i) Financing ii) Marketing iii) Business Strategy iv) Job Scheduling Giannis currently has $1 million in cash available. He plans to invest this money for one year in order to finance his start-up costs (insurance and marketing). There are six investments that interest Giannis; the expected returns and variances of the investments are shown below. In addition, the covariances between the investments are also given. Assuming that his portfolio generates the expected return, Giannis will have his initial $1 million plus the investment returns after one year. The insurance fee is expected to be set at $150,000, and Giannis hopes to have $1 million available for marketing. With respect to marketing, Giannis has identified five different options: Direct Marketing (DM), Industry Conferences (IC), Business Finding Group (BFG), Billboards (B), and Television (T). His goal is to find the optimal mixture of these options. To accomplish this, Giannis set up and solved a linear programming model to determine how his funds should be allocated. The model sought to maximize the firm's total "brand visibility index" while satisfying the below constraints. Each marketing option has its own visibility index: 0.2 , 0.6,1.1,0.3, and 0.5 for DM, IC, BFG, B, and T, respectively. The total brand visibility index is calculated by multiplying the amount spent on each marketing option by its individual visibility index and adding the results. The constraints are: 2) Using only the sensitivity report given above, answer the following questions. Give as much information as possible regarding changes to the objective function value and/or changes to the optimal variable values. a) Suppose that the brand visibility index for direct marketing is questioned. What would happen if its index was actually 0.4 ? b) Suppose that the brand visibility index for industry conferences is questioned. What would happen if its index was actually 0.7 ? c) Suppose that the brand visibility index for business finding group is questioned. What would happen if its index was actually 0.9 ? d) Gianni is also interested to understand how things might change under two scenarios: i) if the budget turned out to be $15,000 larger than expected and ii) if the budget turned out to be $500,000 less than expected. What would happen if either of these scenarios occurred? e) What would happen if the amount that can be spent on direct marketing increased to $300,000 ? f) What would happen if the minimum television ad spending was decreased to 25,000 ? g) What would happen if the maximum amount of industry conference spending were to increase to $150,000 ? OAG Group pays its consultants an average salary of $160k per year, and they work an average of 45 hours per week. The average annual revenue per consultant is $350k and the average customer satisfaction score is 3.5 . Scandinavian Insights pays its consultants an average salary of $190k per year and they work an average of 50 hours per week. The average annual revenue per consultant is $375k and the average customer satisfaction score is 3.6. The final task is to optimize the mixture of consulting jobs. Giannis places jobs into four different categories: Pharmaceutical, Transportation, Government, and Community. The below table shows the number of jobs that are expected to be available each month in each category, the amount of time it takes to complete a job in each category, and the profit that a job is expected to generate in each category. For tax reasons, at least 15 government jobs should be completed, and to maintain visibility within the industry, at least 2 pharmaceutical jobs should be completed. Because of the skill required for pharmaceutical and government jobs, no more than 60% of used labor hours can be devoted to these types of jobs. A further requirement is that the combined number of transportation and government jobs cannot exceed 22. Giannis anticipates that 220 hours of labor will be available each month. i) The budget cannot be exceeded. ii) The amount spent on direct marketing cannot exceed $250,000. iii) No more than $125,000 can be spent on industry conferences. iv) At least $250,000 should be devoted to the business-finding group. v) Up to $125,000 can be spent on billboards. vi) Television ad spending should be between $50,000 and $150,000. vii) The amount spent on the business finding group cannot exceed the combined amount spent on direct marketing, industry conferences, and billboards. The optimal solution is (DM, IC, BFG, B, T) =(225,125,475,125,50), with an objective function value of 705 (all values in thousands). Because of the uncertainty inherent in making projections one year forward, Giannis has produced the sensitivity report for this linear programming model: In search of an efficient business strategy, Giannis has identified three comparable consulting firms; Lone Star Consulting, OAG Group, and Scandinavian Insights. Each of these companies make use of a distinct business strategy, and Giannis is trying to determine which of them to emulate. Consultants at Lone Star Consulting are paid an average of $140k per year and work an average of 40 hours per week. They produce an average annual revenue (on a per consultant basis) of $300k and an average customer satisfaction score of 3.0 (on a five-point scale). Case Study 1 Giannis, a Greek entrepreneur, has plans to strike out on his own by opening a boutique consulting business. He plans to open the business in one year. In anticipation of the business opening, Giannis has four tasks to address: i) Financing ii) Marketing iii) Business Strategy iv) Job Scheduling Giannis currently has $1 million in cash available. He plans to invest this money for one year in order to finance his start-up costs (insurance and marketing). There are six investments that interest Giannis; the expected returns and variances of the investments are shown below. In addition, the covariances between the investments are also given. Assuming that his portfolio generates the expected return, Giannis will have his initial $1 million plus the investment returns after one year. The insurance fee is expected to be set at $150,000, and Giannis hopes to have $1 million available for marketing. With respect to marketing, Giannis has identified five different options: Direct Marketing (DM), Industry Conferences (IC), Business Finding Group (BFG), Billboards (B), and Television (T). His goal is to find the optimal mixture of these options. To accomplish this, Giannis set up and solved a linear programming model to determine how his funds should be allocated. The model sought to maximize the firm's total "brand visibility index" while satisfying the below constraints. Each marketing option has its own visibility index: 0.2 , 0.6,1.1,0.3, and 0.5 for DM, IC, BFG, B, and T, respectively. The total brand visibility index is calculated by multiplying the amount spent on each marketing option by its individual visibility index and adding the results. The constraints are: 2) Using only the sensitivity report given above, answer the following questions. Give as much information as possible regarding changes to the objective function value and/or changes to the optimal variable values. a) Suppose that the brand visibility index for direct marketing is questioned. What would happen if its index was actually 0.4 ? b) Suppose that the brand visibility index for industry conferences is questioned. What would happen if its index was actually 0.7 ? c) Suppose that the brand visibility index for business finding group is questioned. What would happen if its index was actually 0.9 ? d) Gianni is also interested to understand how things might change under two scenarios: i) if the budget turned out to be $15,000 larger than expected and ii) if the budget turned out to be $500,000 less than expected. What would happen if either of these scenarios occurred? e) What would happen if the amount that can be spent on direct marketing increased to $300,000 ? f) What would happen if the minimum television ad spending was decreased to 25,000 ? g) What would happen if the maximum amount of industry conference spending were to increase to $150,000 ? OAG Group pays its consultants an average salary of $160k per year, and they work an average of 45 hours per week. The average annual revenue per consultant is $350k and the average customer satisfaction score is 3.5 . Scandinavian Insights pays its consultants an average salary of $190k per year and they work an average of 50 hours per week. The average annual revenue per consultant is $375k and the average customer satisfaction score is 3.6. The final task is to optimize the mixture of consulting jobs. Giannis places jobs into four different categories: Pharmaceutical, Transportation, Government, and Community. The below table shows the number of jobs that are expected to be available each month in each category, the amount of time it takes to complete a job in each category, and the profit that a job is expected to generate in each category. For tax reasons, at least 15 government jobs should be completed, and to maintain visibility within the industry, at least 2 pharmaceutical jobs should be completed. Because of the skill required for pharmaceutical and government jobs, no more than 60% of used labor hours can be devoted to these types of jobs. A further requirement is that the combined number of transportation and government jobs cannot exceed 22. Giannis anticipates that 220 hours of labor will be available each month. i) The budget cannot be exceeded. ii) The amount spent on direct marketing cannot exceed $250,000. iii) No more than $125,000 can be spent on industry conferences. iv) At least $250,000 should be devoted to the business-finding group. v) Up to $125,000 can be spent on billboards. vi) Television ad spending should be between $50,000 and $150,000. vii) The amount spent on the business finding group cannot exceed the combined amount spent on direct marketing, industry conferences, and billboards. The optimal solution is (DM, IC, BFG, B, T) =(225,125,475,125,50), with an objective function value of 705 (all values in thousands). Because of the uncertainty inherent in making projections one year forward, Giannis has produced the sensitivity report for this linear programming model: In search of an efficient business strategy, Giannis has identified three comparable consulting firms; Lone Star Consulting, OAG Group, and Scandinavian Insights. Each of these companies make use of a distinct business strategy, and Giannis is trying to determine which of them to emulate. Consultants at Lone Star Consulting are paid an average of $140k per year and work an average of 40 hours per week. They produce an average annual revenue (on a per consultant basis) of $300k and an average customer satisfaction score of 3.0 (on a five-point scale)