Restaurants have traditionally used bottom-end wines to sell by the glass (BTG) at reasonably low prices per glass. In recent years, there has been a growing trend within the restaurant industry to extend BTG programs to higher-end wines. In doing so, they have gready expanded the sales of higher-end wines. The typical markup on a bottle of wine is 100 percent or more, meaning that a bottle of wine that sells at retail for $25 in a wine shop can be priced at $100 in a restaurant. To simplify this discussion, suppose that wine is offered at $10 per glass. The size of the serving can be adjusted depending on the price per botde of the wine being poured. For example, a 4-ounce pour can be offered for wines in the $40 to $50 range and a 2-ounce pour can be offered for wines in the $80 to $100 range. Because a botde has 25.4 ounces, both examples provide more revenue from BTG sales than from full-botde sales! Such programs have been quite successful and produce little loss due to unsold wine in the botde because, with proper storage, a partial bottle can be poured the next evening widi no notable change in taste.
The Problem: Suppose you manage a restaurant with a BTG program. You sell 200 glasses per week at $10 per glass. As an experiment, you once raised the price during the week to $11 a glass and found that you sold 20 glasses less than before the price change. Suppose you assume this information as the basis for a full demand curve for wine BTG at your restaurant
a. Obtain an algebraic and graphical depiction of your restaurant's BTG demand.
b. How many glasses do you expect to sell at $15 per glass?
c. How would your answers change if the response to a $1 price change is 10 glasses per week or 25 glasses per week?
We now demonstrate how Excel can be used to answer this problem. This allows you to easily conduct a what-if or sensitivity analysis. It also prepares you to use Excel to analyze and estimate more complex demand relationships such as those presented in Chapters 4 and 5. Note: This problem requires three important assumptions.
1. You know a point on the BTG demand curve (e.g., P = $10 and Q = 200).
2. You know the slope of the BTG demand curve at this point is ΔP/ ΔQ = .05.
3. You know that the slope does not change (i.e., demand for wine BTG is linear). For a review of slopes and equations, go to the Mathematical Appendix on the Companion Website or see Appendix 3A.
Excel Sheet 3.1 shows a screenshot of cells A1:K28 from the Single Demand worksheet of the Demand Excel App. It shows one way to analyze tins problem in Excel. The graph is shown next to calculations, and equations are shown next to critical cells, in the worksheet. This is simply done to clarify how each number was obtained: By clicking on a cell, you can check the equations for yourself. This example provides a detailed roadmap for creating a graph in Excel. You are encouraged to open Excel and do this problem yourself.
Notice that the negative slope of the demand curve is represented by the negative sign in cell A4 rather than by writing the equation as P = b - mQ Both practices work as long as

Excel Sheet 3.1

you follow through with the mathematics consistently. The benefit of not forcing the minus sign into the equation is that a supply curve can readily be modeled with this graph by entering a positive slope in cell A4. Of course, you also want to adjust the labels appropriately.
One quick benefit of doing the problem in Excel is that the new scenarios in part c are analyzed by changing a single cell, Al. on Excel Sheet 3.1 (the figures below arc- from Excel Sheet 3.1 based on new values for A4). Additional scenarios can be examined by varying any of the four pieces of data on which the problem is based (in cells A2:A4 and E2on Excel Sheet 1).
Transferring Your Excel Work to Other Programs: Once you have a graph you want to use, you can easily insert it into another program by highlighting the graph in Excel and then using the Copy command on Excel's Home ribbon. To paste in a Word or PowerPoint document, open that software and use Paste- Special, Enhanced Metafile, rather than Paste. From this point, the graph can be resized or positioned as needed by right-clicking on the image; it can be adjusted as needed using the Size and Format Picture menus.
Here is a second, related problem: A successful advertising campaign has increased BTG demand by 25 percent at any price relative to pre-advertising levels. Pre-advertising demand is assumed to be the same as was given in the initial 1 Demand scenario (200 glasses are sold at a price of $10 per glass, and each dollar price change is associated with a twenty-glass-per-week change in sales).
The postadvertising demand implies that 250 glasses are sold at a price of $10 per glass, and each dollar price change is associated with a twenty-five-glass-per-week change in sales. This is confirmed by having the /'intercept the same for both demand curves. Excel sheet 3.2 shows how this is analyzed in Excel.
One final scenario: Try doing this problem yourself using the worksheet you created or the 2Demands worksheet. Consider an alternative postadvertising scenario. Rather than selling 25 percent more glasses at any price, you find that if you charge 25 percent more per glass, then you are able to maintain the same number of BTG glasses sold. Adjust D2:D4 to model this new scenario and Copy and Paste (using Paste-Special as described above) your answer graph to a fresh sheet of paper: include answers to a and b:
a. Provide a verbal description of demand in this instance by filling in the values of C and D in the following sentence:
Postadvertising demand shows that 200 glasses are sold at a price of C per glass, and each dollar price change is associated with a D-glass-per-week change in sales.
b. What is the- equation for the postadvertising demand curve in slope-intercept form?

Transcribed Image Text:

DATA (colored cellis)Answer for B given BTG Demand 200 Q New Price in $ 15 10 P in S 0 AQ/AP 26 S24 antity at this pric 100-A2+AE2-A3) Calculations C4 uses point-slope formula 005 m, s lope (/A4) $18 7 I 9 Creating the graph. The easiest way to graph a line 11 demand point to increase s 20.00 lb, P InterceptにPr-mda-A3A6"A2) 400 intercept (1-b/m·-A7/A6 $12 10 s to ase the endpaints. This version includes the $10 clarity Equaions in A14B16 12 int 05 20 A7 P axis 0 25 50 75 100 125 150 175 200 225 250 275 300 15 200 5 10 A2 A3 Bundle 400 S Q axis Number of glasses per week Q 17This graph was obtained by following these steps Highlight A13.816 then click Insert, Scatter, Scatter with Straight Lines and Markers 19 2 Add axis labels &e change the title by clicking on Layout I from the Chart Layouts options on the Chart Tools ribbon 20 3. Change each title by clicking on the title then type the title in the fx anea (above the spreadsheet at D a Three titles were created and font sizes were adjusted using the A arrow on the Home ribbon b The P legend at the right was removed by clicking on it then pressing delete on the keyboard c The size of the graph was changed by clicking on the graph and dragging to the preferred sizae. 24 25 Change axis formatting by right clicking on either axis then click Format Axis and follow the Format Axis menu. a This Q axis has a fixed maximum set at 300 with major units set at 20 b This P axis has a fised maximum set at 26 with major units set at 2 275. Vertical major unit gridlines have been added by right clicking on the horizontal axis and click Add Major Gridlines Part B is answered, and a more general solution is provided, in cell C4 using the point-slope formula for a line. Demand given A4-10, Q($15) = 150 BTG Demand Demand given A4 25 QS15) 75 BTG Demand 526 $24 $22 518 0 514 10 524 522 $20 518 516 S14 $12 $10 52 500 25 50 75 100 125 150 175 300 225 259 275 300 25 50 75 100 125 150 175 200 225 250 275 30 Number of glasses per week, O Number of glasses per week Q