Help -
Help for Webmasters
« back to results for ""
Below is a cache of http://www.hardinet.org/pdf/Bates07.pdf. It's a snapshot of the page taken as our search engine crawled the Web.
The web site itself may have changed. You can check the current page or check for previous versions at the Internet Archive. Yahoo! is not affiliated with the authors of this page or responsible for its content. Microsoft PowerPoint - hardi07 Customer Profitability:
Management & Action Prepared For HARDI Prepared By The Profit Planning Group 1790 38th St., Suite 204 Boulder, CO 80301 303-444-6212 info@profitplanninggroup.com October 8, 2007 Profit Planning Group Exhibit 1 Objectives of the Session Review the Profit Structure of the Industry Identify the Key Profit Pressure Points for Distributors Examine How it Might be Possible to Manage Customers Profit Planning Group Exhibit 2 Financial Results for Mountain View, Inc. Net Sales 30,000,000 Cash 115,000 Cost of Goods Sold 21,900,000 Accounts Receivable 3,698,630 Gross Margin 8,100,000 Inventory 6,441,176 Payroll & Fringes 4,500,000 Other Current Assets 57,500 All Other Expenses 2,550,000 Total Current Assets 10,312,307 Total Expenses 7,050,000 Fixed Assets 1,187,693 Profit Before Taxes 1,050,000 Total Assets 11,500,000 Income Taxes 315,000 Profit After Taxes 735,000 Accounts Payable 1,800,000 Return on Assets = Profit Before Taxes/Total Assets Income Statement Balance Sheet Profit Planning Group Exhibit 3 The Recent Trend in Return on Assets 0 2 4 6 8 10 2001 2002 2003 2004 2005 2006 Year R
O
A
-
-
% Profit Planning Group Exhibit 4 ROA by Industry 0.0 5.0 10.0 15.0 20.0 25.0 30.0 M
e
d
i
a
n
R
O
A
-
-
% Profit Planning Group Exhibit 5 The Three Issues That Distributors Need to Worry About With Regard to Customers Profit Planning Group Exhibit 6 The Personnel Productivity Ratio--PPR The Percent of Each Gross Margin Dollar That Must Be Devoted to Payroll Salaries and Fringe Benefits Gross Margin = 4,500,000
8,100,000 = Profit Planning Group Exhibit 7 The Profitable Sales Response: A Moderate Growth Plan Summary Percent Income Statement Current Potential Change Net Sales 30,000,000 31,500,000 5.0 Cost of Goods 21,900,000 22,995,000 5.0 Gross Margin 8,100,000 8,505,000 5.0 Payroll & Fringes 4,500,000 4,554,900 1.2 All Other Expenses 2,550,000 2,677,500 5.0 Total Expenses 7,050,000 7,232,400 2.6 Profit 1,050,000 1,272,600 21.2 Profit Planning Group Exhibit 8 Sales Volume Sensitivity: A Negative View Summary Percent Income Statement Current Potential Change Net Sales 30,000,000 31,500,000 5.0 Cost of Goods 21,900,000 22,995,000 5.0 Gross Margin 8,100,000 8,505,000 5.0 Payroll and Fringes 4,500,000 4,895,100 8.8 All Other Expenses 2,550,000 2,677,500 5.0 Total Expenses 7,050,000 7,572,600 7.4 Profit 1,050,000 932,400 -11.2 PPR 55.6% 57.6% Other 8.5% 8.5% Profit Planning Group Exhibit 9 The Relationship Between a PPR Reduction and Return on Assets 0.0 5.0 10.0 15.0 20.0 55.6 53.6 51.6 49.6 47.6 45.6 PPR R
O
A
-
-
% Profit Planning Group Exhibit 10 Profitability in Relationship to Workload Total Firm Order Line Customer Net Sales 30,000,000 300.00 100.00 24,000 Gross Margin 8,100,000 81.00 27.00 6,480 Total Expenses 7,050,000 70.50 23.50 5,640 Profit Before Taxes 1,050,000 Profit Planning Group Exhibit 11 Velocity Code Analysis for Customers A 15% B 15 C 35 D 35 Customers Profits Percentage Mix of Profit Planning Group Exhibit 12 How Customers Impact Profitability Inadequate Gross Margin Inadequate Sales Volume Excessive Cost of Servicing the Account Excessive Sales Costs Slow Payment Practices Profit Planning Group Exhibit 13 The Two Global Options for Improving Customer Profitability Fire em Change Their Behavior Patterns Profit Planning Group Exhibit 14 Who Do We Blame? Sales Generation Us:
Them: Cherry Picking Gross Margin Us:
Them: Still Cherry Picking Expenses Us:
Them: Over Ordering Accounts Rec. Us:
Them: Possible Lack of Concern Profit Planning Group Exhibit 15 Dont Ever Forget This Exhibit: The Increase in Sales Required Offset a Price Cut 0 10 20 30 40 50 60 0 1 2 3 4 5 6 7 8 9 10 Price Reduction--% I
n
c
r
e
a
s
e
i
n
D
o
l
l
a
r
S
a
l
e
s
-
-
% Profit Planning Group Exhibit 16 The Relationship Between Gross Margin and Return on Assets 0.0 5.0 10.0 15.0 20.0 27.0 28.0 29.0 30.0 31.0 Gross Margin--% R
O
A
-
-
% Profit Planning Group Exhibit 17 The Impact of Gross Margin on the PPR 46.0 48.0 50.0 52.0 54.0 56.0 27.0 28.0 29.0 30.0 31.0 Gross Margin--% P
P
R
-
-
% Profit Planning Group Exhibit 18 Why Does the PPR Fall? Summary Percent Income Statement Current Potential Change Net Sales 30,000,000 30,000,000 0.0 Cost of Goods 21,900,000 21,600,000 -1.4 Gross Margin 8,100,000 3.7 Payroll & Fringes 4,500,000 4,500,000 0.0 All Other Expenses 2,550,000 2,550,000 0.0 Total Expenses 7,050,000 7,050,000 0.0 Profit 1,050,000 28.6 Gross Margin 27.0% 28.0% PPR 55.6% 53.6% Profit Planning Group Exhibit 19 Impacting Gross Margin With Product Mix Velocity Gross Potential Gross Code Sales GM% Margin Sales Margin A 18,000,000 20.0 3,600,000 18,000,000 3,600,000 B 6,000,000 30.0 1,800,000 6,000,000 1,800,000 C 4,500,000 40.0 1,800,000 4,500,000 1,800,000 D 1,500,000 60.0 900,000 Total 30,000,000 27.0 8,100,000 Profit Planning Group Exhibit 20 Impacting Gross Margin With Blind-Item Pricing Velocity Gross Potential Gross Code Sales GM% Margin Sales Margin A 18,000,000 20.0 3,600,000 18,000,000 3,600,000 B 6,000,000 30.0 1,800,000 6,000,000 1,800,000 C 4,500,000 40.0 1,800,000 4,500,000 1,800,000 D 1,500,000 60.0 900,000 Total 30,000,000 27.0 8,100,000 Profit Planning Group Exhibit 21 The Excel Template Company Section Series of Total Firm Entries Following the PAR Survey Customer Section Customer by Customer Input Profit Planning Group Exhibit 22 A Typical Set of Company Input Factors: Following the PAR Format Net Sales $20,000,000 Cost of Goods Sold ( Including Freight-in, Less Purchase Discounts) $14,600,000 Gross Profit (Calculates Automatically) $5,400,000 Operating Expenses $4,600,000 Operating Profit (Calculates Automatically) $800,000 Interest Expense $200,000 Average Accounts Receivable $2,400,000 This Section Will Allocate Your Operating Expenses, Which Were: $4,600,000 Payroll Expenses
Executive Salaries and Bonuses $400,000 Sales Executive Salaries/Commissions & Bonuses (Include Branch Managers) 220,000 Outside Sales Salaries/Commissions & Bonuses 440,000 Inside Sales Salaries/Commissions & Bonuses (Include Counter Sales) 560,000 Warehouse Salaries/Wages & Bonuses 340,000 Delivery Salaries/Wages & Bonuses 140,000 All Other Salaries/Wages & Bonuses (Office, G & A., etc.) 420,000 Total Salaries/Wages/Commissions & Bonuses (Calculates Automatically) $2,520,000 A. Summary Information From Your PAR Questionnaire B. Operating Expense Breakouts Profit Planning Group Exhibit 23 A Typical Set of Company Input Factors: Pushing Beyond the PAR Format Number of Customers 1,250 All of the Following Are For a Full Year Total Number of Customer Orders 85,000 Total Number of Customer Order Lines 225,000 Number of Deliveries to Customers 30,000 Number of Customer Return Lines 2,500 A. Customers and Orders Profit Planning Group Exhibit 24 Customer Analysis: Different Sophistication Levels to Chose From Sales Yes Gross Margin Yes Number of Orders Yes Number of Order Lines Yes Note Number of Return Lines Yes Use the Drop Down Boxes to Set These Number of Deliveries Yes Items to Either Yes or No Accounts Receivable Yes Worksheet Five: Analysis Set Up These Factors Will Be Used In Assigning Costs to Customers Profit Planning Group Exhibit 25 Customer Input: Moving Beyond PAR Somewhat 1. You may enter information for up to 1,500 different customers in this worksheet.
2. All data must be for a full year.
3. All information MUST be entered in the proper file sequence, starting in cell B20.
4. Information must be entered in every cell. If you don't track and item by customer, enter zero.
5. You may enter data by hand or by pasting from another Excel spreadsheet, an Access database, etc.
6. It is not possible to sort the information once it is entered here. This will be done on later sheets.
7. There are comments associated with some of the cells to help.
8. Before entering any data for a new group of customers, you MUST click on the button below to delete prior information that may get in the way of the new analysis. Note! The delete button runs a macro. Your Excel macros must be enabled for it to work. Customer Customer Net Gross Outside Accounts Number of Number of Number of Number of Number Name Sales Margin Comm? Rec. Orders Order Lines Deliveries Return Lines 58 Typical Customer 16000 4000 1 1920 68 180 24 2 a68 Nice Profit 10000 5000 1 1000 34 150 24 5 29-14j Loser 5000 1500 0 200 34 200 0 2 Worksheet Seven: Data Entry for Individual Customers Click here to delete ALL customers Profit Planning Group Exhibit 26 Looking at Customers as a Group Sort Settings (click each one to change setting) Column Profit Percent Order Ascending Customer Number Customer Name Sales Gross Margin Direct Expenses Overhead Expenses Profit Gross Margin Percent Direct Expenses Percent Overhead Expenses Percent Profit Percent 29-14j Loser 5,000 1,500 728 1,187 -415 30.0 14.6 23.7 -8.3 58 Typical Customer 16,000 4,000 1,591 2,156 253 25.0 9.9 13.5 1.6 a68 Nice Profit 10,000 5,000 1,052 1,628 2,320 50.0 10.5 16.3 23.2 --------------------Dollar Figures-------------------- ------------Percent of Sales------------ Note! The sort button runs a macro. Your Excel macros must be ENABLED or the button will not work. Click here to sort customers using the sort settings Profit Planning Group Exhibit 27 Looking At Customers Individually Customer Selection Dollars Percent Net Sales 16,000 100.0 Cost of Goods Sold 12,000 75.0 Gross Margin 4,000 25.0 Direct Expenses 1,591 9.9 Contribution Margin 2,409 15.1 Overhead Expenses 2,156 13.5 Net Profit 253 1.6 Variables Current Target Net Sales 16,000 $ 16,000 Gross Margin Percentage 25.0 26.0 % Number of Orders 68 60 Number of Order Lines 180 180 Number of Deliveries 24 18 Number of Returns 2 2 Days Sales Outstanding 43.8 43.8 Days Net Profit 253 600 Typical Customer A Customer Profitability Overview "What If" Analysis for This Customer Profit Planning Group Exhibit 28 The Major Expense Distinction Direct Expenses Clearly associated with an individual customer
Examples: commissions, delivery costs Indirect Expenses Incurred for the total firm Examples: warehouse rent, management
salaries Dollars Percent Dollars Percent Net Sales $16,000 100.0 $16,000 100.0 Cost of Goods Sold 12,000 75.0 11,840 74.0 Gross Margin 4,000 25.0 4,160 26.0 Direct Expenses Orders 64 0.4 57 0.4 Order Lines 161 1.0 161 1.0 Deliveries 272 1.7 204 1.3 Returns 32 0.2 32 0.2 Inside Sales 530 3.3 467 2.9 Outside Sales 416 2.6 367 2.3 Accounts Receivable Interest 115 0.7 115 0.7 Total Direct Expenses 1,591 9.9 1,404 8.8 Contribution Margin 2,409 15.1 2,756 17.2 Overhead Expenses Sales Based Overhead 1,410 8.8 1,410 8.8 Per Customer Overhead 746 4.7 746 4.7 Total Overhead Expenses 2,156 13.5 2,156 13.5 Net Profit $253 1.6 $600 3.7 Sales Increase of 0.0% Gross Margin Percentage 25.0% 26.0% Number of Orders 68 60 Number of Order Lines 180 180 Number of Deliveries 24 18 Number of Return Lines 2 2 Days Sales Outstanding 44 44 Current Potential Typical Customer Profitability Analysis for The Following Changes Were Made For The Potential Analysis increases to stays at decreases to stays at decreases to stays at Profit Planning Group Exhibit 29 The Mandate for Change During Each of the Next Five Years Sales Increase % 5.0 Gross Margin % 0.3 PPR -2.0 Other Expense % -0.1 Inventory Turnover 0.0 AR Collection Days 0.0 Profit Planning Group Exhibit 30 The Impact of Making The Mandated Changes 2007 2008 2012 Net Sales 30,000,000 31,500,000 38,288,447 Cost of Goods 21,900,000 22,900,500 27,376,240 Gross Margin 8,100,000 8,599,500 10,912,207 Payroll & Fringes 4,500,000 4,605,510 4,971,117 All Other Expenses 2,550,000 2,646,000 3,063,076 Total Expenses 7,050,000 7,251,510 8,034,192 Profit 1,050,000 1,347,990 2,878,015 Cash 115,000 120,750 146,772 Accounts Receivable 3,698,630 3,883,562 4,720,493 Inventory 6,441,176 6,735,441 8,051,835 Other Current Assets 57,500 57,500 57,500 Total Current Assets 10,312,307 10,797,253 12,976,601 Fixed Assets 1,187,693 1,247,078 1,515,831 Total Assets 11,500,000 12,044,331 14,492,432 Return on Assets 9.1 11.2 Profit Planning Group Exhibit 31 Summary and Conclusions A PDF file of this seminar will be available for one month on our web site: www.profitplanninggroup.com Go to the seminars section Profit Planning Group Exhibit 32 Hes Back! Dr. Albert Bates is founder and Chief Sophist (whatever that means) of the Profit Planning Group, a research and education firm headquartered in
Boulder, Colorado. The firm works mostly in the area of financial planning. He makes approximately 100 presentations each year on topics such as Improving the Bottom Line, Doing More With Less and Pricing for Profit. He
also heads the firms investigation into profitability research for over fifty
different trade associations. Al received his doctorate from Indiana University. He is married and has three daughters. All four of the ladies in his life have black belts in Tae Kwon Do,
so dont criticize his presentation too much. He can be reached at Profit Planning Group, 303-444-6212, 1790 38th St., Suite 204, Boulder, CO 80301, info@profitplanninggroup.com.
The web site itself may have changed. You can check the current page or check for previous versions at the Internet Archive. Yahoo! is not affiliated with the authors of this page or responsible for its content. Microsoft PowerPoint - hardi07 Customer Profitability:
Management & Action Prepared For HARDI Prepared By The Profit Planning Group 1790 38th St., Suite 204 Boulder, CO 80301 303-444-6212 info@profitplanninggroup.com October 8, 2007 Profit Planning Group Exhibit 1 Objectives of the Session Review the Profit Structure of the Industry Identify the Key Profit Pressure Points for Distributors Examine How it Might be Possible to Manage Customers Profit Planning Group Exhibit 2 Financial Results for Mountain View, Inc. Net Sales 30,000,000 Cash 115,000 Cost of Goods Sold 21,900,000 Accounts Receivable 3,698,630 Gross Margin 8,100,000 Inventory 6,441,176 Payroll & Fringes 4,500,000 Other Current Assets 57,500 All Other Expenses 2,550,000 Total Current Assets 10,312,307 Total Expenses 7,050,000 Fixed Assets 1,187,693 Profit Before Taxes 1,050,000 Total Assets 11,500,000 Income Taxes 315,000 Profit After Taxes 735,000 Accounts Payable 1,800,000 Return on Assets = Profit Before Taxes/Total Assets Income Statement Balance Sheet Profit Planning Group Exhibit 3 The Recent Trend in Return on Assets 0 2 4 6 8 10 2001 2002 2003 2004 2005 2006 Year R
O
A
-
-
% Profit Planning Group Exhibit 4 ROA by Industry 0.0 5.0 10.0 15.0 20.0 25.0 30.0 M
e
d
i
a
n
R
O
A
-
-
% Profit Planning Group Exhibit 5 The Three Issues That Distributors Need to Worry About With Regard to Customers Profit Planning Group Exhibit 6 The Personnel Productivity Ratio--PPR The Percent of Each Gross Margin Dollar That Must Be Devoted to Payroll Salaries and Fringe Benefits Gross Margin = 4,500,000
8,100,000 = Profit Planning Group Exhibit 7 The Profitable Sales Response: A Moderate Growth Plan Summary Percent Income Statement Current Potential Change Net Sales 30,000,000 31,500,000 5.0 Cost of Goods 21,900,000 22,995,000 5.0 Gross Margin 8,100,000 8,505,000 5.0 Payroll & Fringes 4,500,000 4,554,900 1.2 All Other Expenses 2,550,000 2,677,500 5.0 Total Expenses 7,050,000 7,232,400 2.6 Profit 1,050,000 1,272,600 21.2 Profit Planning Group Exhibit 8 Sales Volume Sensitivity: A Negative View Summary Percent Income Statement Current Potential Change Net Sales 30,000,000 31,500,000 5.0 Cost of Goods 21,900,000 22,995,000 5.0 Gross Margin 8,100,000 8,505,000 5.0 Payroll and Fringes 4,500,000 4,895,100 8.8 All Other Expenses 2,550,000 2,677,500 5.0 Total Expenses 7,050,000 7,572,600 7.4 Profit 1,050,000 932,400 -11.2 PPR 55.6% 57.6% Other 8.5% 8.5% Profit Planning Group Exhibit 9 The Relationship Between a PPR Reduction and Return on Assets 0.0 5.0 10.0 15.0 20.0 55.6 53.6 51.6 49.6 47.6 45.6 PPR R
O
A
-
-
% Profit Planning Group Exhibit 10 Profitability in Relationship to Workload Total Firm Order Line Customer Net Sales 30,000,000 300.00 100.00 24,000 Gross Margin 8,100,000 81.00 27.00 6,480 Total Expenses 7,050,000 70.50 23.50 5,640 Profit Before Taxes 1,050,000 Profit Planning Group Exhibit 11 Velocity Code Analysis for Customers A 15% B 15 C 35 D 35 Customers Profits Percentage Mix of Profit Planning Group Exhibit 12 How Customers Impact Profitability Inadequate Gross Margin Inadequate Sales Volume Excessive Cost of Servicing the Account Excessive Sales Costs Slow Payment Practices Profit Planning Group Exhibit 13 The Two Global Options for Improving Customer Profitability Fire em Change Their Behavior Patterns Profit Planning Group Exhibit 14 Who Do We Blame? Sales Generation Us:
Them: Cherry Picking Gross Margin Us:
Them: Still Cherry Picking Expenses Us:
Them: Over Ordering Accounts Rec. Us:
Them: Possible Lack of Concern Profit Planning Group Exhibit 15 Dont Ever Forget This Exhibit: The Increase in Sales Required Offset a Price Cut 0 10 20 30 40 50 60 0 1 2 3 4 5 6 7 8 9 10 Price Reduction--% I
n
c
r
e
a
s
e
i
n
D
o
l
l
a
r
S
a
l
e
s
-
-
% Profit Planning Group Exhibit 16 The Relationship Between Gross Margin and Return on Assets 0.0 5.0 10.0 15.0 20.0 27.0 28.0 29.0 30.0 31.0 Gross Margin--% R
O
A
-
-
% Profit Planning Group Exhibit 17 The Impact of Gross Margin on the PPR 46.0 48.0 50.0 52.0 54.0 56.0 27.0 28.0 29.0 30.0 31.0 Gross Margin--% P
P
R
-
-
% Profit Planning Group Exhibit 18 Why Does the PPR Fall? Summary Percent Income Statement Current Potential Change Net Sales 30,000,000 30,000,000 0.0 Cost of Goods 21,900,000 21,600,000 -1.4 Gross Margin 8,100,000 3.7 Payroll & Fringes 4,500,000 4,500,000 0.0 All Other Expenses 2,550,000 2,550,000 0.0 Total Expenses 7,050,000 7,050,000 0.0 Profit 1,050,000 28.6 Gross Margin 27.0% 28.0% PPR 55.6% 53.6% Profit Planning Group Exhibit 19 Impacting Gross Margin With Product Mix Velocity Gross Potential Gross Code Sales GM% Margin Sales Margin A 18,000,000 20.0 3,600,000 18,000,000 3,600,000 B 6,000,000 30.0 1,800,000 6,000,000 1,800,000 C 4,500,000 40.0 1,800,000 4,500,000 1,800,000 D 1,500,000 60.0 900,000 Total 30,000,000 27.0 8,100,000 Profit Planning Group Exhibit 20 Impacting Gross Margin With Blind-Item Pricing Velocity Gross Potential Gross Code Sales GM% Margin Sales Margin A 18,000,000 20.0 3,600,000 18,000,000 3,600,000 B 6,000,000 30.0 1,800,000 6,000,000 1,800,000 C 4,500,000 40.0 1,800,000 4,500,000 1,800,000 D 1,500,000 60.0 900,000 Total 30,000,000 27.0 8,100,000 Profit Planning Group Exhibit 21 The Excel Template Company Section Series of Total Firm Entries Following the PAR Survey Customer Section Customer by Customer Input Profit Planning Group Exhibit 22 A Typical Set of Company Input Factors: Following the PAR Format Net Sales $20,000,000 Cost of Goods Sold ( Including Freight-in, Less Purchase Discounts) $14,600,000 Gross Profit (Calculates Automatically) $5,400,000 Operating Expenses $4,600,000 Operating Profit (Calculates Automatically) $800,000 Interest Expense $200,000 Average Accounts Receivable $2,400,000 This Section Will Allocate Your Operating Expenses, Which Were: $4,600,000 Payroll Expenses
Executive Salaries and Bonuses $400,000 Sales Executive Salaries/Commissions & Bonuses (Include Branch Managers) 220,000 Outside Sales Salaries/Commissions & Bonuses 440,000 Inside Sales Salaries/Commissions & Bonuses (Include Counter Sales) 560,000 Warehouse Salaries/Wages & Bonuses 340,000 Delivery Salaries/Wages & Bonuses 140,000 All Other Salaries/Wages & Bonuses (Office, G & A., etc.) 420,000 Total Salaries/Wages/Commissions & Bonuses (Calculates Automatically) $2,520,000 A. Summary Information From Your PAR Questionnaire B. Operating Expense Breakouts Profit Planning Group Exhibit 23 A Typical Set of Company Input Factors: Pushing Beyond the PAR Format Number of Customers 1,250 All of the Following Are For a Full Year Total Number of Customer Orders 85,000 Total Number of Customer Order Lines 225,000 Number of Deliveries to Customers 30,000 Number of Customer Return Lines 2,500 A. Customers and Orders Profit Planning Group Exhibit 24 Customer Analysis: Different Sophistication Levels to Chose From Sales Yes Gross Margin Yes Number of Orders Yes Number of Order Lines Yes Note Number of Return Lines Yes Use the Drop Down Boxes to Set These Number of Deliveries Yes Items to Either Yes or No Accounts Receivable Yes Worksheet Five: Analysis Set Up These Factors Will Be Used In Assigning Costs to Customers Profit Planning Group Exhibit 25 Customer Input: Moving Beyond PAR Somewhat 1. You may enter information for up to 1,500 different customers in this worksheet.
2. All data must be for a full year.
3. All information MUST be entered in the proper file sequence, starting in cell B20.
4. Information must be entered in every cell. If you don't track and item by customer, enter zero.
5. You may enter data by hand or by pasting from another Excel spreadsheet, an Access database, etc.
6. It is not possible to sort the information once it is entered here. This will be done on later sheets.
7. There are comments associated with some of the cells to help.
8. Before entering any data for a new group of customers, you MUST click on the button below to delete prior information that may get in the way of the new analysis. Note! The delete button runs a macro. Your Excel macros must be enabled for it to work. Customer Customer Net Gross Outside Accounts Number of Number of Number of Number of Number Name Sales Margin Comm? Rec. Orders Order Lines Deliveries Return Lines 58 Typical Customer 16000 4000 1 1920 68 180 24 2 a68 Nice Profit 10000 5000 1 1000 34 150 24 5 29-14j Loser 5000 1500 0 200 34 200 0 2 Worksheet Seven: Data Entry for Individual Customers Click here to delete ALL customers Profit Planning Group Exhibit 26 Looking at Customers as a Group Sort Settings (click each one to change setting) Column Profit Percent Order Ascending Customer Number Customer Name Sales Gross Margin Direct Expenses Overhead Expenses Profit Gross Margin Percent Direct Expenses Percent Overhead Expenses Percent Profit Percent 29-14j Loser 5,000 1,500 728 1,187 -415 30.0 14.6 23.7 -8.3 58 Typical Customer 16,000 4,000 1,591 2,156 253 25.0 9.9 13.5 1.6 a68 Nice Profit 10,000 5,000 1,052 1,628 2,320 50.0 10.5 16.3 23.2 --------------------Dollar Figures-------------------- ------------Percent of Sales------------ Note! The sort button runs a macro. Your Excel macros must be ENABLED or the button will not work. Click here to sort customers using the sort settings Profit Planning Group Exhibit 27 Looking At Customers Individually Customer Selection Dollars Percent Net Sales 16,000 100.0 Cost of Goods Sold 12,000 75.0 Gross Margin 4,000 25.0 Direct Expenses 1,591 9.9 Contribution Margin 2,409 15.1 Overhead Expenses 2,156 13.5 Net Profit 253 1.6 Variables Current Target Net Sales 16,000 $ 16,000 Gross Margin Percentage 25.0 26.0 % Number of Orders 68 60 Number of Order Lines 180 180 Number of Deliveries 24 18 Number of Returns 2 2 Days Sales Outstanding 43.8 43.8 Days Net Profit 253 600 Typical Customer A Customer Profitability Overview "What If" Analysis for This Customer Profit Planning Group Exhibit 28 The Major Expense Distinction Direct Expenses Clearly associated with an individual customer
Examples: commissions, delivery costs Indirect Expenses Incurred for the total firm Examples: warehouse rent, management
salaries Dollars Percent Dollars Percent Net Sales $16,000 100.0 $16,000 100.0 Cost of Goods Sold 12,000 75.0 11,840 74.0 Gross Margin 4,000 25.0 4,160 26.0 Direct Expenses Orders 64 0.4 57 0.4 Order Lines 161 1.0 161 1.0 Deliveries 272 1.7 204 1.3 Returns 32 0.2 32 0.2 Inside Sales 530 3.3 467 2.9 Outside Sales 416 2.6 367 2.3 Accounts Receivable Interest 115 0.7 115 0.7 Total Direct Expenses 1,591 9.9 1,404 8.8 Contribution Margin 2,409 15.1 2,756 17.2 Overhead Expenses Sales Based Overhead 1,410 8.8 1,410 8.8 Per Customer Overhead 746 4.7 746 4.7 Total Overhead Expenses 2,156 13.5 2,156 13.5 Net Profit $253 1.6 $600 3.7 Sales Increase of 0.0% Gross Margin Percentage 25.0% 26.0% Number of Orders 68 60 Number of Order Lines 180 180 Number of Deliveries 24 18 Number of Return Lines 2 2 Days Sales Outstanding 44 44 Current Potential Typical Customer Profitability Analysis for The Following Changes Were Made For The Potential Analysis increases to stays at decreases to stays at decreases to stays at Profit Planning Group Exhibit 29 The Mandate for Change During Each of the Next Five Years Sales Increase % 5.0 Gross Margin % 0.3 PPR -2.0 Other Expense % -0.1 Inventory Turnover 0.0 AR Collection Days 0.0 Profit Planning Group Exhibit 30 The Impact of Making The Mandated Changes 2007 2008 2012 Net Sales 30,000,000 31,500,000 38,288,447 Cost of Goods 21,900,000 22,900,500 27,376,240 Gross Margin 8,100,000 8,599,500 10,912,207 Payroll & Fringes 4,500,000 4,605,510 4,971,117 All Other Expenses 2,550,000 2,646,000 3,063,076 Total Expenses 7,050,000 7,251,510 8,034,192 Profit 1,050,000 1,347,990 2,878,015 Cash 115,000 120,750 146,772 Accounts Receivable 3,698,630 3,883,562 4,720,493 Inventory 6,441,176 6,735,441 8,051,835 Other Current Assets 57,500 57,500 57,500 Total Current Assets 10,312,307 10,797,253 12,976,601 Fixed Assets 1,187,693 1,247,078 1,515,831 Total Assets 11,500,000 12,044,331 14,492,432 Return on Assets 9.1 11.2 Profit Planning Group Exhibit 31 Summary and Conclusions A PDF file of this seminar will be available for one month on our web site: www.profitplanninggroup.com Go to the seminars section Profit Planning Group Exhibit 32 Hes Back! Dr. Albert Bates is founder and Chief Sophist (whatever that means) of the Profit Planning Group, a research and education firm headquartered in
Boulder, Colorado. The firm works mostly in the area of financial planning. He makes approximately 100 presentations each year on topics such as Improving the Bottom Line, Doing More With Less and Pricing for Profit. He
also heads the firms investigation into profitability research for over fifty
different trade associations. Al received his doctorate from Indiana University. He is married and has three daughters. All four of the ladies in his life have black belts in Tae Kwon Do,
so dont criticize his presentation too much. He can be reached at Profit Planning Group, 303-444-6212, 1790 38th St., Suite 204, Boulder, CO 80301, info@profitplanninggroup.com.
