Bartleby Sitemap - Textbook Solutions
All Textbook Solutions for Spreadsheet Modeling & Decision Analysis: A Practical Introduction to Business Analytics (MindTap Course List)
1QP2QP3QP4QPWhat is the relationship between business analytics and spreadsheet modeling?What kinds of spreadsheet applications would not be considered business analytics?7QP8QPWhat is a dependent variable?What is an independent variable?Can a model have more than one dependent variable?Can a decision problem have more than one dependent variable?13QP14QPIn what ways are descriptive models different from predictive models?16QP17QPConsider the spreadsheet model shown in Figure 1.2. Is this model descriptive, predictive, or prescriptive in nature, or does it not fall into any of these categories? Figure 1.2 Example of a simple spreadsheet model19QP20QP21QP22QP23QP24QP25QP26QP27QP1QP2QP3QP4QP5QP6QP7QP8QP9QP10QP11QP12QP13QP14QP15QP16QP17QP18QPAmerican Auto is evaluating their marketing plan for the sedans, SUVs, and trucks they produce. A TV ad featuring this SUV has been developed. The company estimates each showing of this commercial will cost 500,000 and increase sales of SUVs by 3% but reduce sales of trucks by 1% and have no effect of the sales of sedans. The company also has a print ad campaign developed that it can run in various nationally distributed magazines at a cost of 750,000 per title. It is estimated that each magazine title the ad runs in will increase the sales of sedans, SUVs, and trucks by 2%, 1%, and 4%, respectively. The company desires to increase sales of sedans, SUVs, and trucks by at least 3%, 14%, and 4%, respectively, in the least costly manner. a. Formulate an LP model for this problem b. Sketch the feasible region. c. What is the optimal solution?20QP21QP22QP23QP24QP25QP26QP1.1C1.2C1.3C1.4C1.5C1.6C1.7C1.8C1.9C1.10C1QP2QP3QP4QP5QP6QPRefer to question 19 at the end of Chapter 2. Implement a spreadsheet model for this problem and solve it using Solver. 19. American Auto is evaluating their marketing plan for the sedans, SUVs, and trucks they produce. A TV ad featuring this SUV has been developed. The company estimates each showing of this commercial will cost 500,000 and increase sales of SUVs by 3% but reduce sales of trucks by 1% and have no effect of the sales of sedans. The company also has a print ad campaign developed that it can run in various nationally distributed magazines at a cost of 750,000 per title. It is estimated that each magazine title the ad runs in will increase the sales of sedans, SUVs, and trucks by 2%, 1%, and 4%, respectively. The company desires to increase sales of sedans, SUVs, and trucks by at least 3%, 14%, and 4%, respectively, in the least costly manner. a. Formulate an LP model for this problem b. Sketch the feasible region. c. What is the optimal solution?8QP9QP10QP11QP12QP13QP14QP15QP16QP17QPTuckered Outfitters plans to market a custom brand of packaged trail mix. The ingredients for the trail mix will include Raisins, Grain, Chocolate Chips, Peanuts, and Almonds costing, respectively, 2.50, 1.50, 2.00, 3.50, and 3.00 per pound. The vitamin, mineral, and protein content of each of the ingredients (in grams per pound) is summarized in the following table along with the calories per pound of ingredient: The company would like to identify the least costly mix of these ingredients that provides at least 40 grams of vitamins, 15 grams of minerals, 10 grams of protein, and 600 calories per two pound package. Additionally, they want each ingredient to account for at least 5% and no more than 50% of the weight of the package. a. Formulate a LP model for this problem. b. Implement your model in a spreadsheet and solve it. c. What is the optimal mix and how much is the total ingredient cost per package?19QP20QP21QP22QP23QP24QP25QP26QPA manufacturer of prefabricated homes has decided to subcontract four components of the homes. Several companies are interested in receiving this business, but none can handle more than one subcontract. The bids made by the companies for the various subcontracts are summarized in the following table. Assuming all the companies can perform each subcontract equally well, to which company should each subcontract be assigned if the home manufacturer wants to minimize payments to the subcontractors? a. Formulate an LP model for this problem. b. Create a spreadsheet model for this problem and solve it using Solver. c. What is the optimal solution?28QP29QP30QP31QP32QP33QP34QP35QP36QP37QP38QP39QP40QP41QP42QP43QP44QPA natural gas trading company wants to develop an optimal trading plan for the next 10 days. The following table summarizes the estimated prices (per thousand cubic feet (cf)) at which the company can buy and sell natural gas during this time. The company may buy gas at the Ask price and sell gas at the Bid price. The company currently has 150,000 cf of gas in storage and has a maximum storage capacity of 300,000 cf. To maintain the required pressure in the gas transmission pipeline system, the company can inject no more than 200,000 cf into the storage facility each day and can extract no more than 180,000 cf per day. Assume extractions occur in the morning and injections occur in the evening. The owner of the storage facility charges a storage fee of 5% of the market (bid) value of the average daily gas inventory. (The average daily inventory is computed as the average of each days beginning and ending inventory.) a. Create a spreadsheet model for this problem and solve it. b. What is the optimal solution? c. Assuming price forecasts for natural gas change on a daily basis, how would you suggest the company in this problem actually use your model?46QPThe CFO for Eagle Beach Wear and Gift Shop is in the process of planning for the companys cash flows for the next 6 months. The following table summarizes the expected accounts receivables and planned payments for each of these months (in 100,000s). The company currently has a beginning cash balance of 40,000 and desires to maintain a balance of at least 25,000 in cash at the end of each month. To accomplish this, the company has a number of ways of obtaining short-term funds: 1. Delay Payments. In any month, the companys suppliers permit it to delay any or all payments for 1 month. However, for this consideration, the company forfeits a 2% discount that normally applies when payments are made on time. (Loss of this 2% discount is, in effect, a financing cost.) 2. Borrow Against Accounts Receivables. In any month, the companys bank will loan it up to 75% of the accounts receivable balances due that month. These loans must be repaid in the following month and incur an interest charge of 1.5% 3. Short-Term Loan. At the beginning of January, the companys bank will also give it a 6-month loan to be repaid in a lump sum at the end of June. Interest on this loan is 1% per month and is payable at the end of each month. Assume the company earns 0.5% interest each month on cash held at the beginning of the month. Create a spreadsheet model the company can use to determine the least costly cash management plan (i.e., minimal net financing costs) for this 6-month period. What is the optimal solution?48QP1.1C1.2C1.3C1.4C2.1C2.2C2.3C2.4C2.5CKelly Jones is a financial analyst for Wolverine Manufacturing, a company that produces engine bearings for the automotive industry. Wolverine is in the process of hammering out a new labor agreement with its unionized workforce. One of the major concerns of the labor union is the funding of Wolverines retirement plan for its hourly employees. The union believes the company has not been contributing enough money to this fund to cover the benefits it will need to pay to retiring employees. Because of this, the union wants the company to contribute approximately 1.5 million dollars in additional money to this fund over the next 20 years. These extra contributions would begin with an extra payment of 20,000 at the end of 1 year with annual payments increasing by 12.35% per year for the next 19 years. The union has asked the company to set up a sinking fund to cover the extra annual payments to the retirement fund. The Wolverines CFO and the unions chief negotiator have agreed that AAA rated bonds recently issued by three different companies may be used to establish this fund. The following table summarizes the provisions of these bonds. According to this table, Wolverine may buy bonds issued by ACC for 847.88 per bond. Each ACC bond will pay the bondholder 80 per year for the next 15 years, plus an extra payment of 1,000 (the par value) in the fifteenth year. Similar interpretations apply to the information for the IBN and MicroHard bonds. A money market fund yielding 5% may be used to hold any coupon payments that are not needed to meet the companys required retirement fund payment in any given year. Wolverines CFO has asked Kelly to determine how much money the company would have to invest and which bonds the company should buy in order to meet the labor unions demands. If you were Kelly, what would you tell the CFO?1QP2QP3QP4QP5QP6QP7QP8QP9QP10QPUse Solver to create a Sensitivity Report for question 23 at the end of chapter 3 and answer the following questions: a. Is the optimal solution unique? How can you tell? b. Which location is receiving the fewest cars? c. Suppose a particular car at location 1 must be sent to location 3 in order to meet a customers request. How much does this increase costs for the company? d. Suppose location 6 must have at least eight cars shipped to it. What impact does this have on the optimal objective function value? 23. The Rent-A-Dent car rental company allows its customers to pick up a rental car at one location and return it to any of its locations. Currently, two locations (1 and 2) have 16 and 18 surplus cars, respectively, and four locations (3, 4, 5, and 6) each need 10 cars. The costs of getting the surplus cars from locations 1 and 2 to the other locations are summarized in the following table. Because 34 surplus cars are available at locations 1 and 2, and 40 cars are needed at locations 3, 4, 5, and 6, some locations will not receive as many cars as they need. However, management wants to make sure that all the surplus cars are sent where they are needed, and that each location needing cars receives at least five. a. Formulate an LP model for this problem. b. Create a spreadsheet model for this problem and solve it using Solver. c. What is the optimal solution?20QP1QP2QP4QP5QP6QP8QP9QP10QP11QP12QP13QP15QP17QP18QP20QP21QP22QP24QP25QP26QP27QP28QP31QP32QP33QP34QP2.1C2.2C2.3C2.4C3.1C3.2C3.3C4.1C4.2C4.3C1QP2QP3QP6QP7QP8QP11QP14QP15QP16QP17QP18QP19QP21QP24QP30QP31QP32QP33QP34QP35QP37QP41QP42QP44QP1.1C1.2C1.3C2.1C2.2C2.3C2.4C2.5C3.1C3.2C4.1C4.2C4.3C4.4C4.5C1QP2QP3QP8QP9QP13QP14QP15QP16QP17QP19QP22QP23QP26QP27QP28QP29QP1.1C1.2C1.3C1.4C1.5C1.6C2.1C2.2C2.3C2.4C2.5C3.1C3.2C3.3C3.4C3.5C1QP4QP5QP10QP13QP14QP15QP18QP19QP20QP23QP24QP27QP33QP37QP38QP39QP1.1C1.2C2.1C2.2C2.3C2.4C2.5C3.1C3.2C3.3C3.4C3.5C4.1C1QP2QP6QP7QP10QP11QP12QP21QP1.1C1.2C1.3C1.4C1.5C1.6C1.7C1.8C1.9C1.10C2.1C2.2C2.3C2.4C2.5C2.6C2.7C2.8C2.9C2.10C