The “Top Academic Athletes at WNMU” is a project calendar planned by ASWNMU to be published in Silver City, New Mexico depicting the top academic athletes at Western New Mexico University.

A group of ambitious undergraduate students from nearby New Mexico Tech published a similar calendar in the fall of 2017. The publication was priced at $7.50, and sales were estimated at a relatively modest 1,500 copies.

The manager of a retail store claimed that the price of $7.50 was too high and believed that at a price of $4 the publishers could expect substantial gift sales of the calendar, with perhaps selling 3500 or even 5000. The editors believed, however, that sales would be only about 2500 at the lower price.

The estimated costs of publishing the calendars are shown in Exhibit 1.

Case Requirements:
Use Excel and Word for this Project. The actual write up should be done in Word. This is a consultant’s report and should be presented as a professional document. Any work done in Excel should be included in an organized Appendix. Explain in detail the process you follow.

1. Based on the information above, do the following for each scenario below:
• Estimate the demand curve (price on the vertical, quantity on the horizontal) using Excel’s X-Y scatterplot with markers, or regression analysis tool (the goal here is to get a linear equation. P=f(Q))
• From the demand curve, derive the marginal revenue curve. (It is the demand curve with twice the slope.)
• Plot both on the same graph. To plot MR, you may need to solve for Q when P = 0 to get your “X intercept.”
• Calculate Price Elasticity of Demand for each demand curve using the points you used to calculate each curve.

The three scenarios are:

(a) Sales increase to 2500 at the lower price.
(b) Sales increase to 3500 at the lower price,
(c) Sales increase to 5000 at the lower price

2. Determine the total cost, average total cost, and marginal cost for quantities of:

a) 1000,
b) 2500
c) 3500
d) 5000

Assume that each quantity was pre-planned prior to printing. Note that the composition costs are fixed for a single run and there is a base cost for the printer. All remaining costs, including freight, are proportional to volume. Make any other assumptions or calculations that seem appropriate.

3. Plot the total cost data points in Excel. Derive the total cost curve using Excel’s X-Y scatterplot with markers or regression analysis tools. (Hint: it is linear.)

4. Plot the ATC data points in Excel. Derive the average total cost curve, and marginal cost curve using Excel’s X-Y scatterplot with markers or regression analysis tools. (Remember your MC curve is the first derivative of TC).

5. Plot your demand and marginal revenue curves for each scenario.
Use total cost of calendar production. Marginal revenue is total cost of production minus sales. Adjust per 1,000 printed

6. Would a price of $4 be sound under any of these scenarios? Explain.
Figure out after all graphing. Highest profit

7. Under each of these scenarios, what is the profit maximizing price? Marginal revenue and profit will decide this.

8. Under each of these scenarios, using the profit maximizing price, what quantity should be produced and sold.

9. What is the maximum profit under each scenario?

10. Finally, which scenario would you recommend the ASWNMU choose? Explain. (Hint: it would be useful to show what would happen if the scenario came to pass and what would happen if it didn’t.)

The “Top Academic Athletes at WNMU” is a project calendar planned b...