Operations Dashboard for Truss Manufacturer

This manufacturer waited until the middle of the month to learn the financial results of the prior month.  By that time there was little hope of finding what went wrong or what went right.

With the process used by HKC we created a Microsoft Excel workbook whose main tab was an up to the minute dashboard of Month To Date activities:

· Sales by category

· Contribution Margin and Dollars per Board Foot by category

· Backlog by category

· Backlog in each department

· AR aging

· Sales dollars needed to reach breakeven

· Credit memo and no charge orders

· Estimated total month sales

 

Other tabs in the workbook allowed  Drill Down into the detail of all data without having to run separate reports in the enterprise system.  An incredible management tool.

Net Inv$ = Total Invoice$ - Sales Tax$ - Delivery$

 

 

 

 

 

Mo Begin

 

10/1/2015

 

 

 

Tot Work Days

 

21

 

 

Contr$ = (BidPrice + MiscTx) - (Act Lum$ + Plate$ + Item$ + Est Lab$) - Credit$(negative)

 

Mo End

 

10/31/2015

 

 

 

Remaining Work Days

 

16

 

 

Contr% = Contr$ / Net Inv$

 

$/BdFt = (Total Invoice - Sales Tax) / BdFt

 

 

Today

 

10/7/2015

 

 

 

Elapsed Work Days

 

5

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

XXXX CO. DASHBOARD AS OF:

10/7/2010 13:31

 

 

 

 

Week #

 

41

 

 

 

 

 

 

 

 

 

 

 

 

 

 

MTD BdFt

 

 

 

 

 

 

 

 

 

 

 

 

 

BREAKEVEN DOLLARS NEEDED:

 $    450,000

 

 $ 125,000

169,535

 

 

 

 

 

 

 

 

 

 

 

 

 

Category

Tot Inv
MTD

Net Inv  MTD

Contr $

Contr
%

Dlvy
$

$ / BdFt

Total   Backlog

 

In    Production

 

In    Engineering

 

Layout Done,
No Approval

 

Need Layout

 

Sum of Queries

Var

 

Tot Non-Mfg

 $      20,576

 $      19,216

 $        1,533

8%

 $     1,048

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 EWP

 $      13,588

 $      12,228

 $       (1,298)

-11%

 $     1,048

 

 $      78,431

 

 $     31,639

 

 $          759

 

 $         21,035

 

 $        22,703

 

 $      76,137

 $    (2,295)

 

 Non-Mfg Flr

 $        2,615

 $        2,615

 $           798

31%

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 Non-Mfg Rf

 $        4,373

 $        4,373

 $        2,033

47%

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 Floor Pnl

 $              -  

 $              -  

 $              -  

 

$0

 

 $             -  

 

 $             -  

 

 $             -  

 

 $                 -  

 

 $                -  

 

 $             -  

 $            -  

 

 Floor Trs

 $      27,748

 $      24,206

 $        9,318

38%

$2,453

$1.22

 $    687,215

 

 $     31,598

 

 $     33,474

 

 $       115,565

 

 $      203,779

 

 $    384,416

 $(302,799)

 

 Roof Trs

 $    150,862

 $    138,334

 $      63,543

46%

$8,751

$1.02

 $    816,020

 

 $     50,879

 

 $   162,461

 

 $       313,964

 

 $      203,030

 

 $   730,335

 $  (85,685)

 

 Walls

 $        3,173

 $        2,708

 $        1,599

59%

$300

 

 $    112,479

 

 $     19,498

 

 $             -  

 

 $         75,358

 

 $        26,275

 

 $      121,131

 $      8,653

 

 Other

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 TOTAL

 $    204,635

 $    186,523

 $      76,488

41%

 $   12,705

 $  1.06

 $ 1,694,146

 

 $   133,614

 

 $   196,694

 

 $       525,923

 

 $      455,788

 

 $  1,312,019

 $ (382,127)

 

BREAKEVEN DOLLARS TO GO:

 $    373,512

 

 $ 112,295

 

 

 

 

 

 

 

 

 

 

 

 

 

 

NET INV RQD @ CURRENT CONTR%:

 $    910,842

 

 $ 112,295

 

CURRENT MONTH BREAKEVEN SALES(incl Dlvy) =

 

 $   1,209,660

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 Aging

 0 to 30

 31 to 60

 61 to 90

 91 to 120

 Over 120

 

 Credits

 

EWP

 

Floor Pnl

 

Floor Truss

 

Roof Truss

 

Wall

TOTAL

 

 

 $    769,653

 $    802,373

 $      22,601

 $    93,509

 $     6,009

 

 

 

 $           (30)

 

 $             -  

 

 $                 -  

 

 $         (2,923)

 

 $             -  

 $    (2,953)

 

 

45%

47%

1%

6%

0%

 

 

 

2

 

0

 

0

 

5

 

0

7

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SMALL ORD $ THRESHOLD =

 

 

 $  500.00

 

 

NO CHG RF =

 $         (127)

 

Contrib$

 

 

 

 

 

 

 

 

no EWP

 

NUMBER =

 

13

 

 

NO CHG FL =

 $            (7)

 

Contrib$

 

 

 

 

 

 

 

 

AVG CONTR $ PER ORDER =

 

 

 $    108.71

 

 

NO CHG TOT =

 $         (134)

 

Contrib$

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Inv $ / day

Net $ / day

Rem
Mfg$/day

Contr$

 

 

Inoviced

 

In Prod

 

In Engr

 

Missing

 

$ from No Appr

 

 

 

 

Average:

 $      40,927

 $      37,305

 $      33,461

 $    15,298

% consumed =

100%

 

100%

 

100%

 

94%

 

0%

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Month Projection
using MTD Avg

 $    859,466

 $    783,397

 $      33,461

 $  321,250

 

 

 $    204,635

 

 $   133,614

 

 $   196,694

 

 $       357,736

 

 $                -  

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

100%

 

100%

 

100%

 

100%

 

63%

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Month Projection
4 wk Avg Ord

 $ 1,193,532

 

 $      53,901

 

 

 

 $    204,635

 

 $   133,614

 

 $   196,694

 

 $       382,127

 

 $      330,231