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

TEAM PROJECT MIS 3300 Introduction to Computers and MIS Honors Section Parks -- SPRING 2020 Version 2-- Last Updated 1:20 PM 2/25/2020

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.

The Excel spreadsheet is stored here: http://www.bauer.uh.edu/parks/honorsM.xlsm.
[right click the link above and then click "save as"]
This spreadsheet has macros (VBA code) and is stored NOT as a *.xlsx file asa typical Excel file but as a *.xlsm file (because 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 "honorsM.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/s203300_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

      Product	Sales Price	Gross Margins (Sales Price - Unit Cost)	Unit Costs
      Tricorner Hat	$269.99	$ 114.00	$155.99
      White Powder Wig	$245.70	$ 56.49	$189.21
      Tunic	$477.40	$227.40	$250.00
      Telescope	$137.00	$69.71	$67.29
      Belt	$14.99	$ 13.61	$1.38
      White Hot Pants	$89.46	$ 35.24	$54.22
      Boots of Spanish Leather	$299.99	$149.99	$150.00
      Parchment Paper	$ 14.99	$ 10.08	$ 4.91
      Quill Pen	$ 23.38	$ 11.05	$ 12.33