Concept: Construct histograms and bivariate plots using Excel.
Reading Reference: Topic 2 of Sevilla & Somers and Ch. 4 of Lind, Marchal, & Wathen
Example of Application
Bivariate Plots: Economics: We often need to plot a demand curve to illustrate levels of quantity demanded based on the price of a product. Bivariate plots can be used to project future sales based on pricing or to determine if a price is too high or low. People typically buy more of a product if the price of the product is less, indicating an inverse relationship between price and quantity demanded. Due to the law of marginal utility, however, people buy less of a product as their need for the product becomes satiated which means that the price has to be progressively reduced (McConnell & Brue, p. 40). Therefore, it is typical that a demand curve is nonlinear. Once a few data points are obtained from trial, market studies, or industry information, a bivariate (two sets of data) plot can be drawn that reflects the demand curve for a particular product. Using Excel, the data can be entered in a spreadsheet and plotted. The independent (x) variable is typically plotted on the horizontal axis and the dependent (y) variable is plotted on the vertical axis. When plotting a demand curve, economists always place the quantity demanded on the horizontal axis and the price on the vertical axis. Below is an individual’s demand for corn.
|20 | $ 6.00 |
|40 | $ 5.00 |
|70 | $ 4.00 |
|110 | $ 3.00 |
|160 | $ 2.00 |
How the Math Connects to the Concept
To plot this data into a scatter plot, enter the data on an Excel spreadsheet, as shown below (Figure 1). The quantity demanded is entered in the first column (A1) because it represents the independent variable. The price is entered in the second column (B1). Highlight the data by pointing on A1 with the mouse, left clicking, and drawing in the areas to B6. Then, click on the tool bar icon for Chart Wizard (See arrow).
A screen will pop up (Figure 1). Select: XY (Scatter). Select the chart sub type desired. In this case, we selected the top right sample. Press next twice. Another screen will pop up (Figure 2).
Enter a title for your graph. In this case, we entered “Individual Demand for Corn”. Enter a title for the X-axis. We entered “Quantity Demanded”. Enter a title for the Y-axis. We entered “Price”.
Press Finish and your chart will appear on the spreadsheet. Resize the chart to show all data. The chart can then be copied and pasted in your document (Figure 3).
Figure 1. Excel speadsheet and Chart Wizard
Figure 2. Excel spreadsheet, Chart Wizard step 3 of 4.
Figure 3. Final chart.
Why is this important? The plot shows the different quantities an individual will purchase at different prices. The entire curve is known as the demand curve. The individual points on the curve are referred to as Quantity Demanded. By knowing the demand curve, we can estimate that corn priced at $3.50 per bushel will sell 90 bushels. By the same token, if we price our corn over $6.00 a bushel, we can extrapolate that less than 20 bushels will sell.
Example of Application
Histograms: Histograms are used to analyze frequency distributions. Typically, a frequency distribution includes a number of data points that fall into a specific range. An example of this is a small electric company that wants to save money on its current insurance policy, which has a $1000 deductible and a high premium. The company decides to analyze loss data to determine the amount of reserves it would need to pay potential claims if it increased its deductible to $10,000. Because the cost of liability insurance is high, it intends to pay small claims out of pocket and to submit claims of $10,000 to its liability insurance policy. This...