Reconsider the example of choosing the advertising budget at VRX that is presented in Section 1.4. In addition to the VRX2000, VRX also has a higher-end virtual-reality headset, the VRX3000. This model sells for $725 and the variable cost for producing the headset is $495. The quarterly fixed costs associated with the VRX3000 are $80,000. VRX would like to determine the best level of advertising for the VRX3000. Past data for advertising and sales are given in the table below. 

a. Use Excel to create a scatter plot to help visualize the relationship between Sales and advertising. 

b. VRX thinks the same type of square-root relationship between sales and advertising found for the VRX2000 in Section 1.4 applies to the VRX3000 as well, that is, Sales ≈ a √Advertising + b. Using Excel, create a new scatter plot with Sales on the vertical axis and the square root of advertising on the horizontal axis. Use Excel’s trendline feature to determine the value for a and b that provides the best fit for the prediction formula Sales ≈ a √Advertising + b.). 

c. Using the prediction formula from part b, build an optimization model to determine the best level of advertising for the VRX3000. (Feel free to use the original spreadsheet model for the VRX2000 as a template; it is available at www.mhhe.com/hillier7e) Solve the model with Solver. What is the best level of advertising and the corresponding estimate for total profit?

Transcribed Image Text:

2 282 28 Q4 Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 Advertising Budget $30,000 $60,000 $10,000 $40,000 $100,000 $50,000 $0 $20,000 $80,000 Sales 568 750 380 612 910 697 149 496 838