MicroSlo Advertising and Unit Price
There is a one-year pro forma income statement (or cost-profit statement) for the MicroSlo company. The company sells microwave slow cookers, for use in restaurants.
The owner will draw a salary of $80,000 per year. There is also a manager of operations, who will draw a salary of $60,000 per year. The corporate tax rate (based on gross profit and ignore deductions and such for now) is expected to follow the IRS Tax Rate Table (such as from https://www.irs.gov/pub/irs-pdf/i1120.pdf) in each of the two years. Each MicroSlo cooker will require $40 in materials costs and $25 in labor costs in the first year. There will be three sales people. Their salary is expected to average $30,000 per person. Factory rent will be $3,000 per month.
You have the following market data on demand based on unit price and amount you spend on advertising. The unit price must be more than $100/unit and your cash flow does not enable you to spend more than $5000/month on advertising.
The goal of the company is to forecast properly which is essential to their business plan. Your job is to investigate, calculate and justify the forecasted net profit on the pro forma income statement. What balance of unit price and advertising will allow the company to maximize their Net profit?
Learning Objectives
- Explain the pro forma (goal: create a business plan to forecast accurately)
- Investigative action
- Multivariable regression
Statistics Needed
- 3-D graphs
- Break-even Analysis
- Linear, exponential, polynomial curves
- Regression Analysis
- Data Tables & Heatmap
- Transforming/Data Matching
- Max/Min Analysis
- Optimization
- Sensitivity Analysis
Data Source
MicroSlo Proforma Income spreadsheet
| Unit Sold (1 month) | Price | Advertising ( 1 month) |
|---|---|---|
| 245 | 200 | 0 |
| 238 | 205 | 0 |
| 230 | 210 | 0 |
| 254 | 195 | 0 |
| 263 | 190 | 0 |
| 261 | 185 | 0 |
| 255 | 200 | 500 |
| 249 | 200 | 600 |
| 251 | 200 | 700 |
| 251 | 200 | 800 |
| 262 | 200 | 900 |
| 256 | 200 | 1000 |
Tasks:
Answer the questions below to determine the actions needed to maximize the net profit of MicroSlo by balancing the price and money spent on advertising. Use the 6-step model for business analytics as a framework to answer the questions and creating the final report to MicroSlo executives.
Step 1. Recognizing the problem
- Why do we need accuracy in forecasting for net profits?
- What are the 2 factors that will affect our demand?
- Is demand directly related to revenue for sales/net profits?
Step 2. Defining the problem
- Are there any limits for setting values on price or advertising?
- What are the limits? Do they affect how we have to think about or treat the data?
Step 3. Structuring the problem
- Does a linear relationship between price and units sold make sense?
- What about for advertising and units sold?
- What is the scope for profit maximization based on the data we have?
- Can we expand outside the relevant range?
Step 4. Analyzing the problem
- What models and techniques are needed to address the questions?
- Are the assumptions reasonable?
Step 5. Interpreting results and making a decision
- How confident can we be in our results?
- What assumptions were made and how do they affect these results?
- What decisions can we make based off the results?
Step 6. Implementing the solution
- How should the decisions be carried out to achieve our original goal of profit maximization?
- What will determine success or failure of the solution?
- What resources or limitations do we need to consider?
Questions to answer:
-
Do a break-even analysis to determine what the minimum price must be charged. Is there a maximum?
-
Describe how price affects demand, how advertising affects demand, and if there are simultaneous price-advertising effects. Analyze the demand-price-advertising data, create a graph to visually see any interactions.
-
Economically, how does advertising affect demand? Why does that affect what model may be useful for predicting demand based on price and advertising? What is the relevant range?
-
Use a model to find a price and advertising amount that maximizes monthly demand. Is the solution reasonable and reliable? If not, consider if the models for advertising v. demand and price v. demand are the same. If they are different, think about transforming data match theories and/or using logs to perform a linear instead of non-linear regression.
-
Determine the degree of confidence you can have in your value for maximizing monthly demand. Does maximizing demand also maximize profit?
-
Find a price and monthly advertising amount that maximizes Net profit. Note that this will be a non-linear problem. Is the solution reasonable and reliable? Determine the degree of confidence you can have in using this optimal value.
Report
Write a professional report (as if you were a hired consultant or employee) for the director of MicroSlo. The report should be to the point and give specific, actionable advice or solutions based on the data and analytics. Avoid technical aspects and terms that are non-essential and any speculations not substantiated by the data. This report should be concise without lengthy explanations being necessary to understand it.
There is no min or max page limit as charts and tables can take up a highly variable amount of space. However, any charts or tables included need to be understandable to a layman at first glance (labeled and captioned if needed). The particular models you use, interpretations, and advice given are your choice and you should be prepared to explain or defend this if needed!
Use this as an outline for the report:
A. Description of the business problem
- What price and advertising amounts were tried and what improvements are we looking for?
- What are the key decisions that need to be made to enact the improvements? Indicate specifically what the options are (or give examples of options).
- Why are your findings important?
- What questions will be answered and how do these explicitly help address the decisions?
B. Data, methods, and models and results
- Discuss the basic approach used to analyze the data and any concerns about the integrity and quality of the data used. e.g. “There is concern about using $x for advertising because it was not part of the data set. There is insufficient data to apply this assumption to other areas x,y, and z.”
- Briefly describe the models used (formulas, tables, graphs) indicating what they are used for (try to avoid technical details) e.g. “This calculation determines the individual effects of x and y on z to see which variable affects z more.”
- Provide detailed answers to the decision questions using the models
- Indicate all “important” assumptions made and why you think they are reasonable. An assumption is important if: you need it to get a result e.g. “To get the relationship between advertising and price we only measure price within our given range” or, if wrong or invalid, would significantly affect your results. e.g. the assumption “Demand is equal for the company across all 12 months”. If wrong or invalid, would significantly affect what prices could be set and the advertising amounts that would be needed.
- Do not list technical assumptions used for statistical analysis e.g. “We assume the sales data is Normally distributed.”
C. Decision making
- Explain specifically how you used the models and results to make your decisions. This may be literal results such as “Set the advertising price to $500 as a minimum, anything less than that will result in a loss regardless of price. Do not raise the price above $300 as no advertising amount can result in a profit.”
- Detail special considerations or issues to watch out for e.g. “There is not enough data for annual generalizations so net profit maximization will be on a monthly basis.”
- Describe how the effects from using the results for making the decisions can be measured or observed. i.e. How can the company know that profits are being maximized? How can management measure the success of the changes?