Solver&PreciseSolutions

Solver&PreciseSolutions - This worksheet will look at a...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
where the demand formula is gotten through regression from data points. Using Solver, we can get the precise solutions to a break even analysis as well as for maximizing profit. We have the following weekly data points from a book store for 2 months. We will plot this data and do a trendline regression to get the demand formula for p. demand x avg price p 893 9.53 1015 8.67 947 9.06 1039 8.4 1081 8.29 883 9.5 965 8.96 1098 8.18 The demand formula from linear regression is p = -0.0064*x + 15.178. Then the weekly revenue formula is R = x*p. The variable cost is an average of $2.72 per book and the fixed cost is $2785 per week. So the weekly cost formula is C = 2.72*x + 2785. Finally, the weekly profit formula is P = R - C. x p revenue R cost C profit P 0 15.18 0 2785 -2785 5 15.15 75.73 2798.6 -2722.87 10 15.11 151.14 2812.2 -2661.06 15 15.08 226.23 2825.8 -2599.57 20 15.05 301 2839.4 -2538.4 25 15.02 375.45 2853 -2477.55 30 14.99 449.58 2866.6 -2417.02 35 14.95 523.39 2880.2 -2356.81 40 14.92 596.88 2893.8 -2296.92 45 14.89 670.05 2907.4 -2237.35 50 14.86 742.9 2921 -2178.1 55 14.83 815.43 2934.6 -2119.17 60 14.79 887.64 2948.2 -2060.56 65 14.76 959.53 2961.8 -2002.27 70 14.73 1031.1 2975.4 -1944.3 75 14.7 1102.35 2989 -1886.65 800 900 1000 1100 1200 7.5 8 8.5 9 9.5 10 f(x) = -0.01x + 15.18 R² = 0.99 Demand Data Points Books x Avg Price p 0 200 400 -4000 -3000 -2000 -1000 0 1000 2000 3000 4000 profit P
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
80 14.67 1173.28 3002.6 -1829.32 85 14.63 1243.89 3016.2 -1772.31 90 14.6 1314.18 3029.8 -1715.62 95 14.57 1384.15 3043.4 -1659.25 Using Solver, the lower break eve 100 14.54 1453.8 3057 -1603.2 x p revenue R 105 14.51 1523.13 3070.6 -1547.47 257.66 13.53 3485.82 110 14.47 1592.14 3084.2 -1492.06 115 14.44 1660.83 3097.8 -1436.97 Using Solver, the upper break eve 120 14.41 1729.2 3111.4 -1382.2 x p revenue R 125 14.38 1797.25 3125 -1327.75 1688.91 4.37 7378.83 130 14.35 1864.98 3138.6 -1273.62 135 14.31 1932.39 3152.2 -1219.81 Using Solver, the maximum profit 140 14.28 1999.48 3165.8 -1166.32 x p revenue R 145 14.25 2066.25 3179.4 -1113.15 973.28 8.95 8709.89 150 14.22 2132.7 3193 -1060.3 155 14.19 2198.83 3206.6 -1007.77 160 14.15 2264.64 3220.2 -955.56 You must always keep in mind tha
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 12

Solver&PreciseSolutions - This worksheet will look at a...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online