Breakeven analysis

1. Overview

1.1 Background

Breakeven analysis, also known as breakeven analysis or cost-volume-profit analysis, is a comprehensive analysis based on the mutual restriction between product business volume, cost, and profit. It is used to predict profits, control costs, and judge operating conditions. Analytical method.

Zhang Ming is a person in charge of the apparel e-commerce industry, and his business was not doing well last year. Zhang Ming hopes to control the investment of various costs through break-even analysis and analysis, so that the operating profit of the store can reach a new level.

1.2 Analysis method

[Total Cost = Fixed Cost + Variable Cost] [Profit = Monthly Sales-Total Cost]

Fixed cost: The cost that does not change with the increase or decrease in sales within a certain range, such as rent, water and electricity, labor costs, etc.

Variable cost: refers to the cost that changes in a roughly proportional relationship with the increase or decrease in sales, such as: sales commission, cost of goods purchase, etc.

Zhang Ming only needs to fill in last year's cost indicators in the red area, as shown in the figure below:

It can be seen that the profitability of the store last year was negative. Based on the current cost of expenditure, to achieve a breakeven, monthly sales need to reach "3509091.91".

1.3 Analysis results

Cause Analysis:

• The fixed cost is high. If a product is not sold, it will cost 193,000 yuan per month;

• High variable costs, low gross profit for every dollar sold;

• Breakeven sales = fixed cost / gross profit per one dollar of sales. The numerator is high and the denominator is low, so the sales to break even are very high.

Solution:

• Reduce fixed costs: The main rent of e-commerce is warehouses, and you can find cheap warehouses to replace the warehouses with high rents;

• Reduce variable costs: reduce the pricing ratio to 0.65, so that the price of goods will not increase a lot and cause customer disgust.

• Look for the balance between discounts and profits: Although increasing discounts will increase costs, it will also bring about an increase in sales volume. Zhang Ming can compare the relationship between discounts and sales last year, and use the dashboard to calculate the most profitable discounts to provide a reference for this year's big promotion activities.

Manually adjust various cost parameters to obtain the most reasonable cost input method.

1.4 Get the dashboard

Click to view the dashboard: Break-even analysis. After saving the dashboard, users can view the detailed operations in the learning dashboard.

2. Implementation

2.1 Use filter components as fill-in-the-blank parameters

In this dashboard, all information needs to be entered manually by the user. The value entered in the filter component can be used as a parameter to participate in the calculation. For detailed function documents, please refer to: filter component as a parameter to participate in the calculation.

1) Create a new dashboard, drag in the "Numerical Drop-down Filter Component", select "Custom Value List", and change the component name to "Number of Employees", as shown in the figure below:

2) In the same way, create the "employee's average basic salary, monthly rent, other fixed expenses, sales discounts, pricing ratios, platform deductions, and employee commissions".

2.2 Create a "Fixed Cost" indicator

1) To create a component in FineBI, you must select a data table. We don't actually need a data table, so you can choose one at will, as shown in the following figure:

2) Create a new calculation indicator "Fixed Cost", Zhang Ming's fixed cost includes: rent, labor cost, and other fixed expenses, as shown below:

2.3 Create a "Variable Cost" indicator

Zhang Ming's variable costs include: purchase costs, platform deductions, and employee commissions.

• Purchase cost: pricing (tag price) = sales/sale discount, purchase cost = pricing * pricing ratio;

• Platform deduction: For shops on the platform (such as a certain treasure), a certain percentage of sales needs to be paid as a platform handling fee;

• Employee commission: need to pay a certain percentage of sales to employees as a commission to improve their enthusiasm;

Create a new "variable cost" calculation indicator, as shown in the figure below:

2.4 Create a "breakeven sales" indicator

At breakeven, monthly sales = variable cost + fixed cost. Through the formula transformation, the monthly sales formula at break-even is shown in the following figure:

In the same way, add the "Total Cost" and "Monthly Profit" fields according to the formula [Total Cost = Fixed Cost + Variable Cost] [Monthly Profit = Monthly Sales-Total Cost], which will not be repeated here.

2.5 Make indicator cards

Take "Fixed Cost" as an example, select the indicator card, as shown in the figure below.

Because we use a data table, dragging it in will automatically fix the cost * the total number of rows in this table, so the summary mode should choose "Average", as shown below:

Similarly, make other indicator cards: "variable cost", "Total Cost", "Monthly Profit", "breakeven sales".

2.6 Effect display

It is shown in section 1.2.