**Price Point Analysis Worksheet:**

1. The company estimates that it can sell **1,500** of the new gamer mouse at a price **of $65** each. Enter these values in **D5** and **D6**.

2. Enter a formula to calculate the revenue this would produce in **D7**. Format **D5:D7** appropriately as shown.

3. Enter the Price Elasticity value of **1.8** in **D10**, which indicates that for every 10% increase in the price, sales will decline by 18 percent. Enter the sales price of **$65** in **D12**.

4. Enter a formula to calculate the estimated units sold assuming elasticity in **D11**, using the INT function to ensure that the units sold value will be an integer. *(Hint: use the elasticity formula from Tutorial 10.)*

5. Enter a formula in **D13** to calculate the revenue that would result from the price and quantity under price elasticity.

6. Insert formulas in **B21** and **C21** that reference the values of **D12** and **D13** respectively.

7. In the range **B22:C42**, create a **one-variable data table** for price values ranging from **$15** to **$115** in increments of **$5**. Format this table and its headings as shown.

8. Use the range **B22:C42 **to create a **scatterplot with smooth lines** to show how changing the sales price affects revenue.

a. Change the data series name to **Price Elasticity Curve**.

b. Move the chart legend to the bottom of the chart.

c. Set the range of the horizontal axis from **$0** up to **$120**.

d. Set the range of the vertical axis from **$-100,000** up to **$150,000** in increments of **$25,000**. Edit the axis number format to display the axis values in the custom format **$#”K"**. Do not show the **display units** on the chart. Use the **Axes** button to set the **Primary Vertical Axis** to **Show Axis in** **Thousands**.

e. Add **Price** as the horizontal axis title, and **Revenue** as the vertical axis title (rotated). Format these with font size of **10.**

f. Change the chart title to **Price Point Analysis**.

g. Resize and reposition the chart to cover the range **I20:P39**.

9. Add a new data series to the chart consisting of the range **D6:D7** as a single point. Name this series **Current Price Point**.

a. Do not display a line for this point. *(Hint: To find this point on the chart, use the dropdown list in the Current Selection group of the Chart Tools – Layout tab to select the Series "Current Price Point", then use the Format Selection button below it .)*

b. Use the built-in circle marker style, and set the size to **7**.

c. Change the marker color of this data point to **Gold Accent 4 **(in the top color line of the Theme Colors).

d. Change the marker line color to **Red Accent 3** (in the top color line of the Theme Colors).

10. You will now use **Solver** to determine if the company's revenue will increase if the price of the telescope decreases. Use **Solver** to find the maximum revenue in cell **D13** by changing the value of **D12**. Constrain **D12** to be an integer value, and constrain **D12** to be greater than or equal to **$15** (you should have two constraints). Keep the Solver solution.

*11. *Now you will capture the optimal price point values before doing more analysis. Copy the values only (not the formulas) from the range **D12:D13** into the range **G20:G21**, and format them as shown. *(Your values in G20:G21 should match the same cells in the image provided below.)*

12. Save the current Solver parameters into the range **F24:F29**, and format as shown.

13. Add a new data series to the chart named **Optimal Price Point **using the values in the range **G20:G21**.

a. Do not display a line for this point.

b. Use the built-in circle marker style, and set the size to **7**.

c. Change the marker color of this data point to **Orange Accent 1** (in the top color line of the Theme Colors).

d. Change the marker line color to **Red Accent 3** (in the top color line of the Theme Colors).

14. The Solver result you just achieved assumes an unlimited amount of parts in stock. Now you will analyze the optimal price point given the current stock of parts. In the range **I6:I17**, enter formulas to calculate the number of parts required to meet the current units to be built as shown in **D11**. Format as shown.

15. In the range **J6:J17**, enter formulas to calculate how many of each part will be left after the desired number of units are built. Set the number format for the range **J6:J17** to show no decimal places, and negative values in red in parentheses.

16. Change the price value in **D12** back to **$65. ** Edit the Solver model to add a constraint that you cannot produce more computer mice than allowed by the current amount of parts in stock. Ensure that the **Make Unconstrained Variables Non-Negativ**e checkbox is checked. Run the Solver model again. Keep the new Solver results. You should now have the same values for **D11:D13** as shown in the image below.

17. Now you will capture the optimal price point with inventory values. Copy the values only (not the formulas) from the range **D12:D13** into the range **G32:G33** and format as shown.

18. Add a new data series to the chart named **Optimal Price Point with Inventory** using the values in the range **G32:G33**.

a. Do not display a line for this point.

b. Use the built-in circle marker style, and set the size to **7**.

c. Change the marker color of this data point to **Blue Accent 2** (in the top color line of the Theme Colors).

d. Change the marker line color to **Red Accent 3** (in the top color line of the Theme Colors).

19. Save the current Solver parameters into the range **F36:F42**, and format as shown.

20. Set the page orientation to Landscape, and scale it to fit on a single page.

21. Add a **right footer** containing your name, the date, and the name of the workbook on separate lines.

22. Save your file, close Excel, and submit your completed Excel workbook.

Subject | Computer |

Due By (Pacific Time) | 11/22/2013 02:30 pm |

Tutor | Rating |
---|---|

pallavi Chat Now! |
out of 1971 reviews More.. |

amosmm Chat Now! |
out of 766 reviews More.. |

PhyzKyd Chat Now! |
out of 1164 reviews More.. |

rajdeep77 Chat Now! |
out of 721 reviews More.. |

sctys Chat Now! |
out of 1600 reviews More.. |

sharadgreen Chat Now! |
out of 770 reviews More.. |

topnotcher Chat Now! |
out of 766 reviews More.. |

XXXIAO Chat Now! |
out of 680 reviews More.. |