Project #17206 - excel project

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-Negative 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
Report DMCA
TutorRating
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..
All Rights Reserved. Copyright by AceMyHW.com - Copyright Policy