Cost volume profit analysis (CVP analysis):
This analysis depends on the breakeven point determined to make short term decisions. This is useful for the managers in making decisions regarding the number of units to be produced and sold to earn the target profit.
Compute the requirements in the excel sheet as shown below:
The formulas to compute the requirements are shown below:
Explanation of terms:
• Contribution refers to the balance of sales revenue left after incurring the variable costs. Contribution per unit is expressed in terms of amount per unit whereas contribution margin ratio is expressed in terms of percentage of sales.
• Breakeven point refers to a point of sale where the fixed cost is equal to the contribution. Therefore, the entity will not earn any profit nor incur any loss.
To earn the target net income the entity should also earn the target profit in addition to the fixed cost. The fixed cost added to the target profit gives the target contribution.
Open the file indicated in question. Enter formulas in the worksheet using cell references. After entering required formulas results will be visible. Obtained results are shown in image below.
Formulas used are stated below.
The contribution margin per unit is calculated with the help formula given below.
The contribution margin ratio is calculated with the help formula given below.
The break-even point in units is calculated with the help formula given below.
The break-even point in dollars is calculated with the help formula given below.
Number of units needed to achieve target income is calculated with the help formula given below. Idle
Sales dollars needed to achieve target net income is calculated with the help formula given below.
Net income is calculated with the help formula given below.
Based on the following data, Company P will not earn its target net income of $250,000. Based on the projected unit sales, its net income will be $95,000 only.
Hence, the net income is $95,000 , lesser than target income.
In order to achieve target net income, Company P needs to sell no less than 151,000 units at the $16 per unit selling price. That figure can be determined by entering different numbers into the cell for projected unit sales (C22) until net income based on projected unit sales (cell C38) is equal to $250,000.
Hence, the net income is $250,000.