Dr. Tara Westerhold

Econ 332

Excel Assignment #3: Profit maximization, revenue maximization and cost minimizationdue Monday, July 27th.

Part I: Applications of Demand and Cost

Given P=200-2Q and TC=1000+50Q+0.20Q2

  1. Create columns for Quantity (Q), Price (P), Total Revenue (TR), Marginal Revenue (MR), Total Fixed Cost (TFC), Total Variable Cost (TVC), Total Cost (TC), Marginal Cost (MC), Average Fixed Cost (AFC), Average Variable Cost (AVC), Average Total Cost (ATC), Profit (π), and Cost Elasticity (Ec) for quantities ranging from zero to 100 in one unit increments.
  • Note: when creating your columns use the algebraic equations for your variables (e.g. for TR get TR expression using the price equation and don’t simply use P*Q in the spreadsheet). This will insure that when you do calculations by hand you will get the same answer as your excel spreadsheet.
  1. Algebraically find the profit maximizing output level, price, and level of profits earned by this firm. For quantity you may round to the nearest whole number since your data are in one unit increments. For prices and cost round to two decimal places for dollars and cents. Show your work.
  2. Confirm your answer by highlighting the corresponding row on your excel spreadsheet.
  3. Calculate the point price elasticity of demand (Ed) at this point. Categorize your estimate as “elastic”, “inelastic”, or “unit elastic”. Show your work.
  1. Algebraically find the revenue maximizing output level, price, and level of profits earned by this firm. For quantity you may round to the nearest whole number since your data are in one unit increments. For prices and cost round to two decimal places for dollars and centsShow your work.
  2. Confirm your answer by highlighting the corresponding row on your excel spreadsheet.
  3. Calculate the point price elasticity of demand (Ed) at this point. Categorize your estimate as “elastic”, “inelastic”, or “unit elastic”. Show your work.
  4. Is this price elasticity of demand estimate what you expected? Briefly explain.
  1. Algebraically find the cost minimizing output level, price, and level of profits earned by this firm. Also, determine per unit cost (ATC) at this output level. For quantity you may round to the nearest whole number since your data are in one unit increments. For prices and cost round to two decimal places for dollars and cents. Show your work.
  2. Confirm your answer by highlighting the corresponding row on your excel spreadsheet.
  3. Calculate the point cost elasticity (Ec) at this point. Categorize your estimate as indicating “economies of scale or IRS”, “diseconomies of scale or DRS” or no economies/diseconomies of scale or CRS”. Show your work.
  4. Is this cost elasticity estimate what you expected? Briefly explain.
  1. Provide a printout of your data with the highlighted rows. Make sure all columns are printed on one page (so you may need to print this landscape or horizontally) and make sure your variable titles are printed at the top of each page of data. Printouts not conforming to these standards will receive a 20% reduction in grade.
  1. Printout your spreadsheet showing the formulas you entered for each variable. On the keyboard use CTRL ` to show the formulas and print.

Part II:Cost Estimation

Use the excel data provided on the class website providing basic information about nursing costs per patient per day, nursing services and profit status for 40 hospitals in the Southeast US.

  1. Import this data into excel
  1. Descriptive Statistics
  2. At the bottom of your spreadsheet (after the last row of data) calculate the mean nursing care cost per day, the mean number of shots given, the mean IV therapies, the mean for pulse taking, wound dressing, and profit status. Interpret each number.
  • Calculate the averages for all the above variables for two different groups based on profit status: for profit and not-for profit. Compare the averages for nursing costs only. Which is more expensive, on average, and by how much?
  1. Estimate the nursing costs per patient per day using the variables provided in your regression.
  2. Write out your cost equation and provide the following analyses.
  • If the number of shots increased by 2 then determine the impact on cost.
  • If the number of IV therapies increased by 4 then determine the impact on cost.
  • Interpret the coefficient for profit status. Reconcile this number with the averages you calculated for nursing costs across the two different types of hospitals (profit and not for profit) done in part B. Explain.
  1. Provide a printout of your data including the averages you created in part B. Make sure all columns are printed on one page and make sure your variable titles are printed at the top of each page of data. Printouts not conforming to these standards will receive a 20% reduction in grade.
  1. Provide a printout of your regression results.