As sales manager for Montevideo Productions, Inc., you are planning to review the prices you charge clients for television advertisement development. You currently charge each client a fee of $15,000 which has resulted in 50 contracts per month. This is down 8 contracts per month from the figure last year when the company charged only $12,000. The estimated costs to Montevideo Productions are given below.

Fixed Cost: 100,000 per month
Variable Cost: 6000 per contract

State the demand price p as a linear fn of rge number of monthly contracts: x

explain how I would accomplish this in excel

To express the demand price $ p $ as a linear function of the number of monthly contracts $ x $, we can use the information provided about the relationship between price and contracts. You are told that when the price was $12,000, the company received 58 contracts (50 + 8), and when the price was $15,000, the company received 50 contracts.

### Step-by-step to derive the demand price function:

1. **Linear relationship between price and contracts**:
   The price $ p $ and the number of contracts $ x $ can be modeled by a linear equation of the form:
   \[
   p = mx + b
   \]
   where $ p $ is the price, $ x $ is the number of contracts, $ m $ is the slope of the line, and $ b $ is the y-intercept (the price when $ x = 0 $).

2. **Calculate the slope**:
   To find the slope $ m $, use the formula for the slope between two points:
   \[
   m = \frac{p_2 - p_1}{x_2 - x_1}
   \]
   where $ p_1 = 15,000 $, $ x_1 = 50 $, $ p_2 = 12,000 $, and $ x_2 = 58 $.
   \[
   m = \frac{12,000 - 15,000}{58 - 50} = \frac{-3,000}{8} = -375
   \]
   So, the slope $ m $ is -375.

3. **Find the y-intercept $ b $**:
   Now that we have the slope, we can plug in one point (for example, $ x_1 = 50 $, $ p_1 = 15,000 $) into the linear equation to solve for $ b $:
   \[
   15,000 = -375(50) + b
   \]
   \[
   15,000 = -18,750 + b
   \]
   \[
   b = 15,000 + 18,750 = 33,750
   \]
   So, $ b = 33,750 $.

4. **Final equation for demand price $ p $**:
   The linear equation is:
   \[
   p = -375x + 33,750
   \]
   where $ p $ is the price charged per contract, and $ x $ is the number of contracts per month.

### Using Excel to calculate the demand price function:
You can use Excel to model this relationship by following these steps:

1. **Set up your data**:
   In Excel, create two columns:
   - In column A, list the number of contracts $ x $ (e.g., from 50 to 58).
   - In column B, list the corresponding prices $ p $ for those contract values (e.g., $15,000 for 50 contracts and $12,000 for 58 contracts).

2. **Create a scatter plot**:
   - Highlight your data and insert a scatter plot (from the "Insert" tab).
   - Select "Scatter with Straight Lines" to visualize the linear relationship between contracts and price.

3. **Add a trendline**:
   - Right-click on the data points in the scatter plot and choose "Add Trendline."
   - In the options, select "Linear" and check the box for "Display Equation on Chart."
   - Excel will display the linear equation, which should match the equation derived earlier $ p = -375x + 33,750 $.

4. **Use the equation for predictions**:
   You can now use this equation to predict the price for any number of contracts by plugging in the value for $ x $ in Excel:
   - In a new column (C), use the formula: `= -375*A2 + 33750` to calculate the price based on the number of contracts in column A.

This approach will help you analyze the pricing model and make data-driven decisions about your pricing strategy.

Would you like more details or have any questions?

### Related Questions:
1. How would changes in fixed costs impact this pricing model?
2. Can you model the total profit as a function of the number of contracts?
3. How could we account for nonlinear demand behavior if price sensitivity changes at different levels of contracts?
4. What are the impacts of increasing variable costs on the breakeven number of contracts?
5. How could this model be adapted if we introduce promotional discounts?

### Tip:
Always ensure you check the sensitivity of your model to changes in costs or demand to understand its flexibility under different scenarios.

As sales manager for Montevideo Productions, Inc., you are planning to review the prices you charge clients for television advertisement development. You currently charge each client a fee of $15,000 which has resulted in 50 contracts per month. This is down 8 contracts per month from the figure last year when the company charged only $12,000. The estimated costs to Montevideo Productions are given below.

Fixed Cost: 100,000 per month
Variable Cost: 6000 per contract

State the demand price p as a linear fn of the number of monthly contracts: x

explain how I would accomplish this in excel

Learn to model the demand price as a linear function for Montevideo Productions' television advertisement contracts, using a step-by-step approach to derive the demand price equation in Excel. Understand core concepts in algebra and linear functions and explore practical Excel implementation. Suitable for Grades 10-12.

Math Problem Statement

Solution

Ask a new question for Free

By Image

Math Problem Analysis

Mathematical Concepts

Formulas

Theorems

Suitable Grade Level

Related Recommendation