Break Even Analysis and Modeling

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.

Adding Data Tables

After the original Profit / Loss analysis was conducted, the next step in the Spreadsheet modeling was to add more data tables. Data tables help provide numerous data sets that follow the relationship of the function defined in the first steps. This will help provide a more comprehensive picture of how the company sees profits or losses based on the number of units sold, given the quantitative relationships established through the Break Even analysis. Using Excel, a Data Table was completed to show profit as a function of quantity, which could later be used for graphing purposes. The results of these Data Tables are provided below.

Data Table to Show Profit as Function of Quantity

Sales Quantity

Profit

($12,000.00)

0

-52000

-44000

-36000

-28000

2000

-20000

-12000

-4000

12000

20000

28000

36000

44000

52000

60000

68000

76000

Data Graphing