Capt. Horatio T.P. Webb's Explorer Fashions
EXCEL VBA (Visual Basic for Applications)

MACROS

Parks -- Fall 2013
Version 3 -- Last Updated 9:00 PM 10/21/2013

This page demonstrates various uses of vbscript as a programming language for Excel macros. The example assume there is a web store -- Capt. T.P. Webb's Explorer Fashions -- whose inventory system tracks product demand. See the store front here.

An Excel spreadsheet has been design to examine the impact of the inventroy order policy for nine products sold on the web site and in addition to make forecast of future demand. The Excel data for the analysis is stored in an Excel spreadsheet here: http://www.bauer.uh.edu/parks/honorsB.xlsm. This spreadsheet has macros (VBA code) and is stored NOT a a *.xlsx file BUT as a *.xlsm file (it has macros). Save the file in a place you can remember.

To View the VBA code necessary to perform the tasks below, you will need to set up Excel to show the "Developer" tab. to do this follow these instructions: Click here for Excel 2013 OR Click here for Excel 2010.

There are two parts to the analysis:

  1. Inventory Policy

    1. Start Excel
    2. Click "File", then "Open", then find the "honorsB.xlsm" file you downloaded above.

      You will see two tables with data:

      1. The first table (left side of the spreadsheet) is the "demand table" that shows the actual orders placed on the web site for each product for each month for 2002 through 2012. There are 132 rows (11 years x twelve months per year) and 9 columns (one for each product).

      2. The second table (the right side of the spreadsheet) shows the daily operations of the web site.

        • Each product's inventory is shown for each day from "1/1/2003" to "12/31/2012"
        • Beginning Inventory is reduced by the daily demand (green column) -- this is "Sales" -- when possible.
        • When daily demand exceeds the available inventory, some sales are "lost". These lost sales are recorded
        • Receipts of new inventory is also recorded. Thus,

                      Beginning Inventory - Sales (i.e., demand when possible) + Orders Received = Ending Inventory

        • Inventory CANNOT be negative.
        • Unsatisfied demand CANNOT be recovered (i.e., when demand exceeds inventory only the existing inventory can be sold)
        • At day's end, the system checks to see if a new inventory should be ordered. This involves three values: (1) Ending Inventory; (2) Reorder Point; and (2) Reorder Quantity

                      An order is placed (for Reorder Quantity) when the Ending Inventory < Reorder Point

          The Reorder Point and Reorder Quantity are determined at the beginning of each month (see the blue values on the first day of each month)

          When a it has been determined that an order is to be placed, the delivery time (between 5 and 15 days) allows the reorder quantity to appear in the column labelled "on Order" for each day the order is pending (yellow values in the "On Order" column). When the order arrives, it appears in the "Recvd" column and is added to the inventory.

          For all products, the current policy is to use last year's monthly demand as the "Reorder Quantity" and one-half the Reorder Quantity as the Reorder Point. This mimics the traditional "two-bin" system where: orders received were split in half and placed in two equal size bins. Inventory was removed from the first bin only. When the first bin was empty, an order was placed. Inventory from the second bin was used until the new inventory arrived (a simple and easy to implement rule).

      3. Click the "Developer" tab
      4. Click the "Macros" icon (second form the left on the ribbon)
      5. A menu will appear that lists all the macros, there is only one -- named "inv".
      6. Click "Edit"
      7. The VBA code for the "inv" macro will appear in the VBA editor. (Here is text file copy of the macro http://www.bauer.uh.edu/parks/f133300tp_inv_macro.txt)

        The code performs the tasks necessary to populate the Inventory table. Rather than using the built-in Excel functions, the "inv" macro used Visual Basic to manipulate the cells.

        • Cells(row number, column number) corresponds to the spreadsheet's cells -- except that the row/column references are numbers for rows and numbers for columns (instead of letters)
        • Excel properties that are normally set by using the graphical user interface can be made in the vba code programmatically. e.g.,

                      Cells(r,c).Interior.Color = RGB(255, 255, 0) turns the background of a cell to yellow

        To run the macro click on the "Run" on the options at the top, then Click: "Run Sub/User Form F5" to execute the code

        It takes from 15 to 20 seconds for the macro to populate the inv table shown in the spreadsheet

        To save the macro click "File", then "Save"

  2. Product Forecasts for Next Year

    To forecast of the sales of the 9 products for the twelve months (of the year 2013) several alternative methods are used to produce a linear regression.

    TREND

    See this about TREND http://office.microsoft.com/en-us/excel-help/trend-HP005209320.aspx

    See also LINEST http://office.microsoft.com/en-us/excel-help/linest-HP005209155.aspx?CTT=1

    See also SLOPE http://office.microsoft.com/en-us/excel-help/slope-HP005209264.aspx

    See also INTERCEPT http://office.microsoft.com/en-us/excel-help/intercept-HP005209143.aspx

    About Forecasts (Least Squares Methods):

    The idea us to take a set of paired data (x=time, Y=observation (i.e., the data values) and compute a trend line of the form:

    y = slope * time_value + intercept

    This represents a simple linear equation of the form y = m*x + b (m=slope and b=intercept)

    We do this by relating the paired values of the independent variable (time or X) and the dependent variable (y).

    There are two formulas (one easy to compute and one hard to compute)

    1. Computational form (the easy way):

      Given N observations (i.e, x,y pairs)

      Regression Equation: y = b + mx

      Slope(m) = (N*ΣXY - (ΣX)*(ΣY)) / (N*ΣX2 - (ΣX)2)

      Intercept(b) = (ΣY - b*(ΣX) ) / N

    2. Traditional form (the hard way):


      and

    3. There are also several built-in EXCEL functions to perform trend analysis as discussed above. All of this has been perform in another spreadsheet: trend.xlsm ) as an example of how to ALL of these trending tasks:
      1. See Part 1. The Computational Form for trend (yellow)
      2. See Part 2. The Traditional Form for trend (blue)
      3. See Part 3. LINEST (full) Form for trend (green)
      4. See Part 4. LINEST (simple) Form for trend (brown)
      5. See Part 5. EXCEL SLOPE INTERCEPT Form for trend (red)

SEASONALITY

The data you are using is HIGHLY seasonal. The page above (trend.xlsm) has an example of how to compute the seasonal indexes for the sample data (PART 6 -- gray).

The general idea is to remove the trend component in the data. This is accomplished by using the trend equation to compute a trend value FOR EACH observation (i.e., use the values of time in the trend equation to calculate a value for each Y). This is shown in the gray column labelled "TREND".

Then we DIVIDE the "observed value of Y" by the "trend value for Y". This is shown in the column labelled "Y/TREND"

We then sum the de-trended values of Y for each month (column labelled "sum y/t") and calculate the average for each month (column labelled "ave y/t). These are the seasonal indexes for each month. We can use the

To forecast the future year, we use the trend equation calculate by any method above (See PART 7 -- "dark blue" on the Excel page). Then we adjust each forecast with the seasonal indices calculated in PART 6.

PART 8 (dark green) uses the EXCEL TREND Function to perform the same analysis.

EXCEL CHARTS & GRAPHS WITH MACROS

An EXCEL Chart is created to show the relationship between the time series (months 1 to 132 and the actual Y values and trend). This is just a small example.

See this chart making discussion and this chart making-spreadsheet done with macros.