CLASS / COURSE: BUS 635

QUESTION DESCRIPTION:

BUS 635: Summer 2013

Excel Assignment #1

Focus on Microeconomics

The following are to be done in Excel. Please put your response to each element on a separate tab, as laid out on the template. Your work must be instantly identifiable and viewable by the SCs. Form matters as much as content.

1. Estimation of Demand Curve. The Excel spreadsheet template for this assignment contains a tab with simulated scanner data. The data indicate: the quantity purchased; the price at the time of purchase; and column indicating whether a special promotion was underway at the time.

a) Create scatterplot of the data, putting quantity as Y variable, price as the X variable (note that this is different than the way demand curves are often depicted, with quantity on the horizontal axis). The scatterplot should distinguish observations from dates when the promotion was underway, and when it was not (hint: create separate series for the Y variables, use different colors/symbols for the two series).

b) Estimate the demand curve for dates in which the promotion was underway, and when it was not. This can be done using linear regression, available in the Data Analysis tool under the Data tab. Note that you may need to install the Analysis ToolPak Add-in into Excel. (Under File, go to Options, then to Add-ins.) The Data Analysis tool has a tool Regression that you should use. To estimate the demand curve, the Y variable (aka the dependent variable) is the quantity purchased, while the price is the X variable (independent variable). You will estimate the intercept and slope for both periods. Make sure this information is in a format that is easy to find and read.

c) Estimate the impact of the promotion, by using a regression that controls for price, and includes a “dummy variable” for the promotion. The dummy variable is a series (you’ll need to create) that has a value of 0 when the promotion was not underway, 1 when it was. When using the regression tool, you will put the dummy variable series right next to the price series. The “X Range” will include both columns. The regression output will provide an intercept, slope for the demand curve, and an estimate of the impact of the promotion.

d) Using the demand curve slope result from c), compute the “elasticity” of the demand when the price is at $25 and when it is at $35. Do the computation when there is no special promotion underway. In computing the elasticities, think in terms of a one unit (i.e. $1) change in price. The tab should contain the steps you use to compute the elasticity, but be sure to have the two final results in a clearly marked, easy-to-read section in the tab.

2. Determination of Supply/Demand Equilibrium.

a) Let the demand for a product be given as:

q = 20 – 5p + 2Y

where p represents price and Y income. Let the supply be given as:

q = 2 + 6p.

Let income Y be 5. Plot the supply and demand curves in the conventional way in which quantity is on the horizontal axis, price on the vertical axis. The graph may be created by first generating a table of values in which price and quantity vary. Use reasonable values of the price in creating this table (e.g., don’t use price yielding a negative quantity).

b) Find the equilibrium price and quantity. There are a variety of ways to do this. One is to use the Goal Seek tool, found in the What-If Analysis section of Data Tools under the Data tab. In your spreadsheet create a cell showing the difference between quantity demanded and quantity supplied as a function of price. Then, use Goal Seek to find that price that makes this difference be zero.

c) Let income increase to 6. Find the new equilibrium. Be sure your response is in a separate tab.

3. Derivation of Cost Function. Suppose we have a production technology in which output is a function of the capital and labor inputs. Specifically, the output is given by the mathematical relationship:

𝑞=𝐴∙𝐾𝛼∙𝐿𝛽

where K is the capital input, L the labor input, and A, 𝛼and 𝛽are technology parameters. This relationship is called a production function. In Excel, you would code this function as =A*(K^alpha)*(L^beta), where you would use the appropriate cell references in place of the parameters.

Suppose A = 10, alpha = 0.4, and beta = 0.6. In the short term, we’ll assume that capital is fixed at K = 20.

a) Illustrate the principle of diminishing returns by plotting output as a function of the labor input L (a common measure of productivity). Let L vary from 0 to 60. Also plot the output per unit of labor. This can be a separate plot, but see if you can get both series onto the same plot. Ideally, one vertical axis will show total output, the other output per unit labor. (Hint: in an Excel graph, you can format a series, indicating that it should be plotted using the secondary axis.)

b) Generate the total cost function, i.e., the cost of producing a given level of output q. The 20 units of capital have a value of $10,000, and the cost of capital is 10%. (so the fixed capital cost would be …) The cost per unit of labor is $100. Find the total cost of producing the following values of output: {0, 50, 100, 150, 200, 250, 300, 350}. There are a variety of ways of doing this. I suggest using the Goal Seek function. Goal Seek will provide a way to find the required labor needed to produce a given level of output. Then, given the labor requirement, the cost can then be computed. Create a table mapping output to total cost, and plot.

c) Generate the average cost function, and plot.

d) Generate the marginal cost function. I suggest doing this by finding the total cost at the following values of output: {1, 51, 101, 151, 201, 251, 301, 351}. You can compare the costs of these values with

those done in part b) to find the marginal cost (change in cost) for a one unit increase in production. Create a table with marginal cost, and plot in same graph as average cost that you did in part c).

4. Derivation of Production Possibility Frontier.

Consider the production of two goods, X and Y. The production of each is governed by the same type of production function used in problem 3 above. The values of the production technology parameters are given as:

 

Good	A	Alpha	Beta
X	10	0.4	0.6
Y	5	0.3	0.7

 

The total units of capital available in the economy are 200. The total units of labor are 300.

a) Suppose that capital is initially allocated equally between X and Y. Find the resulting production possibility frontier. I suggest using the Solver Add-in in Excel. First, find the endpoints of the frontier by allocating all labor to X, then all labor to Y. Then, trace out the curve by picking a reasonable set of given values of X (e.g. {200, 400, etc.} , and then finding the corresponding maximal value of Y. The maximum is found in Solver, with the constraints: 1) X is at the given level; and 2) the sum of total labor inputs is 300. I would also add the constraint that the labor inputs for each good cannot be less than zero. The set of X values used to trace out the curve should be sufficient that the curve is reasonably determined.

b) Suppose now that capital can be freely allocated. Fixing X at 1400, find the maximum level of Y that can be produced when the allocation of both capital and labor is optimized. In this case Solver will adjust both capital and labor, accomplished by putting these inputs in adjacent cells in the spreadsheet. Show both the maximum level of Y as well as the levels of capital and labor inputs.

SOLUTION DESCRIPTION: Completed Solution is attached. Click on Buy button and then download file to get full solution.

SUBJECTS / CATEGORIES:
1. Business Economics
2. Business Statistics
3. Economics
4. Microeconomics
5. Statistics
6. Quantitative Techniques