Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 1 of 140 PageID #: 30620
`Case 1:13-cv-00919—LPS Document 312-8 Filed 03/10/21 Page 1 of 140 PageID #: 30620
`
`EXHIBIT 69 PART 5
`
`EXHIBIT 69 PART 5
`
`

`

`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 2 of 140 PageID #: 30621
`Case 1:13-cv-00919—LPS Document 312-8 Filed 03/10/21 Page 2 of 140 PageID #: 30621
`
`Part 13 Analyze and Report Sales Data
`
`Next Steps
`
`See
`
`Create a chart
`
`“Create a Chart from Worksheet
`
`Data,” page 260
`
`Chart regional sales on a map
`
`“Display Data on a Map,” page 287
`
`562 Getting Results with Microsoft Office
`
`MS 1 14295
`
`a.” “WWW“. M. M, m... WWW.“ MW.......Wmm...,...~_u...‘_r__....r..,._-.t
`
`ARENDI-DEF800002005
`
`

`

`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 3 of 140 PageID #: 30622
`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 3 of 140 PagelD #: 30622
`
`ate a Sales Summary
`
`tTables t0 Summarize Sales Data
`
`Chances are, you have all the detailed data you need to make
`decisions, but it isn’t always presented in a way that makes it easy to
`draw conclusions from it. For example, suppose what you want is
`the big picture: How is each product selling? Who is selling the
`most of each product?
`
`From the same data, you can create several instant summaries,
`
`called PivotTables, to answer your questions. If you work with sales
`figures or other similar business data, Microsoft Excel can rapidly
`produce the summaries you want from the details you have.
`
`Given the date, amount, and product for each order
`
`
`
`Key Features
`PivotTable Wizard
`Chart Wizard
`
`'___h__a_elSuyama
`
`ndrew Fuller
`
`summarize how each
`
`
`product is selling by creating a
`
`
`
`16-May9-75 Seafood
`PivotTabIe — no formulas to enter.
`
`
` M833.905.34;]
`
`57 81058
`
`
`
`
`I Seafood
`
`Seafood
`
`El Meat
`
`You can also compare
`the results visually,
`with a chart.
`
`
`
`
`MS 114296
`
`Getting Results with Microsoft Office 563
`
`‘,.___. ,.
`
`i
`
`.
`
`A
`
`,
`
`V
`
`... W." ,_
`
`V
`
`,
`
`.
`
`, w “may.“ ..,,
`
`. ”a...” w.
`
`,.
`
`, .i
`
`V, ..V.,,,.,,i.tm,r. mum.» in a wwuuhwma “mama-..”._...,.WMM.W...M-wm
`
`ARENDI-DEF800002006
`
`
`
`lateralfierama
`
`
`
`

`

`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 4 of 140 PageID #: 30623
`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 4 of 140 PageID #: 30623
`
`Part 13 Analyze and Report Sales Data
`
`What Information Is Buried in Your Data?
`
`Your company probably keeps a separate record describing each
`order processed. Scanning the list shows hundreds of orders just for
`the products you are responsible for tracking. You want a fast way
`to see how much each representative has sold of each product.
`
`
`
`
`_
`
`..
`
`
`12- May-3?;
`' 135.60%
`
`‘15-1311513371....
`flichaelSuyama..
`3_ 1 316.35
`
`
`
`-1157 M31?97
`[9WFUUEE
`11.... .
`
`
`ichael Stiyarna
`; 3
`1?-May-3?{
`
`
`
`2
`ndrew Fuller
`3 134.20
`3 y
`18-May-37, .
`
`‘Seafoodh ”
`’22-May~37
`inda Callahan 3 123.431
`
`.ichael Suyama .
`T 3
`_
`37 20
`. 23-11131?” i.
`Seafood
`
`
`Meat;- ,_
`19.9....sterline ,
`1.3 1 1.0500
`.
`24211113397}.
`ndraw Fuller.
`.
`1 ”1.00;.
`Meat___
`
`257M313?
`
`
`Seafood
`Michael Suyama
`_1 530.OD
`
`26-May-371
`p
`
`
`
`Mishael...$urama.. g.
`. .4.?U.-.1.3_9 ’
`.
`
`1— There's too much detail to see
`
`what’s going on; you want total orders
`per sales rep, not a list of every order.
`
`’
`
`.
`
`' "
`
`You have a row of facts for every order.
`
`.
`
`_
`
`.
`
`
`
`You’re interested in monthly totals
`
`rather than day-by-day sales.
`
`Guidelines: Setting Up Data for a PivotTable
`Label your columns PivotTables use your column labels to cross-
`tabulate your data. For example, you can summarize orders by
`product or by sales representative.
`
`Use one worksheet row for each record A PivotTable summarizes
`data stored in rows.
`
`Make sure any dates are in date format Select any column with
`dates, and click Cells (Format menu). On the Number tab, click
`
`the Date category, and then select the date type you want.
`
`It a column contains repeating information, spell each entry the same
`way each time Entries that are the same, such as entries for seafood
`or meat in a product column, can be grouped together automatically
`in the PivotTable.
`
`9 Want to know more? Look up Getting Results - Sales Summary in
`'
`Help.
`
`1
`
`Office Assistant button
`
`564 Getting Results with Microsoft Office
`
`MS 114297
`
`N... 2.4.. _..M‘M._w.wm..un__w____ -.1..._...-. __..
`
`ARENDI- DEF800002007
`
`

`

`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 5 of 140 PageID #: 30624
`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 5 of 140 PagelD #: 30624
`
`Who Is Selling the Most Product?
`A PivotTable can answer this question in a flash. Select a cell in
`your source data. Click PivotTable Report (Data menu), and then
`follow the instructions in the wizard.
`
` " For Help on dialog box options,
`click this button and then click
`
`the option.
`
`Create a Sales Summary
`
`QOLUMN
`
`The wizard lists your column labels as
`field buttons.
`
`To summarize the sales of each product
`by sales rep, drag the field buttons into
`position. Only fields containing numbers
`can be dragged to the DATA area.
`
`The finished result cross-tabulates and
`
`totals the sales for you.
`
`PivotTable Field button
`
`
`Grand Total
`Seafood
`
`
`
`
`
`81 259.92
`:An,argi¢'%uiie’i‘
`27.05554
`
`. 54.17328
`
`
`
`{Annabella Dodsworth
`44 .555."5B
`55 .541 52 _
`22,255.34
`
`
`
`
`*Janice Leverling
`41 554.751 . 52 .797 55. .
`25 “932 35 .
`
`
`
`51.57455
`'"55E3.!.'.ah35W M25.555.00.. H 41 .315.55
`
`
`
`
`ichael Su ama
`m17.51513
`34 .535.25
`51 £15439
`
`
`—.——
`
`.
`
`I
`
`\
`
`’
`
`((Q
`
`Add commas or currency signs, or adjust the decimal places Select a
`number in the DATA area of the PivotTable. On the PivotTable toolbar
`
`(which appears automatically when a PivotTable is active) click Field, and
`then click the Number button to apply number formatting.
`
`To pivot the table, iust drag the gray field buttons You don’t need to
`start the PivotTable Wizard again to change the layout of your summary. For
`example, you could switch the positions of the Name and Product buttons
`in the PivotTable to view the products in the rows and the sales
`representatives in the columns.
`
`MS 114298
`
`Getting Results with Microsoft Office 565
`
`. #2....WMW.-.
`
`. ,7 Maud... ”we ,,
`
`ARENDI- DEF800002008
`
`

`

`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 6 of 140 PageID #: 30625
`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 6 of 140 PagelD #: 30625
`
`Part 13 Analyze and Report Sales Data
`
`How Well Are Products Selling over Time?
`To review product sales, use the PivotTable Wizard to create a
`PivotTable showing the sales performance of each product (ROW
`area) by order date (COLUMN area). If this view still has too much
`detail and too many columns to see at once, you’ll want to see
`monthly totals instead of daily details. A PivotTable can quickly
`group the dates by month.
`
`When you run the PivotTable Wizard to create a different summary,
`first select the Another PivotTable option in step 1. Then, select the
`New worksheet option in step 4. Because Microsoft Excel reuses
`the data from your first PivotTable, your workbook stays smaller
`and the changes you make to your source data are reflected faster in
`
`your PivotTables.
`
`The Group command lets you group dates automatically
`by weeks, months quarters, or years.
`
`
`~
`-
`' Group(Datamenu,Groupand
`-
`,W 49§,....-.395.9_3..5W2.19.5.5.-...-1§£5:4§..,95.3.22§,§...--§ Outline submenu).
`
`
`
`
`
`811]:
`7901?
`4390.28
`2989 08?
`19155.2-
`
`
`
`
`
`
`
`
`
` 4483.82?8536.21215E 11852. 55 28747.8:
`
`
`Select this button, and then click
`
` Dates are now grouped by months.
`
`
`
`
`5'791t155 195545.15 212492.95" 21595922 '
`10171552159022? 53735.09 323925.35
`
`
`
`
`3.5: Select a field button when you click a field button, the entire field is
`selected automatically. To select the button without selecting the entire field,
`click again.
`
`566 Getting Results with Microsoft Office
`
`MS 114299
`
`
`
`ARENDI- DEF800002009
`
`

`

`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 7 of 140 PageID #: 30626
`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 7 of 140 PageID #: 30626
`
`«a: . Update automatically When you make changes to the original data, your
`PivotTables can be updated automatically and can grow and expand. On
`the PivotTable toolbar, click Options (PivotTable menu), and then click
`Refresh on open. Or you can click the Refresh Data button on the
`PivotTable toolbar at any time.
`
` ,yl,
`Refresh Data button
`
`Create a Sales Summary
`
`Make a PivotTable directly from external data When you use data
`outside your worksheet, your PivotTable can be updated automatically when
`the data changes. if you have Microsoft Query installed with the appropriate
`ODBC drivers to access external data sources, select the External data
`source option in step 1 of the wizard. For more information on installing
`Microsoft Query, see “Get Sales Information from a Database," page 548.
`
`Add calculated fields and items You can add calculated fields and items
`
`to your PivotTables. Select a field or item in your PivotTable. On the
`PivotTable toolbar, click Formulas (PivotTable menu), and then click
`Calculated Field or Calculated Item.
`
`MS 114300
`
`Getting Results with Microsoft Office 567
`
`. ”Mum w.
`
`. ... ,... ..,_. s
`
`.
`
`.
`
`..a «WWW. Ms “wwm we. .uuu-..~,.cm.w.WWW. «wwwmwmw.,.......,.s..t.....m..
`
`..
`
`ARENDI-DEF800002010
`
`

`

`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 8 of 140 PageID #: 30627
`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 8 of 140 PagelD #: 30627
`
`Part 13 Analyze and Report Sales Data
`
`Page Fields: Another Way to Group Information
`A single PivotTable can generate several related reports by
`using page fields. A page field lets you display your data in
`three dimensions.
`
`For example, you could edit the Sales by Product PivotTable
`to use the Name column as a page field. This field lets you
`view the sales by product for each sales representative, or for
`all representatives combined.
`
`Click here to view the summary
`for each salesperson, or for all
`combined.
`
`Page field
`
`Linda Callahan???
`
`to keep your tables compact and readable.
`
`Linda Callahan.
`(All)
`Andrew Fuller
`Annabella Dodswortl
`Janice Leverling
`Linda Callahan
`*'
`r
`
`520201533720
`1'04b'40":'1'2 6'24. 40
`
`As you change the salesperson,
`the amounts change to show
`that sale’Sperson's results.
`
`For PivotTables with many fields, page fields are a great way
`
`9 Want to know more? Look up Getting Results - Sales Summary in
`'
`Help
`
`'
`
`Office Assistant button
`
`568 Getting Results with Microsoft Office
`
`MS 114301
`
`
`
`AREND-IDEF800002011
`
`

`

`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 9 of 140 PageID #: 30628
`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 9 of 140 PagelD #: 30628
`
`Create a Sales Summary
`
`Compare Sales Results Graphically
`
`Your product summary lets you consider the totals, but there’s a
`better way to compare the two product lines. You can create a chart
`from a PivotTable as you would from any data. As you make
`changes to the PivotTable or refresh the underlying data, the chart is
`updated too.
`
`
`To create a chart, first select the entire PivotTable, and then use the m
`.
`.__.
`Chart Wizard to select the options you want. For more information,
`Chart Wizard button
`see “Create a Chart from Worksheet Data,” page 260.
`
`A column chart compares data over time. showing variations.
`
`Click anywhere in the PivotTable.
`and then run the Chart Wizard.
`
`In step 2 of the Chart Wizard.
`select the data and labels,
`
`-
`
`2‘iigxieija‘ $192,935.49 ’"
`492.0
`$215,959.92
`
`omitting the totals.
`
`Select data that includes a field button You can select data such as cell
`
`A2 in the illustration above: On the PivotTable toolbar, click Select
`(PivotTable menu), and make sure the Enable Selection button is not
`pressed in. Then drag from the lower-right to the upper-left corner to select
`the data you want.
`
`I) Is your sales data broken down geographically? You can view where
`=
`your sales are concentrated on a map of your region or country. For more
`information, see “Display Data on a Map,” page 287.
`
`MS 114302
`
`Getting Results with Microsoft Office 569
`
`.,H “was”,7-.,.,,..M,._.. ...s..,._ ,., m“...
`
`,. “is ,. mu...“ m, awn... M.» as.” a...,.._..W.....,MM.._.N.M_,._...._,__
`
`a,
`
`ARENDI-DEF800002012
`
`

`

`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 10 of 140 PageID #: 30629
`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 10 of 140 PagelD #: 30629
`
`Part 13 Analyze and Report Sales Data
`
`Next Steps
`
`See
`
`Forecast future sales based on your
`recent results
`
`“Create a Sales Forecast,” page 571
`
`570 Getting Results with Microsoft Office
`
`MS 1 14303
`
`ARENDI-DEF800002013
`
`

`

`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 11 of 140 PageID #: 30630
`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 11 of 140 PagelD #: 30630
`
`
`
`
`
`ate a Sales Forecast
`
`1 n collect and analyze sales figures not just to see how you’re
`
`ing, but in the hope of predicting future results. What are the
`trends in your recent sales, and how can you expect them to affect
`film 331165?
`
`With Microsoft Excel forecasting functions, you can apply
`sophisticated statistical analysis techniques to your data. And you
`don’t have to be a statistician or study involved mathematics to
`create realistic sales projections. You can also use these techniques
`to project expenses, inventory requirements, stock prices, and other
`business trends.
`
`
`
`Key Features
`FORECAST Function
`TREND Function
`
`Trendlines in Charts
`
`Forecast sales one month
`
`ahead. or several months.
`
`
`
`Figure the sales trend
`
`
`
`ProjectedsaleaSecond Half M199?
`.
`,
`: nthqusends .of..d9||_ar§.i
`and show it graphically.
`
`See when you‘re likely
`
`to reach your goals.
`
`
`
`
`
`Year-Tu—Date Sales with Projected Trend:
`Sales Expected To Top $600,000 In October!
`
`
`
`MS 114304
`
`Getting Results with Microsoft Office 571
`
`
`
`ARENDI-DEF800002014
`
`

`

`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 12 of 140 PageID #: 30631
`Case 1:13-cv-00919—LPS Document 312-8 Filed 03/10/21 Page 12 of 140 PagelD #: 30631
`
`Part 13 Analyze and Report Sales Data
`
`Forecast Next Month’s Sales
`
`To predict next month’s sales based on the results in recent months,
`use the FORECAST function. The Paste Function button makes it
`
`
`Paste Function button
`
`easy to enter the formula.
`
`Double-click a cell, and then
`click the Paste Function button.
`
`Next month
`
`
`
`
`
`
`.
`
`..
`,
`Prttduetil-.-
`_- 3200
`
`..
`*Sales
`,,
`x
`.,
`.
`
`Your sales totals
`
`The months for
`
`which you have
`'
`sales figures
`
`wat:
`
`
`
`Click for more information.
`
`
`"""Emil-‘9'
`
`' 410.00
`
`'."'".T"'~'{'".""""
`‘ 5;
`‘
`
`
`
`The resulting forecast for next month
`
`If you want more detail than the Paste
`9 Learn more about functions
`a
`Function dialog box provides, click the question mark button in the lower-
`left corner for full reference information about the function.
`
`Want to know more? Look up Getting Results - Forecast in Help.
`
`Office Assistant button
`
`572 Getting Results with Microsoft Office
`
`MS 114305
`
`,..t ,. _._u...........uMM_. M,M..~..»‘.MHVMWWV~.WW......14....“...... 7
`
`ARENDI-DEF800002015
`
`

`

`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 13 of 140 PageID #: 30632
`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 13 of 140 PagelD #: 30632
`
`Create a‘Sales Forecast
`
`Determine Sales Trends
`
`Will sales continue to go up or down, and how fast? You can
`calculate the likely direction using the TREND function.
`
`You can use a TREND formula to predict the results for the next
`several months, even if you don’t have actual results for recent
`months. A formula that calculates several values at once uses a
`
`range of cells, called an array, to display the set of results. Before
`entering the function, select enough cells for all of the results.
`
`Forecast results for the next six months.
`
`Results from the first six months
`
`When you finish building the formula,
`press the array-entry keystroke to
`enter the formula as an array.
`
`
`
`The formula places the results in
`the array of cells you selected.
`
`9 What is the array-entry keystroke? Press CONTROL+SHIFT+ENTER to enter
`3:
`‘
`an array formula.
`
`Calculate a trend quickly using AutoFill Select your data for previous
`months, drag the fill handle with the right mouse button. and then click
`Linear Trend (to project growth along a straight line), or Growth Trend (to
`project growth along an exponential curve) from the shortcut menu.
`
`"""""
`'E ‘“
`i:~.~..-:-.:;;::«.~.-.:.~.~...:;-.~.;:.~.-.:;-.~;....
`
`Want to know more? Look up Getting Results - Forecast in Help.
`
`Fill handle
`
`Office Assistant button
`
`MS 1 14306
`
`Getting Results with Microsoft Office 573
`
`_.._._..,., -mtw-..
`
`i. M. A. ,. . _ .r. ,. mum.» “W... was... “Neva“...wmtm WWW...-“.,¢M.._.___..W ,s ._...._.m,_r... m.m._..,~._.‘ a.
`
`ARENDI-DEF800002016
`
`

`

`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 14 of 140 PageID #: 30633
`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 14 of 140 PagelD #: 30633
`
`Part 13 Analyze and Report Sales Data
`
`Chart 21 Trendline
`
`Another way to project a trend is to chart a trendline. A trendline
`shows the direction of your sales visually.
`
`First, use the Chart Wizard to create a column chart. Then, click the
`chart, and then click the first column. Click Add Trendline (Chart
`menu), and then select the type of trendline you want. For more
`information about creating charts, see “Create a Chart from
`Worksheet Data,” page 260.
`
`
`.
`Chart Wizard button
`
`Chart the dates in cells C2:L3.
`
`gsales
`
`$379;
`
`z
`
`$2522? $287; $419
`:
`é
`
`:
`
`$1141u_t__,$48_2w
`
`W
`~
`
`
`
`.
`=
`
`Yoursales
`figures
`
`The chart shows
`
`your results and
`the likely trend.
`
`‘9 Change the look of your chart For more information, see “Customize the
`a
`Look of a Chart,” page 276.
`
`MS 114307
`
`574 Getting Results with Microsoft Office
`
`, 17”-”, WW .W ., ._
`
`. p,
`
`. M.
`
`.
`
`._.._.__.,...,._a WWNMHM, . W "mam”-..r “Mm. cw...“ , s,-,..ew~_«.. l...aw...M.Ww.cm~_mwnw_mm,w~m-fl
`
`ARENDI-DEF800002017
`
`

`

`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 15 of 140 PageID #: 30634
`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 15 of 140 PageID #: 30634
`
`Create a Sales Forecast
`
`What If Sales Don’t Follow a Simple Trend?
`
`Trendlines describe future sales well when you
`have a simple, linear increase or decrease. If your
`data doesn’t conform to this type of pattern, one of
`the following charting methods might work better.
`
`First sales are up, then they’re down To see the
`trend in all the spikes and dips, add a moving
`average trendline to your chart. This trendline
`
`smoothes out the fluctuations to show the overall
`growth pattern more clearly.
`
`Sales are really taking 0” If your sales are
`doubling or tripling, you’re seeing exponential
`growth. Use the GROWTH function instead of
`TREND to forecast this kind of expansion.
`
`Mixed Results, Upward Trend
`
`Sales PrleeCfiOn — Rapid Growth!
`
`Moving average trendline
`
`Logarithmic trendline
`
`Next Steps
`
`See
`
`Include your forecasts in a report
`
`“Create a Business Report,”
`
`Perform a complete statistical
`analysis of your sales figures
`
`page 228
`
`The Analysis ToolPak add-in. Also,
`see “Analyze Data from an
`Experiment,” page 592
`
`MS 114308
`
`Getting Results with Microsoft Office 575
`
`NA,
`
`.77,
`
`,.
`
`“.7 ,.,,.__M_,_.,.. ...,,..
`
`.
`
`., ._l_..._,....._,...-wrm_.._. new” i.
`
`,. w.” ”mew. Wm. *flrh HMW‘WWQW_WM.-
`
`ARENDI-DEF800002018
`
`

`

`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 16 of 140 PageID #: 30635
`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 16 of 140 PagelD #: 30635
`
`
`
` ate a Sales Summary from a
`
`‘osoft Access Database
`
`«do,»
`'
`
`I
`
`Use Microsofi Excel PivotTables to Summarize Microsoft Access Data
`
`If ou’re workin on a sales re ort, ou can create an overview of
`Y
`sales results to see how well sales representatives are doing and
`which products are selling the most. To do so, store details about
`each order in a Microsoft Access sales database, and then switch to
`Microsoft Excel to summarize the data. Microsoft Excel can
`
`automatically create an interactive table, called a PivotTable, that
`summarizes large amounts of data. The PivotTable stays current
`because whenever you change information in the sales database, it’s
`automatically updated in the PivotTable as well.
`
`
`
`Key Features
`Simple Query Wizard
`
`PivotTable Wizard
`
`PivotTabIes
`
`
`
`
`,
`»
`
` 7: Chocolede
`02-Jan-97:
`
`.. \fiiropcl' erable
`_02-Jan-31?
`
`
`Ghana...
`
`
`_> [Jack'sNewEnglandClam Chowder
`.
`_
`.. >_
`
`
`. 02-Jan-97}
`TSpegesild
`.
`x
`’_
`;
`
`
`02-Jan-37“
`:: lpoh Coffee
`3
`
`
`jGeitost
`*
`‘
`
`
`FIBoston Crab [vleat
`
`
`
`LeverlingA [Torte au sucre
`
`
` M Alice Mutton
`Leverling m__Cote ole Slave
`
`Aniseed syrup
`
`5593.88 51"B47:23 5138222 5
`
`
`Camembert Pierrot
`
`Carnanron Ti-ers
`:
`5498.185 52.Y2.35O
`
`
`
`,/
`
`
`
`compare who is
`selling the most of
`each product
`
`
`
`To complete the steps in this topic you need to have Microsoft Office,
`Professional Edition or an individual copy of Microsoft Access installed. You
`also need to have sales data stored in a Microsoft Access database.
`
`576 Getting Results with Microsoft Office
`
`MS 114309
`
`WWW 4 ..
`
`. W, “Mam .i . MA .o.._s...._r.,.__......
`
`.. as...a... www-___m.wMm...“at...” W a...
`
`ARENDI- DEF800002019
`
`

`

`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 17 of 140 PageID #: 30636
`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 17 of 140 PagelD #: 30636
`
`Create a Sales Summary from a Microsoft Access Database
`
`Choose the Data You Want to Analyze
`Start by finding out how much each sales representative has sold.
`For each product, you need the name of the sales representative, the
`name of the product, the amount sold, and the dates of the orders. To
`retrieve this information from the database, create a query. In the
`database window, on the Query tab, click New, and then double-
`click Simple Query Wizard.
`
`Select fields from the Employees, Products, and Orders tables, and the Order Subtotals query to create a new query.
`
`Click the arrow, and then select a table or query.
`
`results.
`
`Click a field on the left, and then click
`
`here to add the field to your query.
`
`When you're finished, click here to
`complete the query and to view the
`
`LastN ame
`ProductN ame
`CI rderD ate
`
`
`
`
`_
`
`_
`.,
`
`;
`
`;
`
`T
`i
`
`.
`
`,.
`
`:SIngaporean ldekkre‘n Fried Mee
`.
`,
`01~Ju|-97,
`:Meezare'la..dt9i.°vanni...,,
`$449101:
`maul—er;
`outasaCabrales
`.DZW'QT ,. ”5035345
`,.:T9fu_.,,. ..
`maul-97
`$1 senor
`Manlimup Dried Apples
`05-Jul-97.
`$1,552.65
`. LouisianaFiererPaptm,Sauce
`05-Jul-37
`51.55250:
`‘.Jack'_s New England‘lfitlam Chowder ;
`{05-Jul-97
`$1,552.50:-
`Manjimup Dried Apples
`i
`" Peacock
`-
`_ 050N797; y
`m 5554:0133
`fLuuisiana Fiery Hot Pepper Sauce
`>1>,:?'? Leverling
`05-Jul-97:
`$554.05:
`qustaf‘s Knackebred
`Levarling
`05-4'4l7‘2't'w
`“£56405
`Ravtalmngain
`,,
`Levering
`EB-Jle? _
`_ $3,59?,90§
`Sir Rodney‘s Marmalade
`" Peacock .
`,
`05Jul-97
`$3,597.90}
`Gaitqst
`Peacock
`7
`
`93-Jul-97...,... $53,597.91:
`.. "Camembert Pierrot
`‘ *Peaeoek,
`.0
`V
`:51
`$3,
`Levering. Marilee...“....,__
`
`
`_D’7-.lul~97_.m
`BB
`51
`;
`Leverling
`Chartreuse vane
`Medina.....99:59:13.5? letter;.......................’......0.?:.J.L.I!-§.7.........$1.454;th
`
`.. _.,.
`
`..
`
`Getting Results with Microsoft Office 577
`
`w, ,.., Wm» ,,
`
`. .. .,__...,,.......Mi...r....w ...-,.._,..—._.....N.,.H.._........_..W-_~___.._~_._immd .0
`
`. ”a
`
`ARENDI-DEF800002020
`
`

`

`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 18 of 140 PageID #: 30637
`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 18 of 140 PagelD #: 30637
`
`Part 13 Analyze and Report Sales Data
`
`Summarize Data by Product and Salesperson
`Now that you have the raw data you need, you want to total and
`compare the amount of each product sold by each sales
`representative. While working in Microsoft Access, you can use
`Microsoft Excel to summarize this information.
`
`In the database window, click the New Object button, click Form,
`and then click PivotTable Wizard. Select your query and the fields
`you want to summarize. When the wizard finishes, the PivotTable
`appears as a Microsoft Excel object inserted in a Microsoft Access
`form.
`
`
`New Object button
`
`To create the PivotTable, drag the field buttons on the right to the diagram on the left.
`
`Click here to create the PivotTable.
`
`Use the last names of your sales
`representatives for the column
`headings.
`
`List the products down the rows.
`
`Show the total orders for each sales
`
`representative and product. Only fields
`containing numbers can be dragged to
`the data area.
`
`The resulting PivotTable summarizes
`.
`the sales figures for you-
`
`
`'“*'
`.
`Dodsworth :Fuller
`Davoiio
`1' Buchanan :Callahan
`ProduciName
`
`
`”9423.55
`Alice Mutton”
`5439 y
`‘_ eases ._..1979-23.._ 14353.;
`
`
`
`
`.
`Aniseed Syrup
`
`Boston Crab Meat ..
`
`Camembert Pietra
`
`Carnamon '59??? ,
`
`
`.
`
`w-9 Can’t read some field names? Double-click a long field name so that you
`
`:—
`
`can view all of its characters.
`
`9 Want to know more? Look up Getting Results - Microsoft Access
`'
`Sales Summa
`in Hel
`.
`ry
`p
`
`Office Assistant button
`
`578 Getting Results with Microsoft Office
`
`MS 114311
`
`m. t.i..‘._.u.‘..t...m...._....... .., _...w..m~.... ”we... >.....wwwww “NWW.M,.NM.........
`
`ARENDI-DEF800002021
`
`

`

`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 19 of 140 PageID #: 30638
`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 19 of 140 PagelD #: 30638
`
`Create a Sales Summary from a Microsoft Access Database
`
`Analyze the Data in Different Ways
`After the wizard creates the PivotTable, you can change the layout
`so that products are displayed across the columns, and sales
`
`representatives are listed down each row. Unlike a Microsoft Access
`table, the PivotTable is dynamic; you can transpose its rows and
`columns to see different summaries of the data.
`
`You edit the table in Microsoft Excel by clicking the Edit
`PivotTable button on the form. The PivotTable opens in a separate
`window with Microsoft Excel commands and toolbars displayed.
`
`Click the Refresh
`
`To display the sales representatives by row and the
`
`Data button to update
`total amount sold of each product by column, drag the
`
`the data in the table.
`row and column headings to switch their positions.
`
`
`
`
` Davolio
`EDodsworth E7
`394233-55.“
`...,5433;__.H'.53§9fiiu...,
`
`
`.
`0;.
`20.43.12?
`2453-22?
`
`
`
`
`_
`.
`_
`p
`7724.5
`2547.?)
`5959.35!
`ost'on _‘Crab ‘M'eat _
`“Camembert Pierrot.
`..
`7 14342.11; 5525.82
`25677.58;
`“
`_
`
`amen/0“ Tigers
`V. "793..
`5575.552. 8327.911“
`‘Chai
`2138:
`2415.3;
`2215.35
`
`
`
`
`_ ..
`
`'
`
`
`
`
`u_ofSubtotal ’
`“‘3“ 4
`
`
`
`lice Mutton
`’
`U
`EAniseed S ru-
`:
`Lstfiam :
`7
`
`
`..
`,
`.
`.
`,
`_
`Bushman,“ .,
`.
`. 952.3555
`.
`a
`09
`
`
`,. was:
`“2048.222
`Callahan.
`.
`
`
`Davoliuw
`BEDS;
`24138.22;
`Dndsworth
`1979.23;
`7717.825
`
`14353.45;
`7 0;
`7260.91
`479.45
`
`,,
`
`.
`
`,,
`
`.
`
`_
`
`0
`
`_v Finished editing in Microsoft Excel? Click Exit (File menu) to save your
`e ‘
`changes and return to Microsoft Access.
`
`Want to reactivate Microsoft Excel for further editing? Open the
`Microsoft Access form, and then double-click the PivotTable.
`
`MS 114312
`
`Getting Results with Microsoft Office 579
`
`ARENDI-DEF800002022
`
`

`

`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 20 of 140 PageID #: 30639
`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 20 of 140 PagelD #: 30639
`
`Part 13 Analyze and Report Sales Data
`
`View Sales Data by Quarter
`Suppose you want to compare the total sales figures of each product
`by quarter. You can have the PivotTable Wizard create another
`PivotTable from the same query that displays the sales performance
`of each product (ROW area) by order date (COLUMN area). When
`the wizard finishes, edit the PivotTable to group the data by quaIter.
`
`
`
`Alice Mutton fl
`
`
`Anrseed Syrup
`
`Camembert Pierrot
`,
`I
`
`carnarvori Tigers II
`.
`.
`
`Chat
`Chang
`
`Chartreuse verte
`ChefAntons Cajun Seasoning
`
`ChefAntons Gumbo Mix
`.
`I
`Chocolate
`..
`.
`Gate(is Bla e
`
`,.
`
`To summarize the data by quarter, right-click
`the column heading, and then click Group
`(Group and Outline menu).
`
`Select Quarters from the list in the Grouping
`dialog box.
`
`
`
`
`AlicIIe Mutton V
`
`,
`24335.73.
`., 21.3% .3 S
`
`.1519“?!
`Amseed Syrup
`
`
`5000~04§
`.,
`.,
`-,
`..
`12313
`
`
`27133 43
`23333.75;
`BostonCrab Meat
`.
`.
`
`
`
`_ 3233223;
`Camembert Pierrot
`_ 14553.33 ,.
`
`13723.43E
`
`
`
`
`Sort Descending button
`
`Display specific products; hide those you don’t need Click the Edit
`Pivot Table button on the form, select the rows of products that you want to
`hide, click Row, and then click Hide (Format menu).
`
`Flank products from most to least sold Click Edit Pivot Table, select
`the products you want to sort, and then click the Sort Descending button.
`
`\
`
`D Q
`
`-A
`
`580 Getting Results with Microsoft Office
`
`Ms 114313
`
`.~.V-M.....mwm. mm» ... 3......mwww
`
`ARENDI-DEF800002023
`
`

`

`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 21 of 140 PageID #: 30640
`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 21 of 140 PageID #: 30640
`
`Create a Sales Summary from a Microsoft Access Database
`
`Next Steps
`
`To
`
`Learn more about queries
`
`Display data visually
`
`See
`
`“Evaluate Sales Performance in a
`Microsoft Access Database,”
`
`page 582
`
`“Create 21 Chart from a Database,”
`page 272
`
`MS 114314
`
`Getting Results with Microsoft Office 581
`
`ARENDI-DEF800002024
`
`

`

`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 22 of 140 PageID #: 30641
`Case 1:13-cv-00919—LPS Document 312-8 Filed 03/10/21 Page 22 of 140 PagelD #: 30641
`
` luate Sales Performance
`
`1n Microsoft Access Database
`Use a Query to Find Out How Well Your Sales Force Is Doing
`
`Suppose you want to review sales figures for your company,
`Northwind Traders, for September. In particular, you want to see
`how your sales force is doing and who the top performers are. To
`retrieve this information, you can create a query, a question about
`.
`.
`data that IS stored 1n more than one table.
`
`
`
`Key Features
`Simple Query
`
`3%
`
`Expressions
`
`?
`
`Sorting Records
`
`
` Septe mb
`
`
` Janet Leveriing
`
` Peacock
`Jiargzu‘et
` DEWOiiO
`Nancy
`
`Anne
`Dodsworth
`
`
`Cat 1 uh an
`Lu u ra
`
`
`Robert
`$61L90
` Levcrling Janet
`
`
` Fuller Andrew
`
`
`
`1558.70
`
` Peacock
`Margaret
`
`Dru/cl to
`h’lichael
`
` Nancy 33? 7.40
`
`
`
`
`
`King
`
`To complete the steps in this topic you need to have Microsoft Office,
`Professional Edition or an individual copy of Microsoft Access installed. You
`also need to use the Table Wizard to create the Employees, Orders, and
`Order Details tables shown in this topic.
`
`582 Getting Results with Microsoft Office
`
`~~~ ~ ,
`
`~
`
`‘
`
`.
`
`¢ Mi. .. .i _. _
`
`A
`
`..
`
`.
`
`. Men”. A a.
`
`.WW-..“......._..,....._...M.~~,......W_s_ no”.mm....,MWW,M.MJMM_WAWW. _M...~..W..i....
`
`.W
`
`ARENDI-DEF800002025
`
`MS 114315
`
`

`

`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 23 of 140 PageID #: 30642
`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 23 of 140 PagelD #: 30642
`
`Evaluate Sales Performance in a Microsoft Access Database
`
`Begin the Query
`To see how well your sales force is doing, you want to review sales
`figures. More specifically, you want to see who received each order,
`the date the order was received, and the amount charged for each
`order. This information is stored in three tables: Employees, Orders,
`and Order Details.
`
`To find information that meets specific criteria (such as details on all
`orders received in September), create a query in design view so that
`you can define criteria in the query design grid. In the database
`window, on the Queries tab, click New, and then click Design
`View.
`
`On the Tables tab, click each table
`
`
`
`that contains the fields you need
`
`
`
`To determine how much each employee sold, create a
`calculated field that multiplies Quantity by UnitPrice. To
`create the calculated field, right-click the blank cell to
`the right of the OrderDate field, and then click Build.
`
`
`
` and then click here to add it to the query.
`
`
`
`
`Categories
`
`
`
` Shippels
`
`
`Suppliers
`LaslN ame
`Customerl D
`
`
`
`FirstN ame -
`Employeel D
`
`
`
`UrderDate -
`Title
`
`
`
`T itleUfCourtes
`
`
`
`Order Details Extended r ”menu:
`
`Order Details
`————
`
`
`I-_ II--
`I-
`
`
`
`
`
`
`. Order Details seemed? '
`
`Fm
`
`
`
`Click to select
`
`a field from
`
`the field list,
`and then
`
`drag it to
`the design
`query grid.
`
`MS 114316
`
`Getting Results with Microsoft Office 583
`
`.
`
`,..WWN.M-._- m..__.. ... ww-.........-.-..em.-..w -eww.-mm»._.ww_~. .
`
`ARENDI-DEF800002026
`
`

`

`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 24 of 140 PageID #: 30643
`Case 1:13-cv-00919-LPS Document 312-8 Filed 03/10/21 Page 24 of 140 PagelD #: 30643
`
`Part 13 Analyze and Report Sales Data
`
`‘9 Want to add all the fields from a table? Double-click the title bar on the
`e ‘
`field list and then drag all the field