table-commercial-calculations-formula

table business calculations formula | 9 Exercises

by

Application: Cocotte & Co

States :

Cocotte & Co, a company specializing in the sale of cast iron casseroles, would like to analyze its sales performance for the last quarter. The company sells two main models: the classic cocotte and the premium cocotte. Each model has a different purchase price and a margin that varies according to quarterly sales volumes. Here is the data for the quarter in question:

  • Classic Cocotte: Purchase price excluding tax of €50, sale price excluding tax of €80, and 500 units sold.
  • Premium Cocotte: Purchase price excluding tax of €120, sale price excluding tax of €200, and 200 units sold.

Work to do :

  1. Calculate the unit margin for each of the pressure cooker models.
  2. Determine the overall margin for the quarter for each pressure cooker model.
  3. Calculate the margin rate for each pressure cooker model.
  4. Calculate the markup rate for each model of pressure cooker.
  5. Analyze models in terms of profitability and suggest possible improvements.

Proposed correction:

  1. Unit Margin:

    For the classic casserole:
    Unit margin = PV excluding tax – PA excluding tax
    = €80 – €50
    = 30 €

    For the premium casserole:
    Unit margin = PV excluding tax – PA excluding tax
    = €200 – €120
    = 80 €

    The unit margin for the classic model is €30 and for the premium model is €80.

  2. Overall Margin:

    For the classic casserole:
    Overall margin = Unit margin x Quantity sold
    = €30 x 500
    £15

    For the premium casserole:
    Overall margin = Unit margin x Quantity sold
    = €80 x 200
    £16

    The overall margin for the classic model is €15 and for the premium model is €000.

  3. Margin Rate:

For the classic casserole:
Margin rate = ((PV excluding tax – PA excluding tax) ÷ PA excluding tax) x 100
= ((€80 – €50) ÷ €50) x 100
= 60%

For the premium casserole:
Margin rate = ((PV excluding tax – PA excluding tax) ÷ PA excluding tax) x 100
= ((€200 – €120) ÷ €120) x 100
= 66,67%

The margin rate for the classic model is 60%, and for the premium model it is 66,67%.

  1. Mark Rate:

    For the classic casserole:
    Brand rate = ((PV excluding tax – PA excluding tax) ÷ PV excluding tax) x 100
    = ((€80 – €50) ÷ €80) x 100
    = 37,5%

    For the premium casserole:
    Brand rate = ((€200 – €120) ÷ €200) x 100
    = 40%

    The markup rate for the classic model is 37,5%, and for the premium model it is 40%.

  2. Premium cocottes offer better profitability in terms of unit margin and mark-up rate. However, the sales volume of classic cocottes is higher. To improve overall profitability, Cocotte & Co could intensify its marketing efforts on premium cocottes or apply cost-cutting strategies on classic cocottes without increasing prices.

Formulas Used:

Title Formulas
Unit margin PV HT – PA HT
Overall margin Unit margin x Quantity sold
Margin rate ((PV HT – PA HT) ÷ PA HT) x 100
Brand taxes ((PV HT – PA HT) ÷ PV HT) x 100

Application: TechnoWorld

States :

TechnoWorld, a company specializing in the trading of electronic components, wants to evaluate its CPU sales performance for the last half year. The company sells two types of CPUs: Model X and Model Y. Here is the data for each model:

  • Model X: Purchase price excluding VAT of €150, sale price excluding VAT of €250, and 350 units sold.
  • Model Y: Purchase price excluding tax of €300, sale price excluding tax of €420, and 150 units sold.

Work to do :

  1. Calculate the unit margin for each processor model.
  2. Calculate the net sales for each processor model.
  3. Determine the overall margin for each processor model.
  4. Calculate the margin rate for each processor model.
  5. Evaluate model performance and recommend actions to increase sales or profitability.

Proposed correction:

  1. Unit Margin:

    For Model X:
    Unit margin = PV excluding tax – PA excluding tax
    = €250 – €150
    = 100 €

    For Model Y:
    Unit margin = PV excluding tax – PA excluding tax
    = €420 – €300
    = 120 €

    The unit margin for model X is €100 and for model Y is €120.

  2. Turnover excluding tax:

    For Model X:
    Net sales = Net sales x Quantity sold
    = €250 x 350
    £87

    For Model Y:
    Net sales = Net sales x Quantity sold
    = €420 x 150
    £63

    The net sales figure for the X model is €87 and for the Y model it is €500.

  3. Overall Margin:

For Model X:
Overall margin = Unit margin x Quantity sold
= €100 x 350
£35

For Model Y:
Overall margin = Unit margin x Quantity sold
= €120 x 150
£18

The overall margin for the Model X is €35 and for the Model Y is €000.

  1. Margin Rate:

    For Model X:
    Margin rate = ((PV excluding tax – PA excluding tax) ÷ PA excluding tax) x 100
    = ((€250 – €150) ÷ €150) x 100
    = 66,67%

    For Model Y:
    Margin rate = ((PV excluding tax – PA excluding tax) ÷ PA excluding tax) x 100
    = ((€420 – €300) ÷ €300) x 100
    = 40%

    The margin rate for Model X is 66,67%, and for Model Y it is 40%.

  2. The Model X is more profitable in terms of revenue and overall margin. However, increasing sales of the Model Y could be an interesting lever for TechnoWorld, for example, by developing bundled offers or discounts to encourage purchase.

Formulas Used:

Title Formulas
Unit margin PV HT – PA HT
Turnover excluding tax PV HT x Quantity sold
Overall margin Unit margin x Quantity sold
Margin rate ((PV HT – PA HT) ÷ PA HT) x 100

Application: SweetBakery

States :

SweetBakery, a bakery specializing in custom cakes, wants to analyze the sales of two of its popular products for the past month. Here is the data for these products:

  • Chocolate cake: Purchase price excluding tax of €10, sale price excluding tax of €25, and 300 units sold.
  • Vanilla Cake: Purchase price excluding VAT of €8, sale price excluding VAT of €18, and 500 units sold.

Work to do :

  1. Calculate the unit margin for each type of cake.
  2. Calculate the total sales amount excluding tax for each type of cake.
  3. Determine the overall margin for each type of cake.
  4. Calculate the markup rate for each type of cake.
  5. Provide recommendations to optimize sales based on margin and sales data.

Proposed correction:

  1. Unit Margin:

    For the chocolate cake:
    Unit margin = PV excluding tax – PA excluding tax
    = €25 – €10
    = 15 €

    For the vanilla cake:
    Unit margin = PV excluding tax – PA excluding tax
    = €18 – €8
    = 10 €

    The unit margin for chocolate cake is €15 and for vanilla cake is €10.

  2. Total Sales Amount excluding VAT:

    For the chocolate cake:
    Sales excluding VAT = PV excluding VAT x Quantity sold
    = €25 x 300
    £7

    For the vanilla cake:
    Sales excluding VAT = PV excluding VAT x Quantity sold
    = €18 x 500
    £9

    The total sales amount excluding VAT for the chocolate cake is €7 and for the vanilla cake is €500.

  3. Overall Margin:

For the chocolate cake:
Overall margin = Unit margin x Quantity sold
= €15 x 300
£4

For the vanilla cake:
Overall margin = Unit margin x Quantity sold
= €10 x 500
£5

The overall margin for the chocolate cake is €4 and for the vanilla cake is €500.

  1. Mark Rate:

    For the chocolate cake:
    Brand rate = ((PV excluding tax – PA excluding tax) ÷ PV excluding tax) x 100
    = ((€25 – €10) ÷ €25) x 100
    = 60%

    For the vanilla cake:
    Brand rate = ((€18 – €8) ÷ €18) x 100
    = 55,56%

    The markup rate for chocolate cake is 60%, and for vanilla cake it is 55,56%.

  2. SweetBakery could consider promoting more vanilla cake, which generates a higher overall margin due to the sales volume. An improvement in the chocolate cake margin could be achieved by revisiting production costs or adjusting the selling price.

Formulas Used:

Title Formulas
Unit margin PV HT – PA HT
Sales excluding VAT PV HT x Quantity sold
Overall margin Unit margin x Quantity sold
Brand taxes ((PV HT – PA HT) ÷ PV HT) x 100

Application: GreenTech Solutions

States :

GreenTech Solutions is an innovative company specializing in green energy solutions. The company sells two main products: standard solar panels and advanced solar panels. Here is the sales data for these products for the past year:

  • Standard panels: Purchase price excluding VAT of €500, sale price excluding VAT of €800, and 600 units sold.
  • Advanced panels: Purchase price excluding VAT of €850, sale price excluding VAT of €1, and 500 units sold.

Work to do :

  1. Calculate the unit margin for each of the solar panels.
  2. Determine the overall margin for each type of solar panel.
  3. Calculate the margin rate for each type of solar panel.
  4. Calculate the markup rate for each type of solar panel.
  5. Based on the data provided, recommend strategies to improve the profitability of GreenTech Solutions.

Proposed correction:

  1. Unit Margin:

    For standard panels:
    Unit margin = PV excluding tax – PA excluding tax
    = €800 – €500
    = 300 €

    For advanced panels:
    Unit margin = PV excluding tax – PA excluding tax
    = €1 – €500
    = 650 €

    The unit margin for standard panels is €300 and for advanced panels is €650.

  2. Overall Margin:

    For standard panels:
    Overall margin = Unit margin x Quantity sold
    = €300 x 600
    £180

    For advanced panels:
    Overall margin = Unit margin x Quantity sold
    = €650 x 350
    £227

    The overall margin for standard panels is €180 and for advanced panels is €000.

  3. Margin Rate:

For standard panels:
Margin rate = ((PV excluding tax – PA excluding tax) ÷ PA excluding tax) x 100
= ((€800 – €500) ÷ €500) x 100
= 60%

For advanced panels:
Margin rate = ((PV excluding tax – PA excluding tax) ÷ PA excluding tax) x 100
= ((€1 – €500) ÷ €850) x 850
= 76,47%

The margin rate for standard panels is 60%, and for advanced panels it is 76,47%.

  1. Mark Rate:

    For standard panels:
    Brand rate = ((PV excluding tax – PA excluding tax) ÷ PV excluding tax) x 100
    = ((€800 – €500) ÷ €800) x 100
    = 37,5%

    For advanced panels:
    Markup rate = ((€1 – €500) ÷ €850) x 1
    = 43,33%

    The markup rate for standard panels is 37,5%, and for advanced panels it is 43,33%.

  2. Advanced solar panels have higher unit and overall margin despite lower sales volume. GreenTech Solutions could focus on promoting these panels to increase sales or optimize manufacturing costs of standard panels to improve their profitability.

Formulas Used:

Title Formulas
Unit margin PV HT – PA HT
Overall margin Unit margin x Quantity sold
Margin rate ((PV HT – PA HT) ÷ PA HT) x 100
Brand taxes ((PV HT – PA HT) ÷ PV HT) x 100

Application: BiblioBooks

States :

BiblioBooks, an online bookstore selling e-books, would like to evaluate its recent performance in terms of sales of two categories of books: fiction and non-fiction. Here is the sales data for each category:

  • Fiction books: Purchase price excluding VAT of €5, sale price excluding VAT of €15, and 1000 units sold.
  • Non-fiction books: Purchase price excluding VAT of €7, sale price excluding VAT of €20, and 600 units sold.

Work to do :

  1. Calculate the unit margin for each category of books.
  2. Determine the overall margin for each category of books.
  3. Calculate the markup rate for each category of books.
  4. Estimate the total turnover excluding tax for the two categories combined.
  5. Provide recommendations to optimize BiblioBooks’ online sales strategy.

Proposed correction:

  1. Unit Margin:

    For fiction books:
    Unit margin = PV excluding tax – PA excluding tax
    = €15 – €5
    = 10 €

    For non-fiction books:
    Unit margin = PV excluding tax – PA excluding tax
    = €20 – €7
    = 13 €

    The unit margin for fiction books is €10 and for non-fiction books is €13.

  2. Overall Margin:

    For fiction books:
    Overall margin = Unit margin x Quantity sold
    = €10 x 1000
    £10

    For non-fiction books:
    Overall margin = Unit margin x Quantity sold
    = €13 x 600
    £7

    The overall margin for fiction books is €10 and for non-fiction books is €000.

  3. Mark Rate:

For fiction books:
Brand rate = ((PV excluding tax – PA excluding tax) ÷ PV excluding tax) x 100
= ((€15 – €5) ÷ €15) x 100
= 66,67%

For non-fiction books:
Brand rate = ((€20 – €7) ÷ €20) x 100
= 65%

The markup rate for fiction books is 66,67%, and for non-fiction books it is 65%.

  1. Total turnover excluding tax:

    Total net sales = (Net sales of fiction x Quantity sold of fiction) + (Net sales of non-fiction x Quantity sold of non-fiction)
    = (€15 x 1000) + (€20 x 600)
    = €15 + €000
    £27

    The total turnover excluding tax for the two categories combined is €27.

  2. To maximize profitability, BiblioBooks could explore expanding its non-fiction catalog, which offers a higher unit margin, while improving digital marketing to attract a larger number of fiction readers.

Formulas Used:

Title Formulas
Unit margin PV HT – PA HT
Overall margin Unit margin x Quantity sold
Brand taxes ((PV HT – PA HT) ÷ PV HT) x 100
Total turnover excluding VAT (PV HT x Quantity sold) + (PV HT x Quantity sold)

Application: FashInnovate

States :

FashInnovate, a company specializing in the sale of eco-friendly clothing, wants to evaluate the performance of two product lines: recycled t-shirts and organic jeans. Here is the information regarding the sales of these products for the last quarter:

  • Recycled T-shirts: Purchase price excluding VAT of €12, sale price excluding VAT of €30, and 800 units sold.
  • Organic jeans: Purchase price excluding tax of €25, sale price excluding tax of €55, and 400 units sold.

Work to do :

  1. Calculate the unit margin for each product line.
  2. Estimate the net sales for each product line.
  3. Determine the overall margin for each product line.
  4. Calculate the margin rate for each product line.
  5. Offer marketing actions to increase the visibility and sales of these products.

Proposed correction:

  1. Unit Margin:

    For recycled t-shirts:
    Unit margin = PV excluding tax – PA excluding tax
    = €30 – €12
    = 18 €

    For organic jeans:
    Unit margin = PV excluding tax – PA excluding tax
    = €55 – €25
    = 30 €

    The unit margin for recycled t-shirts is €18 and for organic jeans is €30.

  2. Turnover excluding tax:

    For recycled t-shirts:
    Net sales = Net sales x Quantity sold
    = €30 x 800
    £24

    For organic jeans:
    Net sales = Net sales x Quantity sold
    = €55 x 400
    £22

    The turnover excluding tax for recycled t-shirts is €24 and for organic jeans is €000.

  3. Overall Margin:

For recycled t-shirts:
Overall margin = Unit margin x Quantity sold
= €18 x 800
£14

For organic jeans:
Overall margin = Unit margin x Quantity sold
= €30 x 400
£12

The overall margin for recycled t-shirts is €14 and for organic jeans is €400.

  1. Margin Rate:

    For recycled t-shirts:
    Margin rate = ((PV excluding tax – PA excluding tax) ÷ PA excluding tax) x 100
    = ((€30 – €12) ÷ €12) x 100
    = 150%

    For organic jeans:
    Margin rate = ((PV excluding tax – PA excluding tax) ÷ PA excluding tax) x 100
    = ((€55 – €25) ÷ €25) x 100
    = 120%

    The margin rate for recycled t-shirts is 150%, and for organic jeans it is 120%.

  2. To improve product visibility, FashInnovate could invest in social media campaigns targeting environmentally conscious consumers. It could also institute a word-of-mouth strategy by offering discounts for referrals of new customers.

Formulas Used:

Title Formulas
Unit margin PV HT – PA HT
Turnover excluding tax PV HT x Quantity sold
Overall margin Unit margin x Quantity sold
Margin rate ((PV HT – PA HT) ÷ PA HT) x 100

Application: EcoHome Design

States :

EcoHome Design, a company specializing in sustainable home decor products, evaluates its two flagship product lines: recycled wood furniture and organic accessories. Here are the sales figures for last year:

  • Recycled wood furniture: Purchase price excluding tax of €60, sale price excluding tax of €150, and 200 units sold.
  • Organic accessories: Purchase price excluding tax of €20, sale price excluding tax of €45, and 500 units sold.

Work to do :

  1. Calculate the unit margin for each product line.
  2. Calculate the net sales for each product range.
  3. Determine the overall margin for each product line.
  4. Analyze the margin rate for each product line.
  5. Based on your analysis, propose strategies to increase profitability.

Proposed correction:

  1. Unit Margin:

    For recycled wood furniture:
    Unit margin = PV excluding tax – PA excluding tax
    = €150 – €60
    = 90 €

    For organic accessories:
    Unit margin = PV excluding tax – PA excluding tax
    = €45 – €20
    = 25 €

    The unit margin for recycled wood furniture is €90 and for organic accessories is €25.

  2. Turnover excluding tax:

    For recycled wood furniture:
    Net sales = Net sales x Quantity sold
    = €150 x 200
    £30

    For organic accessories:
    Net sales = Net sales x Quantity sold
    = €45 x 500
    £22

    The turnover excluding tax for recycled wood furniture is €30 and for organic accessories is €000.

  3. Overall Margin:

For recycled wood furniture:
Overall margin = Unit margin x Quantity sold
= €90 x 200
£18

For organic accessories:
Overall margin = Unit margin x Quantity sold
= €25 x 500
£12

The overall margin for recycled wood furniture is €18 and for organic accessories is €000.

  1. Margin Rate:

    For recycled wood furniture:
    Margin rate = ((PV excluding tax – PA excluding tax) ÷ PA excluding tax) x 100
    = ((€150 – €60) ÷ €60) x 100
    = 150%

    For organic accessories:
    Margin rate = ((PV excluding tax – PA excluding tax) ÷ PA excluding tax) x 100
    = ((€45 – €20) ÷ €20) x 100
    = 125%

    The margin rate for recycled wood furniture is 150%, and for organic accessories it is 125%.

  2. EcoHome Design could optimize its profitability by focusing on strong communication on the ecological values ​​of its products to justify a possible price increase while maintaining competitiveness through quality.

Formulas Used:

Title Formulas
Unit margin PV HT – PA HT
Turnover excluding tax PV HT x Quantity sold
Overall margin Unit margin x Quantity sold
Margin rate ((PV HT – PA HT) ÷ PA HT) x 100

Application: CycleCity

States :

CycleCity, a company focused on urban bikes, wants to understand its sales performance for two models: the classic urban bike and the advanced e-bike. Here are the sales details:

  • Classic urban bike: Purchase price excluding tax of €200, sale price excluding tax of €350, and 150 units sold.
  • Advanced electric bike: Purchase price excluding VAT of €700, sale price excluding VAT of €1, and 200 units sold.

Work to do :

  1. Calculate the unit margin for each bike model.
  2. Determine the overall margin for each bike model.
  3. Calculate the markup rate for each model.
  4. Estimate the net sales for each model.
  5. Based on the analyses, suggest improvements for the sales strategy.

Proposed correction:

  1. Unit Margin:

    For the classic urban bike:
    Unit margin = PV excluding tax – PA excluding tax
    = €350 – €200
    = 150 €

    For the advanced electric bike:
    Unit margin = PV excluding tax – PA excluding tax
    = €1 – €200
    = 500 €

    The unit margin for the classic urban bike is €150 and for the advanced electric bike is €500.

  2. Overall Margin:

    For the classic urban bike:
    Overall margin = Unit margin x Quantity sold
    = €150 x 150
    £22

    For the advanced electric bike:
    Overall margin = Unit margin x Quantity sold
    = €500 x 100
    £50

    The overall margin for the classic urban bike is €22 and for the advanced electric bike is €500.

  3. Mark Rate:

For the classic urban bike:
Brand rate = ((PV excluding tax – PA excluding tax) ÷ PV excluding tax) x 100
= ((€350 – €200) ÷ €350) x 100
= 42,86%

For the advanced electric bike:
Markup rate = ((€1 – €200) ÷ €700) x 1
= 41,67%

The markup rate for the classic urban bike is 42,86%, and for the advanced electric bike it is 41,67%.

  1. Turnover excluding tax:

    For the classic urban bike:
    Net sales = Net sales x Quantity sold
    = €350 x 150
    £52

    For the advanced electric bike:
    Net sales = Net sales x Quantity sold
    = €1 x 200
    £120

    The net turnover for the classic urban bike is €52 and for the advanced electric bike is €500.

  2. To maximize sales performance, CycleCity can target promotional campaigns on the electric bike, given its wider margin, while strengthening accessibility and after-sales service to improve customer satisfaction.

Formulas Used:

Title Formulas
Unit margin PV HT – PA HT
Overall margin Unit margin x Quantity sold
Brand taxes ((PV HT – PA HT) ÷ PV HT) x 100
Turnover excluding tax PV HT x Quantity sold

Application: GourmetCafé

States :

GourmetCafé, a company that markets high-quality coffee beans, wants to evaluate its sales performance for two types of coffee: Arabica coffee and Robusta coffee. Here is the sales information for each category:

  • Arabica coffee: Purchase price excluding tax of €15 per kg, sale price excluding tax of €30 per kg, and 800 kg sold.
  • Robusta coffee: Purchase price excluding tax of €10 per kg, sale price excluding tax of €20 per kg, and 1 kg sold.

Work to do :

  1. Calculate the unit margin for each type of coffee.
  2. Determine the overall margin for each type of coffee.
  3. Calculate the margin rate for each type of coffee.
  4. Estimate the net sales for each type of coffee.
  5. Provide advice to increase sales or optimize the current strategy.

Proposed correction:

  1. Unit Margin:

    For Arabica coffee:
    Unit margin = PV excluding tax – PA excluding tax
    = €30 – €15
    = 15 €

    For Robusta coffee:
    Unit margin = PV excluding tax – PA excluding tax
    = €20 – €10
    = 10 €

    The unit margin for Arabica coffee is €15 and for Robusta coffee is €10.

  2. Overall Margin:

    For Arabica coffee:
    Overall margin = Unit margin x Quantity sold
    = €15 x 800
    £12

    For Robusta coffee:
    Overall margin = Unit margin x Quantity sold
    = €10 x 1
    £12

    The overall margin is equivalent for both types of coffee, i.e. €12.

  3. Margin Rate:

For Arabica coffee:
Margin rate = ((PV excluding tax – PA excluding tax) ÷ PA excluding tax) x 100
= ((€30 – €15) ÷ €15) x 100
= 100%

For Robusta coffee:
Margin rate = ((PV excluding tax – PA excluding tax) ÷ PA excluding tax) x 100
= ((€20 – €10) ÷ €10) x 100
= 100%

The margin rate is the same for both types of coffee, i.e. 100%.

  1. Turnover excluding tax:

    For Arabica coffee:
    Net sales = Net sales x Quantity sold
    = €30 x 800
    £24

    For Robusta coffee:
    Net sales = Net sales x Quantity sold
    = €20 x 1
    £24

    The turnover excluding tax is €24 for each type of coffee.

  2. GourmetCafé could increase its sales efforts on Arabica coffee which, despite a higher price, could benefit from a strategy of highlighting its superior quality to attract a more demanding clientele while maintaining sales volumes of Robusta coffee.

Formulas Used:

Title Formulas
Unit margin PV HT – PA HT
Overall margin Unit margin x Quantity sold
Margin rate ((PV HT – PA HT) ÷ PA HT) x 100
Turnover excluding tax PV HT x Quantity sold

Leave comments