Changes between Version 4 and Version 5 of acquisitionsWindsor


Ignore:
Timestamp:
Oct 1, 2009, 6:20:11 PM (13 years ago)
Author:
Art Rhyno
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • acquisitionsWindsor

    v4 v5  
    11= Acquisitions at Windsor =
     2
     3Windsor uses Excel 2007 Workbooks to support acquisitions processing. The layout of the worksheets resembles a printed form.
    24
    35[[Image(acqbib1.JPG)]]
    46
     7In 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.
     8
    59[[Image(acqbib2.JPG)]]
     10
     11We 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.
    612
    713[[Image(acqbib3.JPG)]]
    814
     15The slimpac is a very stripped down display.
     16
    917[[Image(acqbib4.JPG)]]
     18
     19It is necessary to go to the full display of an item to create the Line Item entry.
    1020
    1121[[Image(acqbib5.JPG)]]
    1222
     23Selecting the ''Capture'' button retrieves the record and populates the row of the worksheet. There is then a notification that the record has been added.
     24
    1325[[Image(acqbib6.JPG)]]
     26
     27Bookbags can also be imported to Line Items. In order to access a list of bookbags, it is necessary to supply a password.
    1428
    1529[[Image(acqbib7.JPG)]]
    1630
     31The bookbags are presented as a drop-down list. This is the quickest option for adding large numbers of Line Items for ordering.
     32
    1733[[Image(acqbib8.JPG)]]
     34
     35Each row on the Purchase Order is added or deleted through buttons. Multiple rows can be added at once.
    1836
    1937[[Image(acqbib9.JPG)]]
    2038
     39The calculations for PO pricing are done as a formula. Currency conversions are displayed in the base currency on the right of the PO values.
     40
    2141[[Image(acqbib10.JPG)]]
     42
     43The 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.
    2244
    2345[[Image(acqbib11.JPG)]]
    2446
     47We 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.
     48
    2549[[Image(acqbib12.JPG)]]
     50
     51One 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.
    2652
    2753[[Image(acqbib13.JPG)]]
    2854
     55The 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.
     56
    2957[[Image(acqbib14.JPG)]]
     58
     59Excel has some odd page formatting support. For a printed PO, the ''Page Break Preview'' is very handy.
    3060
    3161[[Image(acqbib15.JPG)]]
    3262
     63Sections 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.
     64
    3365[[Image(acqbib16.JPG)]]
     66
     67In 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.
    3468
    3569[[Image(acqbib17.JPG)]]
    3670
     71The Invoice/Processing worksheet is more complicated. Almost every activity outside of ordering takes place here.
     72
    3773[[Image(acqbib18.JPG)]]
     74
     75Although a calculator is made available in the PO sheet, it has the most application in the Invoice sheet.
    3876
    3977[[Image(acqbib19.JPG)]]
    4078
     79A 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.
     80
    4181[[Image(acqbib20.JPG)]]
     82
     83Otherwise, the invoice items are searched and processed based on Line Items.
    4284
    4385[[Image(acqbib21.JPG)]]
    4486
     87We calculate a subtotal based on the Line Item information.
     88
    4589[[Image(acqbib22.JPG)]]
     90
     91Clicking the GST button will automatically pull the subtotal and apply the GST rate specified in preferences.
    4692
    4793[[Image(acqbib23.JPG)]]
    4894
     95Shipping is a fairly involved layer of Invoicing and needs extra calculations.
     96
    4997[[Image(acqbib24.JPG)]]
     98
     99In 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 remain ''non-rebateable'' portion.
    50100
    51101[[Image(acqbib25.JPG)]]
    52102
     103The ''Send to Finance'' option creates a flat file which is used the campus FIS application.
     104
    53105[[Image(acqbib26.JPG)]]
     106
     107The ''masterlist'' is used to track all changes.
    54108
    55109[[Image(acqbib27.JPG)]]
    56110
     111In the ''settings'' workbook, total figures are automatically calculated with each change.
     112
    57113[[Image(acqbib28.JPG)]]
    58114
     115We 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.
     116
    59117[[Image(acqbib29.JPG)]]
     118
     119The ''Preferences'' sheet in the main workbook can be modified per user. This determines, for example, the default vendor and currency for a new order.
    60120
    61121[[Image(acqbib30.JPG)]]