Executive Profitability Dashboard

TV Commercial Booking Operations - legacy MBAS profit engine with validated LoiNhuan from ChiTietLichBookNgayView.

Source: dbo.ChiTietLichBookNgayView | Legacy MBAS LichBookChiTietNgayViewModel | Tháng Giêng 2023

Read-only demo - salesperson identities masked.
Gross sales 4.622.692.272
Net sales (ex-VAT) 2.823.921.694
Purchase cost (ex-VAT) 2.498.404.802
Commission 0
Profit (LoiNhuan) 325.516.891
Margin % 11,53%

Executive Profitability Dashboard for TV Commercial Booking Operations - KPIs and charts aggregate aired rows from dbo.ChiTietLichBookNgayView using the formulas below.

Gross Sales
= TongTienBan + TongTienVTUTBan
Net Sales
= TongBanSauGGBS / 1.1
Purchase Cost
= TongMuaSauGGBS / 1.1
Commission
= TongHoaHong
Profit
= TongBanSauGGBS / 1.1 - TongMuaSauGGBS / 1.1 - TongHoaHong

Source: Legacy MBAS LichBookChiTietNgayViewModel

Validated against MBAS F0010_1 export.

Source & filters

  • Legacy form: MBAS F0010_1 - export Chitiet_LNBooking.xls
  • View: dbo.ChiTietLichBookNgayView
  • Grain: one row per dbo.LichBookChiTietNgay (aired broadcast day)
  • Engine: LichBookChiTietNgay.Search() -> LichBookChiTietNgayViewModel (C# properties)

Profit row filters (confirmed airings only):

  • KyTu IS NOT NULL AND KyTu <> ''
  • StatusComplete = true
  • StatusLichBookChiTiet <> 'DELETE'

Dashboard KPI formulas

KPI Legacy expression
Gross Revenue SUM(TongTienBan + TongTienVTUTBan)Pre-discount sell incl. VTUT
Net Revenue SUM(TongBanSauGGBS / 1.1)Sell after fees/GGBS, ex-VAT
Purchase Cost SUM(TongMuaSauGGBS / 1.1)Buy after GGBS mua, ex-VAT
Commission SUM(TongHoaHong)Customer + sales commissions
Gross Profit SUM(LoiNhuan)Legacy profit per aired row
Margin % SUM(LoiNhuan) / SUM(TongBanSauGGBS/1.1) x 100Not in legacy F0010_1 - derived KPI

Financial field dictionary

Field Source Business meaning
GiaBan LichBookChiTietNgay.GiaBan Unit sell price per aired spot (QC)
GiaMua LichBookChiTietNgay.GiaMua Unit buy price per aired spot (QC) from supplier
GiamGiaBan LichBookChiTietNgay.GiamGiaBan Sales discount % on GiaBan
GiamGiaMua LichBookChiTietNgay.GiamGiaMua Purchase discount % on GiaMua
DonGiaVTUTBan LichBookChiTietNgay.DonGiaVTUTBan VTUT sell unit rate
DonGiaVTUTMua LichBookChiTietNgay.DonGiaVTUTMua VTUT buy unit rate
GiaVTUT LichBookChiTiet.GiaVTUT VTUT percentage factor on unit rates
GGBSBan LichBookChiTietNgay.GGBSBan Wholesale discount % on sell (GGBS bán)
GGBSMua LichBookChiTietNgay.GGBSMua Wholesale discount % on buy (GGBS mua)
PhiTT LichBookChiTietNgay.PhiTT Sponsorship / payment fee % (CL Tài Trợ)
LePhi LichBook.LePhi Service fee % on ThanhTienBan (Phí DV)
HoaHongKH LichBookChiTietNgay.HoaHongKH Customer commission % on TongTienBan
HoaHongSale LichBookChiTietNgay.HoaHongSale Salesperson commission % on TongTienBan
KyTu LichBookChiTietNgay.KyTu Broadcast occurrence flag - TongPost = 1 when present
StatusComplete LichBookChiTietNgay.StatusComplete Aired/completed broadcast row
StatusVTUT LichBookChiTietNgay.StatusVTUT VTUT line selected
StatusCompleteVTUT LichBookChiTietNgay.StatusCompleteVTUT VTUT line completed
StatusGiamGiaVTUT Dai.StatusGiamGiaVTUT Apply GiamGiaBan/Mua to VTUT amounts
TongTienBan Calculated TongPost x GiaBan - gross QC sell
TongTienMua Calculated TongPost x GiaMua - gross QC buy
TongTienBanSauGiamGia Calculated TongPost x GiaBan x (1 - GiamGiaBan/100)
TongTienMuaSauGiamGia Calculated TongPost x GiaMua x (1 - GiamGiaMua/100)
ThanhTienBan Calculated TongTienBanSauGiamGia + TongTienVTUTBanSauGiamGia - amount receivable from customer
ThanhTienMua Calculated TongTienMuaSauGiamGia + TongTienVTUTMuaSauGiamGia - amount payable to supplier
TTGGBSBan Calculated (TongTienBan + TongTienVTUTBan) x GGBSBan/100
TTGGBSMua Calculated (TongTienMua + TongTienVTUTMua) x GGBSMua/100
TTPhiTT Calculated (TongTienBan + TongTienVTUTBan) x PhiTT/100
TongBanSauGGBS Calculated ThanhTienBan + ThanhTienBanxLePhi/100 + TTPhiTT - TTGGBSBan - Tổng Thu KH
TongMuaSauGGBS Calculated ThanhTienMua - TTGGBSMua - Tổng Mua NCC
TongHoaHong Calculated TTHoaHongKH + TTHoaHongSale
LoiNhuan Calculated TongBanSauGGBS/1.1 - TongMuaSauGGBS/1.1 - TongHoaHong - Lãi(+), Lỗ(-)

Legacy compatibility notes

  • Profit (LoiNhuan) is computed in C# - not in SQL. MBAS binds ChiTietLichBookNgayView rows to LichBookChiTietNgayViewModel calculated properties.
  • TyLeLN does not exist in F0010_1. MVC Margin % = SUM(LoiNhuan) / SUM(TongBanSauGGBS/1.1) x 100 at dashboard aggregate level.
  • GGBS (Giảm giá bán sỉ / wholesale discount) is applied on pre-discount TongTienBan + TongTienVTUTBan, not on ThanhTienBan.
  • Commission (TongHoaHong) uses TongTienBan only - VTUT sell amount is excluded from HoaHongKH and HoaHongSale bases (legacy commented code confirms).
  • VAT divisor 1.1 is applied to TongBanSauGGBS and TongMuaSauGGBS inside LoiNhuan only - commissions are subtracted after VAT removal.

Calculation validation (Tháng Giêng 2023):

89 aired rows | SUM(LoiNhuan) = 325.516.891 | Formula check = 325.516.891 | Variance 0,000000% - within tolerance

Sample records (top |LoiNhuan| rows)

View Id Product Customer GiaBan GiaMua TongBanSauGGBS TongMuaSauGGBS TongHoaHong LoiNhuan
414436 HOẠT HUYẾT NHẤT NHẤT Gamma Group 23.563.636 23.563.636 14.138.182 4.279.156 0 8.962.750
414437 HOẠT HUYẾT NHẤT NHẤT Gamma Group 23.563.636 23.563.636 14.138.182 4.279.156 0 8.962.750
414055 TƯƠNG ỚT CHINSU Alpha Group 150.700.000 150.700.000 116.039.000 107.750.500 0 7.535.000
418466 BIA RED RUBY Alpha Group 150.700.000 150.700.000 116.039.000 107.750.500 0 7.535.000
414267 XỊT HỌNG NHẤT NHẤT NN Gamma Group 18.000.000 18.000.000 10.800.000 3.268.800 0 6.846.545

Per-row LoiNhuan = TongBanSauGGBS/1,1 - TongMuaSauGGBS/1,1 - TongHoaHong

Executive Findings Derived from ChiTietLichBookNgayView aggregates - no AI
  • Highest profit product Protected Demo Material 144.018.318 VND
  • Highest profit customer Gamma Group 206.256.319 VND
  • Highest profit channel THVL1 139.967.500 VND
  • Highest margin product Protected Demo Material 74,06%
  • Total validated profit 325.516.891 VND
Validation (Tháng Giêng 2023): 89 aired rows | Sum(LoiNhuan) = 325.516.891 | Formula check = 325.516.891 | Variance 0,0000% (tolerance < 0.01%). LoiNhuan = TongBanSauGGBS/1.1 - TongMuaSauGGBS/1.1 - TongHoaHong (MBAS LichBookChiTietNgayViewModel)
Monthly Profit Trend | January 2023 - July 2023 - Revenue, Cost, Profit (SUM(LoiNhuan)) by month

No data for this chart.

Top products by profit

No data for this chart.

Rank Product Revenue Cost Profit Margin % Bookings
1 Protected Demo Material 1.042.182.273 898.163.955 144.018.318 13,82% 4
2 Protected Demo Material 449.580.785 391.892.784 57.688.001 12,83% 4
3 Protected Demo Material 309.330.000 287.235.000 22.095.000 7,14% 3
4 Protected Demo Material 267.540.000 248.430.000 19.110.000 7,14% 2
5 Protected Demo Material 130.909.091 120.000.000 10.909.091 8,33% 2
6 Protected Demo Material 13.745.455 3.565.964 10.179.491 74,06% 1
7 Protected Demo Material 13.745.455 3.565.964 10.179.491 74,06% 1
8 Protected Demo Material 140.525.000 130.487.500 10.037.500 7,14% 2
9 Protected Demo Material 74.454.545 67.009.091 7.445.455 10,00% 2
10 Protected Demo Material 70.909.091 63.818.182 7.090.909 10,00% 1
11 Protected Demo Material 62.045.455 55.840.909 6.204.545 10,00% 1
12 Protected Demo Material 59.345.455 54.400.000 4.945.455 8,33% 1
13 Protected Demo Material 63.700.000 59.150.000 4.550.000 7,14% 1
14 Protected Demo Material 26.181.818 24.000.000 2.181.818 8,33% 2
15 Protected Demo Material 26.181.818 24.000.000 2.181.818 8,33% 1
16 Protected Demo Material 26.181.818 24.000.000 2.181.818 8,33% 1
17 Protected Demo Material 17.727.273 15.954.545 1.772.727 10,00% 1
18 Protected Demo Material 16.545.455 14.890.909 1.654.545 10,00% 1
19 Protected Demo Material 13.090.909 12.000.000 1.090.909 8,33% 1

Revenue = SUM(TongBanSauGGBS / 1.1) | Cost = SUM(TongMuaSauGGBS / 1.1) | Profit = SUM(LoiNhuan) | Bookings = DISTINCT LichBook_Id | Top 20 by profit

Top customers by profit

No data for this chart.

Rank Customer Revenue Profit Margin % Bookings
1 Gamma Group Gamma Group Highest revenue Highest profit 1.555.463.057 206.256.319 13,26% 9
2 Alpha Group Alpha Group 959.076.818 75.410.682 7,86% 13
3 Beta Group Beta Group 281.890.909 23.490.909 8,33% 8
4 Epsilon Group Epsilon Group 27.490.909 20.358.982 74,06% 2

Revenue = SUM(TongBanSauGGBS / 1.1) | Profit = SUM(LoiNhuan) | Bookings = DISTINCT LichBook_Id | Top 20 by profit

Top channels by profit - profit contribution by channel, not sales volume alone

No data for this chart.

Channel Revenue Cost Profit Margin %
THVL1 THVL1 1.959.545.000 1.819.577.500 139.967.500 7,14%
THVL2 THVL2 121.923.967 35.713.666 86.210.301 70,71%
VTV9 VTV9 500.770.909 425.600.000 75.170.909 15,01%
HTV7 HTV7 241.681.818 217.513.636 24.168.182 10,00%

Executive view - which channels generate profit, not just sales volume. Revenue = SUM(TongBanSauGGBS / 1.1) | Cost = SUM(TongMuaSauGGBS / 1.1) | Profit = SUM(LoiNhuan) | Top 20 by profit

Highest margin products - Margin % = Profit / Net revenue (ex-VAT); TyLeLN not in legacy F0010_1
Rank Product Revenue (ex-VAT) Cost (ex-VAT) Profit Margin % Bookings
1 Technology Brand A 13.745.455 3.565.964 10.179.491 74,06% 1
2 Agricultural Product A 13.745.455 3.565.964 10.179.491 74,06% 1
3 Healthcare Product A 1.042.182.273 898.163.955 144.018.318 13,82% 4
4 Healthcare Product B 449.580.785 391.892.784 57.688.001 12,83% 4
5 Beverage Brand A 74.454.545 67.009.091 7.445.455 10,00% 2
6 Technology Service A 70.909.091 63.818.182 7.090.909 10,00% 1
7 Beverage Brand B 62.045.455 55.840.909 6.204.545 10,00% 1
8 E-Commerce Store A 17.727.273 15.954.545 1.772.727 10,00% 1
9 Retail Campaign 16.545.455 14.890.909 1.654.545 10,00% 1
10 E-Commerce Campaign 130.909.091 120.000.000 10.909.091 8,33% 2
11 Personal Care Product A 59.345.455 54.400.000 4.945.455 8,33% 1
12 Consumer Product A 26.181.818 24.000.000 2.181.818 8,33% 2
13 Household Product A 26.181.818 24.000.000 2.181.818 8,33% 1
14 Automotive Brand A 26.181.818 24.000.000 2.181.818 8,33% 1
15 Seasonal Campaign A 13.090.909 12.000.000 1.090.909 8,33% 1
16 Food Product A 309.330.000 287.235.000 22.095.000 7,14% 3
17 FMCG Product A 267.540.000 248.430.000 19.110.000 7,14% 2
18 Beverage Brand C 140.525.000 130.487.500 10.037.500 7,14% 2
19 Healthcare Product C 63.700.000 59.150.000 4.550.000 7,14% 1
Top suppliers by profit
Supplier Revenue Cost Profit Margin %
Regional TV Network Regional TV Network 2.081.468.967 1.855.291.166 226.177.801 10,87%
National TV Network National TV Network 500.770.909 425.600.000 75.170.909 15,01%
Media Partner Group Media Partner Group 241.681.818 217.513.636 24.168.182 10,00%

Supplier = TenNCC from dbo.ChiTietLichBookNgayView (legacy MBAS) | Revenue = SUM(TongBanSauGGBS / 1.1) | Cost = SUM(TongMuaSauGGBS / 1.1) | Profit = SUM(LoiNhuan) | Top 20 by profit

Top salespersons by profit - demo aliases (Demo1, Demo2, ...) by profit rank

No data for this chart.