Acquisitions at Windsor

Windsor uses Excel 2007 Workbooks to support acquisitions processing. The layout of the worksheets resembles a printed form.

In our setup, we use several workbooks, and each staff person gets one that is a local copy. This allows preferences and other individual settings to be supported. Bibliographic information for Line Items is assembled on its own worksheet. The title can then be added to the PO through a drop down list. The Line Items are retrieved directly from the catalogue.

We use the slimpac interface though the full blown interface is also accessible. The browser control in Excel is cranky and the slimpac is a much smoother experience.

The slimpac is a very stripped down display.

It is necessary to go to the full display of an item to create the Line Item entry.

Selecting the Capture button retrieves the record and populates the row of the worksheet. There is then a notification that the record has been added.

Bookbags can also be imported to Line Items. In order to access a list of bookbags, it is necessary to supply a password.

The bookbags are presented as a drop-down list. This is the quickest option for adding large numbers of Line Items for ordering.

Each row on the Purchase Order is added or deleted through buttons. Multiple rows can be added at once.

The calculations for PO pricing are done as a formula. Currency conversions are displayed in the base currency on the right of the PO values.

The workbooks update one shared masterlist, a workbook that is treated as the general ledger. The Place Order button updates the masterlist, and a confirmation is provided. We check to make sure each PO entry is unique.

We use a separate worksheet for printing out or producing a PDF of an order. A PO can be looked up in several ways, and there is support for type-ahead queries as well.

One really useful trick is to use Control-PageDown? to jump to the end of the PO list, which is always presented in the order that POs are created.

The worksheet is populated based on selections and searching. The contents can be modified for printing purposes, but no changes are stored in the masterlist from this worksheet.

Excel has some odd page formatting support. For a printed PO, the Page Break Preview is very handy.

Sections of the PO are defined as "unprintable" and this is where staff notes on a PO would appear but not show up on the printed form. We may want to sometimes fit printable sections on one page.

In this case, we decide we want the Ship To and Bill To sections to be on the same page as the rest of the order.

The Invoice/Processing? worksheet is more complicated. Almost every activity outside of ordering takes place here.

Although a calculator is made available in the PO sheet, it has the most application in the Invoice sheet.

A PO can be processed all at once, and looking up a PO number will populate the Invoice with all Line Items associated with a particular PO.

Otherwise, the invoice items are searched and processed based on Line Items.

We calculate a subtotal based on the Line Item information.

Clicking the GST button will automatically pull the subtotal and apply the GST rate specified in preferences.

Shipping is a fairly involved layer of Invoicing and needs extra calculations.

In this case, selecting the Shipping button will pull the charge entered in the cell, apply one rate specifed in preferences for the percentage of rebateable GST and come up with another figure for the remaining non-rebateable portion.

The Send to Finance option creates a flat file which is used for interfacing with the campus FIS application. The library is required to provide this interface in order to process payments.

The masterlist is used to track all changes.

In the settings workbook, total figures are automatically calculated with each change.

We need to provide access to approximately 150,000 Line Items that have been created since 1997. This is possible in Excel and we allow searching across all Fiscal Years we have data for. Excel is neither a search engine nor a database, so the sophistication of searching is far short of what is supplied in the catalogue.

The Preferences sheet in the main workbook can be modified per user. This determines, for example, the default vendor and currency for a new order.

Last modified 13 years ago Last modified on Oct 1, 2009, 6:25:16 PM

Attachments (31)