Download sample
This sample demonstrates data analysis tasks executed using toolbar buttons and menus in Microsoft Office Excel 2003. The data is stored in XML files.
In addition, the sample is completely localizable; it demonstrates how to take advantage of System.Resources.ResourceManager and managed resource (RESX) files. The code was written to be globalized using culture-switching techniques that were necessary in earlier versions of Microsoft Visual Studio 2005 Tools for the Microsoft Office System. Those techniques are no longer necessary because of a late change in how Visual Studio Tools for Office works with Excel. For more information, see Globalization and Localization of Office Solutions, Formatting Data in Excel with Various Regional Settings, and How to: Make String Literals Region-safe in Excel Using Reflection.
.gif) Note |
|---|
| This sample does not run if you have an English language version of Office running on Windows with regional settings other than English (United States). |
The sample is a simple inventory management tool for a fictitious ice cream store. The workbook has systems for tracking sales, inventory in storage, and the products sold. Each of these systems stores data in XML files.
The store has the following constraints on inventory:
-
Storage capacity is limited to 300 units of ice cream.
-
Ice cream orders are delivered once per week, in the morning.
-
Orders must be placed a minimum of two days before scheduled delivery date. The schedule is to place the order on Tuesday, and receive delivery on Thursday.
-
If additional stock is needed outside of the normal delivery schedule, there is an additional charge of 25 to have the order delivered.
.gif) Security Note |
|---|
| This sample code is provided to illustrate a concept and should not be used in applications or Web sites, as it may not illustrate the safest coding practices. Microsoft assumes no liability for incidental or consequential damages should the sample code be used for purposes other than as intended. |
For information about installing the sample project on your computer, see How to: Download Sample Files in Help Topics.
To run this sample
-
Press F5.
The workbook opens to the Inventory worksheet, which displays a pivot table of average daily sales and average daily profits per flavor and a ListObject control that shows the previous day’s sales data. Notice that an Orders menu has been added to the workbook, and a toolbar with buttons that correspond to the two menu items.
-
View historical sales data by selecting a different date in the Calendar control. If you select the last day of data in the data source, two additional columns (Estimated Inventory and Recommendation) are displayed.
-
Click Add New Date to add data for a new day.
The ListObject control is cleared so you can enter the end-of-day inventory values for each flavor. As you enter the current inventory for each ice cream flavor, the Estimated Inventory column shows expected end-of-week shortfalls or overages. The Recommendation column shows whether it makes sense to create an unscheduled order. The actions pane shows a list of high inventory items and low inventory items.
-
Click Save Data to save your changes.
-
Click an ice cream flavor in one of the lists on the actions pane.
Historical sales data and a trend chart on that flavor appear in the Details worksheet.
-
If an unscheduled order is recommended, click Create to determine which flavors and how much to order.
-
A new worksheet named Unscheduled Order_<Date> is added to the workbook. The worksheet estimates the quantity of each ice cream flavor that should be ordered to cover any shortage for the rest of the week.
-
Click Create Weekly Order on the Orders menu to create the weekly order.
Sales information is read from the XML files for the previous two weeks, the average of the daily sales is calculated, and a standard deviation of the distribution is determined. An estimate of future sales is calculated based on average daily sales plus two standard deviations, multiplied by seven days. This gives a 95.4% probability that the amount ordered will cover the week’s expected sales.
A new worksheet named Weekly Order_<Date> is added to the workbook. <Date> is the projected order date.
Demonstrates
The sample demonstrates how to:
-
Read data from XML files.
-
Customize menus and toolbars.
-
Use Excel built-in functions to analyze data.
-
Create pivot tables that are linked to data in XML files.
-
Bind XML data to Excel list controls.
-
Create charts.
-
Prepare user interface layout and strings for localization.
See Also