please Solve the LP and get the sensitivity report by creating Excel file

8.6 Electronic Communications Problem The Electronic Communications problem is a maximization problem involving four deci- sion variables, two less-than-or-equal to constraints, one equality constraint, and one greater-than-or-equal to constraint. We will use this problem to provide a summary of the process of formulating a mathematical model, using Excel to obtain an optimal solution, and interpreting the solution and sensitivity report information. In the next chapter we will continue to illustrate how linear programming can be applied by showing additional exam- ples from the areas of marketing, finance, and production management. Electronic Communications manufactures portable radio systems that can be used for two-way communications. The company's new product, which has a range of up to 25 miles, is suitable for use in a variety of business and personal applications. The distri- bution channels for the new radio are as follows: 1. Marine equipment distributors 2. Business equipment distributors 3. National chain of retail stores 4. Direct mail Because of differing distribution and promotional costs, the profitability of the product will vary with the distribution channel. In addition, the advertising cost and the personal sales Chapter 8 Linear Programming: Sensitivity Analysis and Interpretation of Solution TABLE 8.3 PROFIT, ADVERTISING COST, AND PERSONAL SALES TIME DATA FOR THE ELECTRONIC COMMUNICATIONS PROBLEM Distribution Channel Marine distributors Business distributors National retail stores Direct mail Profit per Unit Sold $90 $84 $70 $60 Advertising Cost per Unit Sold $10 $ 8 $ 9 $15 Personal Sales Effort per Unit Sold 2 hours 3 hours 3 hours None Cengage Learning 2013 effort required will vary with the distribution channel. Table 8.3 summarizes the contribu- tion to profit, advertising cost, and personal sales effort data for the Electronic Communi- cations problem. The firm set the advertising budget at $5000. A maximum of 1800 hours of sales force time is available for allocation to the sales effort. Management also decided to produce exactly 600 units for the current production period. Finally, an ongoing contract with a national chain of retail stores requires that at least 150 units be distributed through this distribution channel. Electronic Communications is now faced with the problem of determining the number of units that should be produced for each of the distribution channels in order to maximize the total contribution to profit. In addition to determining how many units should be allo- cated to each of the four distribution channels, Electronic Communications must also de- termine how to allocate the advertising budget and sales force effort to each of the four distribution channels Problem Formulation We will now write the objective function and the constraints for the Electronic Communi- cations problem. We begin with the objective function. Objective function: Maximize profit Four constraints are needed to account for the following restrictions: (1) a limited adver- tising budget, (2) limited sales force availability, (3) a production requirement, and (4) a re- We will now write the objective function and the constraints for the Electronic Communi- cations problem. We begin with the objective function. Objective function: Maximize profit Four constraints are needed to account for the following restrictions: (1) a limited adver- tising budget, (2) limited sales force availability, (3) a production requirement, and (4) a re- tail stores distribution requirement. Constraint 1 Advertising expenditure > Budget Constraint 2 Sales time used