Break even analysis for Sports Feet Manufacturing using Excel

Excel Modeling: Sports Feet Manufacturing

Planning and Implementing the Model

The first step in this modeling exercise was to plan for the variables that will later be used in the desired calculations. Annual fixed cost ($52,000), unit variable cost ($9), and unit selling price ($25) need to be utilized as inputs for calculations on outputs such as annual revenue, annual total cost, and annual profit / loss (). This data was then placed in a Spreadsheet model for the Profit / Loss analysis. Calculations an outputs used Excel calculations to calculate annual revenue (unit sales*quantity sold), annual variable cost (quantity sold*unit variable cost), annual total cost (annual revenue+annual fixed cost), and annual profit/loss (annual revenue-annual total cost). The model is shown below with the appropriate calculations as well as on the original spreadsheet.

Profit / Loss for Sports Feet Manufacturing

Inputs

Unit Sales Price

$25.00

Annual Fixed Cost

$52,000

Unit Variable Cost

$9.00

Quantity Made and Sold

Caculations and Outputs

Annual Revenue

$62,500.00

Annual Variable Cost

$22,500.00

Annual Fixed Cost

$52,000.00

Annual Total Cost

$74,500

Annual Profit (Loss)

($12,000.00)

Break Even Point

$3,250.00

The next step in the modeling of the Profit / Loss analysis was to repeat the calculation of the outputs, but this time using Excel's Goal Seek feature. The same Break Even Point was calculated using this feature, showing how it is another viable option for calculating the Break Even Point. The following image is a screen shot of the finished Goal Seek calculation on Excel.

Data Graphing

Finally, the last step of the Spreadsheet modeling process was to plot the data points provided in the Data Table step. Using the outputs calculated by Excel, the Chart Wizard was used to draw a Scatter Plot Graph. The Profit was used as Series 1, with increments of 2,000 placed on the Y axis of the graph. The X axis was set at the quantity of units sold, which was set in increments of 500 from 0 to 8,000. The line representing the data points is seen in blue and marks the Break Even Point of 3,250 as it cross over the X axis. The graph can be seen below. The final results are then useful for interpreting profit / loss information based on the unit quantities that Sports Feet Manufacturing has sold in the given period of investigation.