Excel Dashboard Helper

Help

Dynamic Dashboard Illustration Reports

Lloyds Banking Group - For Internal Use Only

Inputs

•

Dynamic Das!oard

•

Data

•

"#1 - $alesperon analysis

•

"#% Data

•

"#2 - &roduct range analysis

•

Inputs

•

"#' - &roduct ranage analysis (trend)

•

"#* - $alesperson analysis (trend)

•

"#+ - $ales , epenses analysis

•

"#% - &roduct report

•

#elp$eet

•

"#7 - "ompetitor analysis

•

"#. - /ecuti0e summary

Help

1 of 27

Titles you can't find anywhere else

Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.













Hel

Salesperson analysis &I for &roduct " - 3D

Salesperson 1 Salesperson 2 Salesperson 3 Salesperson 4 Salesperson 5

Product Group &roduct " Month: 3D KPI: &I41 Salesperson

$alesp $alesper erson son 1 $alesp $alesper erson son 2 $alesp $alesper erson son ' $alesp $alesper erson son * $alesp $alesper erson son + Total

Product & Period

Product & 'TD

(ll Products (ll Products & Period & 'TD

'7' '7' '%2 '%2 +6* +6* +*+ +*+ '.. '..

-

'7' '%2 +6* +*+ '..

-

15+2' 15+2' 15**+ 15**+ 15*.' 15*.' 15%16 15%16 15+6. 15+6.

2,!2

"

2, 2,!2

"

!,#

%$26 18 26 21 26

























Product Ran)e analysis 3otal G& for 9ll $alespeople - 3D

Help

Product A Product B Product C Product D

Salesperson: 9ll $alespeople Month: 3D KPI: 3otal4G& Product

Product ( Product * Product + Product D Total

Salespers Salesper (ll (ll on & son & Salesperso Salesperson Period 'TD ns & Period s & 'TD %751*6 %658'6 225**6 '85766

-

-

-

%751*6 %658'6 225**6 '85766

%,2

"

"

"

%,2

'+ '2 12 21



























Help

Product Ran)e analysis &I trend for $alesperson 1 120 100 !0 0 40 20 Apr-0 9 Pro duct A

Ma y-0 9 Pro duc t B

Ju n-09 Pro du ct C

Jul-0 9 Pro duct D

Salesperson: $alesperson 1 KPI: &I41 Product Product ( Product * Product + Product D

(pr"% +6 *+ %+ %+

May"% '2 %6 *+ 2

-un"% 8+ 7+ '2 *6

-ul"% 21 +6 87 +6

Product ( Product * Product + Product D

226 2+7 '+1 1+2

2%1 27. 188 11%

2.2 '1+ '27 '26

2'1 186 21* 1*6

Product Product ( Product * Product + Product D

(pr"% +6 *+ %+ %+

May"% '2 %6 *+ 2

-un"% 8+ 7+ '2 *6

-ul"% 21 +6 87 +6













Help

Salesperson analysis &I trend for &roduct 9 100 90 !0 "0 0 50 40 30 20 10 -

Salesperso n1 Salesperso n2 Salesperso n3 Salesperso n4

Apr-09

Product: KPI: $alesperson $ale $alesp sper erso son n1 $ale $alesp sper erso son n2 $ale $alesp sper erso son n' $ale $alesp sper erso son n* $ale $alesp sper erso son n+ $alesp $alesper erson son 1 $alesp $alesper erson son 2 $alesp $alesper erson son ' $alesp $alesper erson son * $alesp $alesper erson son + $alesperson $ale $alesp sper erso son n1 $ale $alesp sper erso son n2 $ale $alesp sper erso son n' $ale $alesp sper erso son n* $ale $alesp sper erso son n+

Ma y-09

Jun -09

Ju l-0 9

&roduct 9 &I41 (pr"% +6 *6 +6 %6 26 22+ 22+ 166 166 226 226 2*6 2*6 18+ 18+ (pr"% +6 *6 +6 %6 26

May"% '2 +2 +* +. %+

-un"% 8+ 2+ %' ** ++

-ul"% 21 26 .6 86 26

1'8 1*6 1++ 2+2 1%.

2*2 21* 2'7 281 2%6

21. 81 182 1.2 82

May"% '2 +2 +* +. %+

-un"% 8+ 2+ %' ** ++

-ul"% 21 26 .6 86 26













Help

Sales (nalysis :


9

$cale Budget

8 of 27





Product report &roduct 18 as te igest turno0er Product


Sales

+.S

&roduct 1%

2+6

1++

&roduct 17

2%6

1%6

&roduct 1.

276 ;<

266

&roduct 18

2.6 ;<

26+

&roduct 26

166 ;-

26

16 of 27





Select competitors to include:

"ompetitor analysis for &roduct + =e remain in market leader position

1666 866 .66 766 %66 +66 *66 '66 266 166 6

1666 7+6

Our $ales


"omp *

%66

%66

"omp +

"omp %

11 of 27







Help /0ecuti1e Summary

/0ecuti1e Summary 9rea for entry of eecuti0e summary summary commentary> commentary> For eample - $ales and /penses ?itin tolerance> &roduct :ange 9 represents '+ of total sales> &roduct 18 remains our !est selling product> $ales &erson ' is our leading salesperson>













Month Product @an-68 &roduct 9 @an-68 &roduct 9 @an-68 &roduct 9 @an-68 &roduct 9 @an-68 &roduct 9 @an-68 &roduct B @an-68 &roduct B @an-68 &roduct B @an-68 &roduct B @an-68 &roduct B @an-68 &roduct " @an-68 &roduct " @an-68 &roduct " @an-68 &roduct " @an-68 &roduct " @an-68 &roduct D @an-68 &roduct D @an-68 &roduct D @an-68 &roduct D @an-68 &roduct D Fe!-68 &roduct 9 Fe!-68 &roduct 9 Fe!-68 &roduct 9 Fe!-68 &roduct 9 Fe!-68 &roduct 9 Fe!-68 &roduct B Fe!-68 &roduct B Fe!-68 &roduct B Fe!-68 &roduct B Fe!-68 &roduct B Fe! 68 &roduct "

Salesperson $alesperson 1 $alesperson 2 $alesperson ' $alesperson * $alesperson + $alesperson 1 $alesperson 2 $alesperson ' $alesperson * $alesperson + $alesperson 1 $alesperson 2 $alesperson ' $alesperson * $alesperson + $alesperson 1 $alesperson 2 $alesperson ' $alesperson * $alesperson + $alesperson 1 $alesperson 2 $alesperson ' $alesperson * $alesperson + $alesperson 1 $alesperson 2 $alesperson ' $alesperson * $alesperson + $alesperson 1

nit +ost

nit SP 26 26 26 26 26 +6 +6 +6 +6 +6 %6 %6 %6 %6 %6 16 16 16 16 16 26 26 26 26 26 +6 +6 +6 +6 +6 %6

nit Pro3it +6 +6 +6 +6 +6 .6 .6 .6 .6 .6 76 76 76 76 76 '6 '6 '6 '6 '6 +6 +6 +6 +6 +6 .6 .6 .6 .6 .6 76

+ount '6 '6 '6 '6 '6 '6 '6 '6 '6 '6 16 16 16 16 16 26 26 26 26 26 '6 '6 '6 '6 '6 '6 '6 '6 '6 '6 16

KPI4 26 %6 166 .6 %6 '6 76 76 %6 76 +6 .6 116 *6 .6 26 +6 16+ .6 86 '6 %6 86 %6 +6 26 .6 .6 +6 26 '6

+6 26 %6 *6 +6 %6 .6 2' +% 8. '6 166 266 1+6 86 166 266 166 +6 86 %6 26 +6 * +6 %+ 8. '2 %+ *+ 166













Aar-68 &roduct D Aar-68 &roduct D Aar-68 &roduct D Aar-68 &roduct D 9pr-68 &roduct 9 9pr-68 &roduct 9 9pr-68 &roduct 9 9pr-68 &roduct 9 9pr-68 &roduct 9 9pr-68 &roduct B 9pr-68 &roduct B 9pr-68 &roduct B 9pr-68 &roduct B 9pr-68 &roduct B 9pr-68 &roduct " 9pr-68 &roduct " 9pr-68 &roduct " 9pr-68 &roduct " 9pr-68 &roduct " 9pr-68 &roduct D 9pr-68 &roduct D 9pr-68 &roduct D 9pr-68 &roduct D 9pr-68 &roduct D Aay-68 &roduct 9 Aay-68 &roduct 9 Aay-68 &roduct 9 Aay-68 &roduct 9 Aay-68 &roduct 9 Aay-68 &roduct B Aay-68 &roduct B Aay 68 &roduct B

$alesperson 2 $alesperson ' $alesperson * $alesperson + $alesperson 1 $alesperson 2 $alesperson ' $alesperson * $alesperson + $alesperson 1 $alesperson 2 $alesperson ' $alesperson * $alesperson + $alesperson 1 $alesperson 2 $alesperson ' $alesperson * $alesperson + $alesperson 1 $alesperson 2 $alesperson ' $alesperson * $alesperson + $alesperson 1 $alesperson 2 $alesperson ' $alesperson * $alesperson + $alesperson 1 $alesperson 2 $alesperson '

16 16 16 16 26 26 26 26 26 +6 +6 +6 +6 +6 %6 %6 %6 %6 %6 16 16 16 16 16 26 26 26 26 26 +6 +6 +6

'6 '6 '6 '6 +6 +6 +6 +6 +6 .6 .6 .6 .6 .6 76 76 76 76 76 '6 '6 '6 '6 '6 +6 +6 +6 +6 +6 .6 .6 .6

26 26 26 26 '6 '6 '6 '6 '6 '6 '6 '6 '6 '6 16 16 16 16 16 26 26 26 26 26 '6 '6 '6 '6 '6 '6 '6 '6

86 11+ +6 %6 ++ 76 126 .8 26 %% +6 86 7* *6 .. %6 1%6 +% '6 ++ ** 1+6 +2 26 %6 76 166 '+ 16 26 .6 86

* + 7. 8. +6 *6 +6 %6 26 *+ 12 86 +6 %6 %+ *+ 7. 8. %+ %+ ' 2 '2 +6 '2 +2 +* +. %+ %6 +% %+













@un-68 &roduct " @un-68 &roduct " @un-68 &roduct D @un-68 &roduct D @un-68 &roduct D @un-68 &roduct D @un-68 &roduct D @ul-68 &roduct 9 @ul-68 &roduct 9 @ul-68 &roduct 9 @ul-68 &roduct 9 @ul-68 &roduct 9 @ul-68 &roduct B @ul-68 &roduct B @ul-68 &roduct B @ul-68 &roduct B @ul-68 &roduct B @ul-68 &roduct " @ul-68 &roduct " @ul-68 &roduct " @ul-68 &roduct " @ul-68 &roduct " @ul-68 &roduct D @ul-68 &roduct D @ul-68 &roduct D @ul-68 &roduct D @ul-68 &roduct D

$alesperson * $alesperson + $alesperson 1 $alesperson 2 $alesperson ' $alesperson * $alesperson + $alesperson 1 $alesperson 2 $alesperson ' $alesperson * $alesperson + $alesperson 1 $alesperson 2 $alesperson ' $alesperson * $alesperson + $alesperson 1 $alesperson 2 $alesperson ' $alesperson * $alesperson + $alesperson 1 $alesperson 2 $alesperson ' $alesperson * $alesperson +

%6 %6 16 16 16 16 16 26 26 26 26 26 +6 +6 +6 +6 +6 %6 %6 %6 %6 %6 16 16 16 16 16

76 76 '6 '6 '6 '6 '6 +6 +6 +6 +6 +6 .6 .6 .6 .6 .6 76 76 76 76 76 '6 '6 '6 '6 '6

16 16 26 26 26 26 26 '6 '6 '6 '6 '6 '6 '6 '6 '6 '6 16 16 16 16 16 26 26 26 26 26

8+ '6 26 +6 +6 +* 26 86 .6 .6 .8 16 26 86 86 %2 16 '6 7* .6 *+ 26 *6 %6 *6 7+ 16

.7 %+ *6 +6 %6 86 .6 21 26 .6 86 26 +6 26 '6 *6 +6 87 % +6 *6 21 +6 *+ '2 12 1













Total sales Total GP 1666 %66 '666 1.66 +666 '666 *666 2*66 '666 1.66 2*66 866 +%66 2166 +%66 2166 *.66 1.66 +%66 2166 '+66 +66 +%66 .66 7766 1166 2.66 *66 +%66 .66 %66 *66 1+66 1666 '1+6 2166 2*66 1%66 2766 1.66 1+66 866 '666 1.66 *+66 2766 '666 1.66 2+66 1+66 1%66 %66 %*66 2*66 %*66 2*66 *666 1+66 1%66 %66 2166 '66

Help















2766 '*+6 1+66 1.66 27+6 '+66 %666 **+6 1666 +2.6 *666 7266 +826 '266 %1%6 *266 11266 '826 2166 1%+6 1'26 *+66

1.66 2'66 1666 1266 1%+6 2166 '%66 2%76 %66 18.6 1+66 2766 2226 1266 ..6 %66 1%66 +%6 '66 1166 ..6 '666













%%+6 2166 %66 1+66 1+66 1%26 %66 *+66 *666 *666 **+6 +66 1%66 7266 7266 *8%6 .66 2166 +1.6 +%66 '1+6 1*66

8+6 '66 *66 1666 1666 16.6 *66 2766 2*66 2*66 2%76 '66 %66 2766 2766 1.%6 '66 '66 7*6 .66 *+6 266













Product Sales &roduct 1 &roduct 2 &roduct ' &roduct * &roduct + &roduct % &roduct 7 &roduct . &roduct 8 &roduct 16 &roduct 11 &roduct 12 &roduct 1' &roduct 1* &roduct 1+ &roduct 1% &roduct 17 &roduct 1. &roduct 18 &roduct 26 &roduct 21

P 166 ;116 126 1'6 1*6 1+6 1%6 176 1.6 186 266 216 226 2'6 2*6 2+6 2%6 276 ;< 2.6 ;< 166 ;116

+.S P2 Gross Pro3it P5 KPI  P6 KPI 2 P# KPI 5 P$ +ontn P! Sales Trend .6 ;26 ;+ ;'+ ;' ;12 ;.+ 2+ ;+ >+ '.>+ '>7+ ;1' 86 '6 % *2 * >+ 1* 8+ '+ % >+ *+>+ +>2+ 1* 166 *6 7 *8 % 1+ 16+ *+ 7 >+ +2>+ %>7+ 1% 116 +6 . +% 7 >+ 17 11+ ++ . >+ +8>+ .>2+ 17 126 %6 8 %' 8 1. 12+ %+ 8 >+ %%>+ 8>7+ 18 1'6 76 16 76 16>+ 26 1'+ 7+ 16>+ 7'>+ 11>2+ 26 1*6 .6 11 77 12 21 1*+ .+ 11>+ .6>+ 12>7+ 22 1+6 86 12 .* 1'>+ 2' 1++ 8+ ;< 12>+ .7>+ 1*>2+ ;< 2' 1%6 166 ;< 1' 81 1+ ;< 2* 266 ;< 76 1'>+ ;< 8*>+ ;< 16>+ '6 ;< 26+ ;< 7+ 1* ;< 8. ;< 11>2+ '1 ;< 26 ;.6 + ;'+ ;12 ' ;.+ 2+ ;++ '. + ' 7+ ;1' B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B

B B B B B B B B B B B

B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B

B B B B B B B B B B B B B B B B B B B B B B B B B B B

B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B

B B B B B B B B B B B B B B B B B B

B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B

B B B B B B B B B B B B B B B B B B B B B

B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B

B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B

B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B

B B B B B B B B B B B

B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B

B B B B B B B B B B B B B B B B B B B B B B B B B B B

B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B

B B B B B B B B B B B B B B B B B B

B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B

B B B B B B B B B B B B B B B B B B B B B

B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B

B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B













-an"% * 2 + * 2 1 2 1 * . 16 2 + * 2 1 2 1 * . *

7eb"% + 2 + + ' 1 * 2 + 8 16 2 + + ' 1 * 2 + 8 +

Mar"% % 2 + % + ' % ' % 7 16 2 + % + ' % ' % 7 %

(pr"% . 1 + + . ' . * 7 + 16 1 + + . ' . * 7 + .

May"% 8 1 + * . * 16 + . * 16 1 + * . * 16 + . * 8

-un"% 16 ' % ' % % . % 8 2 16 ' % ' % % . % 8 2 16













Hel p

Ran)e 8ame: Position4Ran)e

3op Left Aiddle Left Bottom Left 3op :igt Aiddle :igt Bottom :igt Cie? #ide

SP49ist

Prod49ist

Period4list Period4list2

KPI48ame

9ll $alespeople $alesperson 1 $alesperson 2 $alesperson ' $alesperson * $alesperson +

9ll &roducts &roduct 9 &roduct B &roduct " &roduct D

3D

"ount 3otal4sales 3otal4G& &I41

@an-68 Fe!-68 Aar-68 9pr-68 Aay-68 @un-68 @ul-68 9ug-68 $ep-68 Oct-68 o0-68 Dec-68















Product49P

8one " lins to Dynamic Dashboar +hart 9ocation matri0

"ount of sales 3otal sales 3otal G& &I

&roduct 9 &roduct B &roduct " &roduct D

"ost &rice $a $ales &rice G& G& 26 +6 +6 .6 %6 76 16 '6

3op '6 '6 16 26

3op Left Aiddle Left Bottom Left 3op :igt

'66 +'6 7+6 '66













" ;here each cell has named ran)e

Left .6 .6 .6 '.6













Help 7ront pa)e

=elcome to te Dynamic Das!oard Illustration from "larity "onsultancy $er0ices> 3e user notes are arranged !y 3a!> 3a!> /iter select a ta! from te drop do?n !elo? or scroll troug te notes using te netEpre0ious !uttons>

Home

3is is an area for uick na0igation troug te pages in tis spreadseet> ---------------------------------------------------------------------$imply click on te page tat you ?is to 0isit>

Dynamic Dashboard

3e Dynamic Das!oard allo?s te user to select te reports tat tey ?ould like to display and te location to put tem in> ---------------------------------------------------------------------In te cart location dri0er coose a cart and select te location> If you select #ide it ?ill !e placed outside of te print area> If you select Cie? it ?ill !e placed at te top of te seet for you to 0ie?>













+H2

&roduct :ange 9nalysis - &ie "art --------------------------------------------------------------------- 9 simple pie cart !y product product range> 3e user can select te salesperson5 &I type and te period to !e analysed>

+H5

&roduct :ange 3rend 9nalysis --------------------------------------------------------------------- 9 simple line cart !y product range> 3e user can select te salesperson5 &I type and te start period to !e analysed>

+H6

$alesperson 3rend 9nalysis --------------------------------------------------------------------- 9 simple line cart !y salesperson> salesperson> 3e user can select te product range5 &I type and te start period to !e analysed>













+H$Data

Base data for "art % ----------------------------------------------------------------------

Inputs

Inputs ---------------------------------------------------------------------Inputs used for te model identified troug named ranges>

Help Sheet

Inputs for tese user notes ---------------------------------------------------------------------/nter te title in column 9 and te !ody into column B>















3is document is from candoo>org candoo>org

Excel Dashboard Helper

Recommend Documents