Capt. Horatio T.P. Webb's Explorer Fashions
TEAM PROJECT

MIS 3300 Introduction to Computers and MIS
Honors Section
Parks -- Spring 2018

Version 1-- Last Updated 8:00 AM 4/2/2018

Using Excel, Access or whatever tools you choose, the team project is to perform two analytic tasks for Capt. T.P. Webb's Explorer Fashions -- on-line web store. See the store front here.

Data for the analysis is stored in an Excel spreadsheet here: http://www.bauer.uh.edu/parks/honorsI.xlsm.
[right click the link above and then click "save as"]
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 "honorsH.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).

                      Your task is to develop and implement a new inventory policy that improves the lost sales problem

      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/f173300_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 about 12 minutes for the macro to populate the inv table shown in the spreadsheet

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

      Cost Details:

      The original website has 9 products and with color and size options, there are about 319 possible products in the inventory. For this exercise we will assume that there is ONLY one product option for each product (i.e., 9 products).

      Inventory Carrying Costs= Cost of the Inventory * (Credit Line Interest Rate (15%/year)+ Storage Costs (72%))/12

      Order Costs= $181/order

      ProductSales PriceGross Margins
      (Sales Price - Unit Cost)      		Unit Costs
      Tricorner Hat$182.99 $98.15 $84.84
      White Powder Wig $145.70 $48.75 $96.95
      Tunic $347.40 $204.31 $143.09
      Telescope $583.00 $323.33 $259.67
      Belt $277.99 $223.61 $4.38
      White Hot Pants $118.45 $53.29 $65.16
      Boots of Spanish Leather $365.99 $310.46 $55.53
      Parchment Paper $7.49 $4.04 $3.45
      Quill Pen $9.07 $5.11 $3.96

  2. Product Forecasts for Next Year

    Task: Forecast of the sales of the 9 products for the twelve months (of the year 2013)

    1. TREND

      See this about TREND here

      See also LINEST here

      See also SLOPE here

      See also INTERCEPT here

      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 See this Excel spreadsheet (trend.xlsm) for 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)
        6. See Part 8. EXCEL TREND for trend (dark green)

    2. 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 (tren= slope*time+intercept)to compute a trend value FOR EACH observation Y (i.e., use the values of time in the trend equation (TREND=slope(X)*intercept). This is shown in the gray columns labelled "TREND".

      Then we DIVIDE the "observed value of Y" by the "trend value for Y" to get the det-rended series. 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.

      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.

    3. CHART (plotting the data)

      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.