38. retail sales pl template proofread
TRANSCRIPT
-
8/12/2019 38. Retail Sales PL Template Proofread
1/13
PROFIT AND LOSS INSTRUCTIONS
This Profit & Loss Statement (P&L) is for two products manufactured by the same company in a retail salesmodel. The product flows through the supply chain as follows: Manufacturer --> Distributor --> Retailer-->
Customer. Instructions on how to fill out the P&L statement are broken down by the tabs below.
Assumptions
Input data only in cells with blue characters. Fill in the data as accurately as possible, as this information is used
in the Retailers and Summary tabs.
Retailers
1. Input data only in cells with blue characters. All areas in black will calculate automatically. Not all blue cellsrequire inputs. If this is the case, simply enter nothing in the cell (but do not delete the line, as it will affect the
formulas).
a. Input name of retailer #1 where it says "Retailer #1" and repeat for every retailer.
b. Input the number of units sold for Product A and Product B, per retailer.
2. The Retailers tab pushes data to the Summary tab. The spreadsheet has been set up to anticipate 5 retailers.
In order to add more retailers, simply scroll down to the last remaining retailer (#5), copy the box above it and
insert below, i.e., Retailer #5 gets copied and inserted to make Retailer #6. Then link the relevant cells into the
"Group" on the corresponding Summary tab in order to accurately roll up the total.
Summary
No inputs are necessary on this tab, as the data inputted in Assumptions, Retailers, and Line Items tabs are
pushed into this tab.
Line Items
Distributor's Expenses
1. Input data only in cells with blue characters. All areas in black will calculate automatically. This tab pushesdata to the Summary tab under Distributor's Administrative Expenses. Not all blue cells require inputs. If this is
the case, simply enter nothing in the cell (but do not delete the line, as it will affect the formulas). Enter as a
negative number.
a. Distribution Expenses: Input the costs associated with transporting inventory from the manufacturer to the
retailers and any business-related travel for personnel.
b. Personnel Expenses: Input the costs associated with Sales Manager and other personnel (not salespeople)
c. Company Administration: Input the amount spent on rent, supplies, etc.
-
8/12/2019 38. Retail Sales PL Template Proofread
2/13
Manufacturer's Expenses1. Input data only in cells with blue characters. All areas in black will calculate automatically. This tab pushes
data to the Summary tab under Manufacturer's Marketing and Administrative Expenses. Not all blue cells
require inputs. If this is the case, simply enter nothing in the cell (but do not delete the line, as it will affect the
formulas). Enter as a negative number.
a. Marketing Expenses: Input the above the line and below the line marketing activity names and their
associated costs.
Above-the-line marketing activities are broadcasts to mass audiences such as through TV, radio, and
newspapers and can reach a larger audience quickly. It is harder to tailor the messages or measure the return
on investment (how effective are these activities at reaching your targeted audience?) compared with below-
the-line marketing.
Below-the-line marketing can be tailored to a specific group and includes items such as leaflets, coupons, gifts
with purchase, and point-of-sale displays. It is much easier to measure the return on investment of these
activities. For example, if you offer coupons to customers, it is easy to count how many coupons were
redeemed per store. You can measure the change in sales during the months you offer the coupon compared
with the months that you did not offer that promotion to see if the increase in revenues is worth the cost.
b. Distribution Expenses: Input any costs associated with transporting the inventory and any business-related
travel for personnel.
c. Personnel Expenses: Input the costs associated with Sales Manager and other personnel (not salespeople)
d. Company Administration: Input the amount spent on rent, supplies, etc.
2012
-
8/12/2019 38. Retail Sales PL Template Proofread
3/13
P&L ASSUMPTIONS
ASSUMPTIONS
Currency KH Riel The currency of the country where you are conducting business.
Exchange Rate 4,200
Product A Retail Price/Unit 120,000 The price that the manufacturer suggests the retailer should charge the customer for Product A.
Product A Distributor Price/Unit 100,000 The price that the distributor charges the retailer for Product A.
Product A Manufacturer Price/Unit 80,000 The price that the manufacturer charges the distributor for Product A.
Product A Manufacturer Cost/Unit 60,000 The cost for the manufacturer to produce Product A.
Product B Retail Price/Unit 150,000 The price that the manufacturer suggests the retailer should charge the customer for Product B.
Product B Distributor Price/Unit 130,000 The price that the distributor charges the retailer for Product B.
Product B Manufacturer Price/Unit 115,000 The price that the manufacturer charges the distributor for Product B.
Product B Manufacturer Cost/Unit 90,000 The cost for the manufacturer to produce Product B.
Value Added Tax (Retailer) 10.0% VAT is the tax on the purchase price for customers. The rate varies from country to country.Value Added Tax (Manufacturer) 10.0% VAT is applied once "value" has been added to the product at the production stage, which is when
all the supplies are assembled to create the final product. The rate varies from country to country.
If you need to report to stakeholders in different countries, reporting amounts in their currency
will help them understand the profitability of your business better. For example, if your business is
based in Cambodia but you are receiving funding from a donor in the United States, you should
report amounts converted from Cambodian Riel to US Dollars.
-
8/12/2019 38. Retail Sales PL Template Proofread
4/13
RETAILERS P&L
Retailer #1 January February March April May June July August September October November December
PRODUCT A unit sales 120
PRODUCT B unit sales 175
Product A Revenue 14,400,000 0 0 0 0 0 0 0 0 0 0 0 14
Less: Product Cost (12,000,000) 0 0 0 0 0 0 0 0 0 0 0 (12
Gross Profit 2,400,000 0 0 0 0 0 0 0 0 0 0 0 2,
Gross Margin % 17%
Price/Unit 120,000 1
Profit/Unit 20,000
Product B Revenue 26,250,000 0 0 0 0 0 0 0 0 0 0 0 26
Less: Product Cost (22,750,000) 0 0 0 0 0 0 0 0 0 0 0 (22
Gross Profit 3,500,000 0 0 0 0 0 0 0 0 0 0 0 3,
Gross Margin % 13%
Price/Unit 150,000 1
Profit/Unit 20,000
Less: Value Added Tax (4,065,000) 0 0 0 0 0 0 0 0 0 0 0 (4
Total Gross Profit 1,835,000 0 0 0 0 0 0 0 0 0 0 0 1,
Gross Margin % 5%
Retailer #2 January February March April May June July August September October November December
PRODUCT A unit sales 50
PRODUCT B unit sales 85
Product A Revenue 6,000,000 0 0 0 0 0 0 0 0 0 0 0 6,Less: Product Cost (5,000,000) 0 0 0 0 0 0 0 0 0 0 0 (5
Gross Profit 1,000,000 0 0 0 0 0 0 0 0 0 0 0 1,
Gross Margin % 17%
Price/Unit 120,000 1
Profit/Unit 20,000
Product B Revenue 12,750,000 0 0 0 0 0 0 0 0 0 0 0 12
Less: Product Cost (11,050,000) 0 0 0 0 0 0 0 0 0 0 0 (11
Gross Profit 1,700,000 0 0 0 0 0 0 0 0 0 0 0 1,
Gross Margin % 13%
Price/Unit 150,000 1
Profit/Unit 20,000
Input the number of units sold of each product for each retailer and the individual margins and profits will be automatically calculated based on the amounts listed in the Assumptions tab. Thesesummarized in the Group at the bottom. Doing this monthly allows you to see the retailers performance over time to better understand their individual sales trends and performance. This tells
and worst performing retailers are and allows you to follow up with them to better understand why and improve performance across all retail outlets.
-
8/12/2019 38. Retail Sales PL Template Proofread
5/13
Retailer #3 January February March April May June July August September October November December
PRODUCT A unit sales 45
PRODUCT B unit sales 95
Product A Revenue 5,400,000 0 0 0 0 0 0 0 0 0 0 0
Less: Product Cost (4,500,000) 0 0 0 0 0 0 0 0 0 0 0
Gross Profit 900,000 0 0 0 0 0 0 0 0 0 0 0
Gross Margin % 17%
Price/Unit 120,000
Profit/Unit 20,000
Product B Revenue 14,250,000 0 0 0 0 0 0 0 0 0 0 0Less: Product Cost (12,350,000) 0 0 0 0 0 0 0 0 0 0 0
Gross Profit 1,900,000 0 0 0 0 0 0 0 0 0 0 0
Gross Margin % 13%
Price/Unit 150,000
Profit/Unit 20,000
Less: Value Added Tax (1,965,000) 0 0 0 0 0 0 0 0 0 0 0
Total Gross Profit 835,000 0 0 0 0 0 0 0 0 0 0 0
Gross Margin % 4%
Retailer #4 January February March April May June July August September October November December
PRODUCT A unit sales 78
PRODUCT B unit sales 110
Product A Revenue 9,360,000 0 0 0 0 0 0 0 0 0 0 0
Less: Product Cost (7,800,000) 0 0 0 0 0 0 0 0 0 0 0
Gross Profit 1,560,000 0 0 0 0 0 0 0 0 0 0 0
Gross Margin % 17%
Price/Unit 120,000
Profit/Unit 20,000
Product B Revenue 16,500,000 0 0 0 0 0 0 0 0 0 0 0
Less: Product Cost (14,300,000) 0 0 0 0 0 0 0 0 0 0 0
Gross Profit 2,200,000 0 0 0 0 0 0 0 0 0 0 0
Gross Margin % 13%
Price/Unit 150,000
Profit/Unit 20,000
Less: Value Added Tax (2,586,000) 0 0 0 0 0 0 0 0 0 0 0
Total Gross Profit 1,174,000 0 0 0 0 0 0 0 0 0 0 0
Gross Margin % 5%
-
8/12/2019 38. Retail Sales PL Template Proofread
6/13
Retailer #5 January February March April May June July August September October November December
PRODUCT A unit sales 100
PRODUCT B unit sales 202
Product A Revenue 12,000,000 0 0 0 0 0 0 0 0 0 0 0
Less: Product Cost (10,000,000) 0 0 0 0 0 0 0 0 0 0 0
Gross Profit 2,000,000 0 0 0 0 0 0 0 0 0 0 0
Gross Margin % 17%
Price/Unit 120,000
Profit/Unit 20,000
Product B Revenue 30,300,000 0 0 0 0 0 0 0 0 0 0 0Less: Product Cost (26,260,000) 0 0 0 0 0 0 0 0 0 0 0
Gross Profit 4,040,000 0 0 0 0 0 0 0 0 0 0 0
Gross Margin % 13%
Price/Unit 150,000
Profit/Unit 20,000
Less: Value Added Tax (4,230,000) 0 0 0 0 0 0 0 0 0 0 0
Total Gross Profit 1,810,000 0 0 0 0 0 0 0 0 0 0 0
Gross Margin % 4%
RETAILER GROUP January February March April May June July August September October November December
PRODUCT A unit sales 393 0 0 0 0 0 0 0 0 0 0 0
PRODUCT B unit sales 667 0 0 0 0 0 0 0 0 0 0 0
Product A Revenue 47,160,000 0 0 0 0 0 0 0 0 0 0 0
Less: Product Cost (39,300,000) 0 0 0 0 0 0 0 0 0 0 0
Gross Profit 7,860,000 0 0 0 0 0 0 0 0 0 0 0
Gross Margin % 17%
Price/Unit 120,000
Profit/Unit 20,000
Product B Revenue 100,050,000 0 0 0 0 0 0 0 0 0 0 0
Less: Product Cost (86,710,000) 0 0 0 0 0 0 0 0 0 0 0
Gross Profit 13,340,000 0 0 0 0 0 0 0 0 0 0 0
Gross Margin % 13%
Price/Unit 150,000
Profit/Unit 20,000
Less: Value Added Tax (14,721,000) 0 0 0 0 0 0 0 0 0 0 0
Total Gross Profit 6,479,000 0 0 0 0 0 0 0 0 0 0 0
Gross Margin % 4%
-
8/12/2019 38. Retail Sales PL Template Proofread
7/13
SUMMARY P&L
RETAILER GROUP January February March April May June July August September October Novem
PRODUCT A
Unit Sales 393 0 0 0 0 0 0 0 0 0 0
Revenues 47,160,000 0 0 0 0 0 0 0 0 0 0
Less: Product Cost (39,300,000) 0 0 0 0 0 0 0 0 0 0
Product A Gross Profit 7,860,000 0 0 0 0 0 0 0 0 0 0
Gross Margin % 17%
PRODUCT B
Unit Sales 667 0 0 0 0 0 0 0 0 0 0
Revenues 100,050,000 0 0 0 0 0 0 0 0 0 0
Less: Product Cost (86,710,000) 0 0 0 0 0 0 0 0 0 0
Product B Gross Profit 13,340,000 0 0 0 0 0 0 0 0 0 0Gross Margin % 13%
Less: Value Added Tax (14,721,000) 0 0 0 0 0 0 0 0 0 0
Total Gross Profit 6,479,000 0 0 0 0 0 0 0 0 0 0
Gross Margin % 4%
-
8/12/2019 38. Retail Sales PL Template Proofread
8/13
DISTRIBUTOR January February March April May June July August September October Novem
PRODUCT A
Unit Sales 393 0 0 0 0 0 0 0 0 0 0
Revenues 39,300,000 0 0 0 0 0 0 0 0 0 0
Less: Product Cost (31,440,000) 0 0 0 0 0 0 0 0 0 0
Product A Gross Profit 7,860,000 0 0 0 0 0 0 0 0 0 0Gross Margin % 20%
PRODUCT B
Unit sales 667 0 0 0 0 0 0 0 0 0 0
Revenues 86,710,000 0 0 0 0 0 0 0 0 0 0
Less: Product Cost (76,705,000) 0 0 0 0 0 0 0 0 0 0
Product B Gross Profit 10,005,000 0 0 0 0 0 0 0 0 0 0
Gross Margin % 12%
Less: Administrative Expenses (5,000,000) 0 0 0 0 0 0 0 0 0 0
Distributor Net Profit 12,865,000 0 0 0 0 0 0 0 0 0 0
Net Margin % 72%
Price/Unit 118,877
Profit/Unit 12,137
-
8/12/2019 38. Retail Sales PL Template Proofread
9/13
MANUFACTURER January February March April May June July August September October Novem
PRODUCT A
Unit Sales 393 0 0 0 0 0 0 0 0 0 0
Revenues 31,440,000 0 0 0 0 0 0 0 0 0 0
Less: Product Cost (23,580,000) 0 0 0 0 0 0 0 0 0 0
Product A Gross Profit 7,860,000 0 0 0 0 0 0 0 0 0 0Gross Margin % 25%
PRODUCT B
Unit sales 667 0 0 0 0 0 0 0 0 0 0
Revenues 76,705,000 0 0 0 0 0 0 0 0 0 0
Less: Product Cost (60,030,000) 0 0 0 0 0 0 0 0 0 0
Product B Gross Profit 16,675,000 0 0 0 0 0 0 0 0 0 0
Gross Margin % 22%
Total Gross Profit 24,535,000 0 0 0 0 0 0 0 0 0 0
Gross Margin % 23%
Less: Value Added Tax (2,453,500) 0 0 0 0 0 0 0 0 0 0
Less: Marketing Expenses (2,800,000) 0 0 0 0 0 0 0 0 0 0
Less: Administrative Expenses (3,950,000) 0 0 0 0 0 0 0 0 0 0
(6,750,000) 0 0 0 0 0 0 0 0 0 0
Manufacturer Net Profit 15,331,500 0 0 0 0 0 0 0 0 0 0
Net Margin % 14%Profit/Unit 14,464
-
8/12/2019 38. Retail Sales PL Template Proofread
10/13
Dollars
$11,229
($9,357)
$1,871
$23,821
($20,645)
$3,176
($3,505)
$1,543
-
8/12/2019 38. Retail Sales PL Template Proofread
11/13
Dollars
$9,357
($7,486)
$1,871
$20,645
($18,263)
$2,382
($1,190)
$3,063
$28
$3
-
8/12/2019 38. Retail Sales PL Template Proofread
12/13
Dollars
$7,486
($5,614)
$1,871
$18,263
($14,293)
$3,970
$5,842
($584)
($940)
($1,607)
$3,650
$3
-
8/12/2019 38. Retail Sales PL Template Proofread
13/13
DISTRIBUTOR'S EXPENSES
ADMINISTRATIVE EXPENSES January February March April May June July August September October November December Year 1 Dollars
Distribution expenses (2,500,000) 0 0 0 0 0 0 0 0 0 0 0 (2,500,000) ($595)
Personnel expenses (1,500,000) 0 0 0 0 0 0 0 0 0 0 0 (1,500,000) ($357)Company administration (1,000,000) 0 0 0 0 0 0 0 0 0 0 0 (1,000,000) ($238)
Total Administrative Expenses (5,000,000) 0 0 0 0 0 0 0 0 0 0 0 (5,000,000) ($1,190)
MANUFACTURER'S EXPENSES
MARKETING EXPENSES January February March April May June July August September October November December Year 1 Dollars
Above-the-Line (ATL)
Radio spot (1,000,000) 0 0 0 0 0 0 0 0 0 0 0 (1,000,000) ($238)
Loudspeaker announcements (450,000) 0 0 0 0 0 0 0 0 0 0 0 (450,000) ($107)
Newspaper advertisement (400,000) 0 0 0 0 0 0 0 0 0 0 0 (400,000) ($95)
Total ATL Expenses (1,850,000) 0 0 0 0 0 0 0 0 0 0 0 (1,850,000) ($440)
Below-the-Line (ATL)
Point-of-sale displays (650,000) 0 0 0 0 0 0 0 0 0 0 0 (650,000) ($155)
Gifts with purchase (300,000) 0 0 0 0 0 0 0 0 0 0 0 (300,000) ($71)
Total BTL Expenses (950,000) 0 0 0 0 0 0 0 0 0 0 0 (950,000) ($226)
Total Marketing Expenses (2,800,000) 0 0 0 0 0 0 0 0 0 0 0 (2,800,000) ($667)
ADMINISTRATIVE EXPENSES January February March April May June July August September October November December Year 1 Dollars
Distribution expenses (450,000) 0 0 0 0 0 0 0 0 0 0 0 (450,000) ($107)
Personnel expenses (2,000,000) 0 0 0 0 0 0 0 0 0 0 0 (2,000,000) ($476)Company administration (1,500,000) 0 0 0 0 0 0 0 0 0 0 0 (1,500,000) ($357)
Total Administrative Expenses (3,950,000) 0 0 0 0 0 0 0 0 0 0 0 (3,950,000) ($940)
Retail Sales Profit and Loss Template
Distribution expenses include the costs associated with transporting the inventory from the manufacturer to the retailers and any business-related travel for personnel.
Personnel expenses include the costs associated with the Sales Manager and other personnel (not salespeople).
Company administration include the costs associated with rent, supplies, etc.
Above-the-line marketing activities are broadcasts to mass audiences such as through TV, radio, and newspapers and can reach a larger audience quickly. It is harder to tailor the messages or
measure the return on investment (how effective these activities are at reaching your targeted audience) compared with below the l ine marketing.
Below-the-line marketing can be tailored to a specific group and includes items such as leaflets, coupons, gifts with purchase, and point-of-sale displays. It is much easier to measure the return on
investment of these activities. For example, if you offer coupons to customers, it is easy to count how many coupons were redeemed per store. You can measure the change in sales during themonths you offer the coupon and compare with the months that you did not offer that promotion to see if the increase in revenues is worth the cost.
Distribution expenses include the costs associated with transporting the inventory and any business-related travel for personnel.
Personnel expenses include the costs associated with the sales manager and other personnel (not salespeople).
Company administration include the costs associated with rent, supplies, etc.