Cost-Volume-Profit Analysis Investigation Project – Chapter 8

Ana Duff

Modeling data for Cost-Volume-Profit Analysis

Getting the data

  1. Create a document in Microsoft Word and add the heading CVP Analysis Summary.

  2. Get the data:

    1. Download the Excel file CVP Analysis Data.
    2. Save the document.
  3. Open https://www.desmos.com/calculator

Cost model

  1. Open a new folder and name it Cost.

1. Click on + and select "folder" 2. Enter "Cost" in folder field
Add Cost folder on Desmos
  1. Select the data in cost-production worksheet and copy into Cost folder

  • Make note that the data variables created by Desmos are [latex]x_1[/latex] and [latex]y_1[/latex]. You can change these but will have to make the corresponding changes to formulas used below as well.
Create a new equation line in the folder and copy-paste data from the worksheet into the equation line
Add data to the Cost folder
  1. Analyze the data trend and decide on an appropriate function type to model the cost data

    • Cost data appears to follow a linear trend
    • Should try [latex]C(x)=mx+b[/latex]
  1. Create an appropriate cost function model using the Desmos regression tool

  • In Cost folder, add y1~mx1+b to a new line in the folder
In Cost folder, add y1~mx1+b into new equation line. This will create a curve that best models the data using a linear function and Desmos will produce the function parameters.
Finding the parameters for the curve of best fit
  1. Write the cost function model and plot it:

  • In Cost folder, add the appropriate formula for , with appropriate domain restrictions;
  • Hide the regression model and the data.
Use the values of the parameters produced by the regression to create the function describing the cost in a new equation line in Cost folder. Write the domain restriction(s) in curly brackets at the end of the equation.
The model cost function
  1. Hide and minimize Cost folder:

    In the top left of the folder title line click on the circle to hide the folder items on the graph. Click on the dropdown beside the title of the folder to hide (or unhide) the folder content list.
    Hide folder content list and graph
  1. Record in your CVP Analysis Summary document the cost function model and its domain.

Price-demand model

  1. Open a new folder and name it Price-demand.

1. Click on + and select "folder" 2. Enter "Price-demand" in folder field
Add price-demand folder on Desmos
  1. Copy-paste data from PriceDemandData file into Price-Demand folder.

Create a new equation line in the folder and copy-paste data from the worksheet into the equation line
Add data to the Price-demand folder
  1. Adjust scaling of the axes for a better view

  • Click on Graph Settings tool and adjust the horizontal and vertical view window.
Click on the Settings icon (wrench) in top right corner and adjust the x axis and y axis ranges so that the viewing window is zoomed in on the data points
Adjusting the viewing window
  1. Analyze the data trend and decide on an appropriate function type to model the cost data

  • Price-demand data appears to follow a quadratic trend
  • Should try [latex]p(x)=ax^2+bx+c[/latex]
  1. Create an appropriate price-demand function model using the Desmos regression tool

  • In price-demand folder, add y2~ax2^2+bx2+c
In a new equation line in •In price-demand folder, add y2~ax2^2+bx2+c. A curve through the price-demand data points will appear and Desmos will produce parameters for the quadratic model for the data
Finding the curve of best fit
  1. Write the cost function model and plot it

  • In Price-demand folder, add the appropriate formula for , with appropriate domain restrictions;
  • Hide the regression model and the data.
Hide the regression equation line by clicking on the circle to the left and add to a new line formula for p(x) using the regression parameters for the model calculated by Desmos
  1. Hide and minimize Price-demand folder

  1. Enter into your CVP Analysis Summary document the price function model and its domain.

Revenue model

  1. Open a new folder and name it Revenue

  1. Write the revenue function and plot it

  1. Adjust scaling of the axes for a better view

  • Zoom out until the whole graph is visible, then click on Graph Settings tool and adjust the horizontal and vertical view window.
  1. Hide and minimize Revenue folder

  1. Determine the maximum revenue amount and when it occurs (demand and price).

  2. Enter into your CVP Analysis Summary document the following:

    1. the revenue function model
    2. the maximum revenue amount and when it occurs (demand and price).

Profit model

  1. Open a new folder and name it Profit

  1. Write the profit function and plot it

  1. Hide and minimize Profit folder

  1. Determine the maximum profit amount and when it occurs (demand and price).

  2. Enter into your CVP Analysis Summary document the following:

    1. the profit function model
    2. the maximum profit amount and when it occurs (demand and price).

Cost-volume-profit analysis chart

  1. Create a new folder titled CVP Analysis

  1. Plot the cost, revenue and profit models

  1. Adjust scaling of the axes for a better view

  • Zoom out until all three graphs are fully visible, then click on Graph Settings tool and adjust the horizontal and vertical view window.
  1. Add points of interest to the graph: fixed cost, maximum revenue, maximum cost, maximum profit and break-even points

  • Click on the graph around each point of interest and note the value of the point as identified by Desmos;
  • Plot and label each point on the graph.
  1. Mark regions of profit and loss

  • Plot area where revenue is higher than cost: ;
  • Plot area where cost is higher than revenue: .
  1. Finalize the CVP Analysis Graph

  • Minimize CVP folder;
  • Add graph arrows and axes titles using Graph Settings tool.
  1. Complete your CVP Analysis Summary document by adding the following:

    1. the break-even demand, price and revenue
    2. the link to the graph through Share Your Graph – Copy Link tool and insert it into the Word document;
    3. the image of the graph by first downloading it using Share Your Graph – Export Image tool, then copying into the submission document.

THE END

 

Attribution: Created by Ana Duff (Ontario Tech University), published under Creative Commons Attribution-NonCommercial-ShareAlike licence (CC-BY-NC-SA)

Any modifications to this case study may affect its solvability and caution is advised.