Macs Magazine Software Rebuild

1. Software Requirements Macs Magazine Software Rebuild Review Document 11/16/11 1. Define general areas of basic functions first. 2. Define processes that need to occur within each basic function. 3. Define any potential areas of difficulty or risk (beyond basic programming). 4. Describe the basic flow of information during the normal course of business operations. • Maintain Inventory, • Track Expirations, Track Outdated Stock, • Various Reports, • Etc. • Determining Quantities, • Generate Orders, Adjusting Inventory, • Etc. • Maintain Customer Data, Customer Trending, • Various Reports, • Etc. Inventory (Static) 2. Customers (Static) 3. Allocation (Semi-Dynamic) • Generating Invoices, • Printing Invoices, • Tracking Routes, • Etc. • Counting Returns/Rejects, • Closing Invoices, • Various Reports, • Etc. 4. Invoicing (Static) 5. Reconciliation (Semi-Dynamic) Basic Workflow *Definition of the data needed to execute each process will be provided at a later date.

2. Maintain Inventory, • Track Expirations, Track Outdated Stock, • Various Reports, • Etc. Inventory (Static) 3. Allocation (Semi-Dynamic) 5. Reconciliation (Semi-Dynamic) • Maintain Total Inventory: • Enter new products’ bar code manually or scanned. Quantity is always entered manually. • Supplier has to be included with each product. [Needed for Reconciliation Function] • Cost-price (discount) has to be included. • Type of product has to be included and editable (currently commodity categories). [Needed for Multiple Functions.] • Frequency has to be included (see notes below). • Change name and price of product (same upc), ‘on-the-fly’. • Manually make products inactive or active, inactive stays in ‘products carried’ but does not show up in ‘inventory counts’. • Need to be able to reactivate an old products that were made ‘inactive’. • Manually make products ‘dead’. Dead is removed from the database. • Need to be able to ‘forward-allocate” (put products into the system before they arrive, then change quantity and/or title • as applicable.) • Need to account for a different number of digits in foreign magazine barcodes. Can the scanner software be set up to read multiple numbers of digits on barcodes? Can the system be set up to input multiple bar codes manually? If not, these products have to be manually counted during Reconciliation. [This automates the Reconciliation Function] A fool-proof date function has to be set up to account for different frequencies of expiration and 2 important dates, the date that we can no longer distribute the product, and the date that we can no longer return the product to our supplier. This is critical for almost every phase of the operation. Date function must have manual overrides. [This automates the Reconciliation Function] Main Reports/Screens: 1. Total products carried. [This generates the title sheet- Misc Functions] 2. Total in stock merchandise (currently called the ‘Stock Report’, multiple views (same data) have to be defined) 3. View and print list by product type.

3. Maintain Customer Data, Customer Trending, • Various Reports, • Etc. 2. Customers (Static) 3. Allocation (Semi-Dynamic) 4. Invoicing (Static) • Maintain Customer Data: • Enter new customers. • Update customer information. • * Add field for customer email. • * Add field for distance from warehouse (we can get this from Google). • * Possibly, add a field for link to Google map of customer location (simple hypertext link) • Type of business has to be included in customer data (bodega, supermarket, hospital, newsstand, etc., • need to be able to add and update these classifications ). • Need solid validation for all fields of customer data. • Assign customers to routes. • Define routes. • Need to be able to add/change to amount of shipping and handling to each customer (manual entry). • Assign stop numbers to routes. • Make customers ‘inactive’- absolutely no allocation, do not appear on route sheets (leave ability to reactivate). • Make customers ‘dead’- gone from the database. • *NEW- • Customer Reliability. • Total money collected over previous weeks. • Bounced checks • Return Shorts • Rejects • Refusals • Distance • Other Issues Have to create a new, editable Customer Pricing Matrix. 5-10% are charged a different price than others, and the price depends on the type of magazine. Current matrix can probably be used but must be editable. [This effects the Invoicing Function] There is currently a customer priority feature (1-9?). The priority may not have a criteria and the ‘Customer Reliability’ idea above may need to be implemented. [This effects the Invoicing Function] Main Reports/Screens: View and change- customer by product title. View and change- product title by customer. View and change- customer by route. View and change- route by customer. View/Change/Print- customer priority. View/Change/Print- customer by category. View/Change/Print- route by sequence. View/Change/Print- customer sales history (profit generated).

4. Determining Quantities, • Generate Orders, Adjusting Inventory, • Etc.. 3. Allocation (Semi-Dynamic) Inventory (Static) 4. Invoicing (Static) 2. Customers (Static) • Determine Customer Shipments: • Assign products to customers. • Assign specific quantities of products to each customer. • The Inventory Draw Form/Display should be editable to increase/decrease the amount of each specific product on a per-customer basis. • The Customer Draw Form/Display should be editable to increase/decrease the amount of every specific product to a specific customer (this form is currently exported into Excel, updated, then imported back into the main program, as a .txt file ?). • The Allocation Table Form/Table itself should be editable with changeable quantities of product (this would be an override for last minute, quick changes (this form is currently exported into Excel, updated, then imported back into the main program, as a .txt file ?). • Customer Priority? [From the Customer Function] • Copy allocations customer-to-customer. • Copy allocations title-to-title. • Make customers ‘inactive’- absolutely no allocation, do not appear on route sheets (leave ability to reactivate). • Make customers ‘dead’- gone from the database. • Should be able to review inventory immediately after each allocation to check on any product not shipped and manually • reallocate. Main Allocation Process (Reports): Allocation Table: Exact Number of product to each customer. Have to create a new, editable over-draw and under-draw functions. The simplest way is to pull up a product-by-customer view and change how many each customer gets. [This effects the Invoicing Function] This needs to be looked at in depth, an earlier version of some other software had advanced functions that need to be discussed (warnings and options for over-draw and under-draw scenarios, etc.). [This effects the Invoicing Function]

5. 3. Allocation (Continued) The allocation function has a desired amount of product built in and editable for each product for each customer. An important point is that this amount needs to be totaled for ordering purposes with a tighter grip on exactly how much of each product is needed. [This effects the Invoicing Function] Main Reports/Screens: Customer Draw (currently in Excel , format of existing form can be retained). Inventory Draw (currently in Excel, format of existing form can be retained). Complete Allocation Table (per schedule).

6. Generating Invoices, • Printing Invoices, • Tracking Routes, • Etc. 4. Invoicing (Static) 3. Allocation (Semi-Dynamic) • Prepare Inventories: • Print order invoices. [From Allocation Functions] • Need to be able to add/change to amount of shipping and handling to each customer (manual entry). • Quick-change invoice based on last -minute product overages and shortages. • [This should auto-update the Inventory Function] • Batch print by route. • Blanket messages in new invoice format at bottom. • Need to be able to void invoice and send all product back to inventory. • Print previous invoices (invoice history for customers by date delivered). • Rejected returns (if applicable ) from last delivery are included in days delivery for return to customer. • From Reconciliation Functions] • Reject sheets are provided showing discrepancy between what was counted in last returns and amount on return sheet. Main Reports/Screens: Invoices. [This should auto-update the Inventory Function] Invoices by invoice number before deliveries (for specific date), including total charges and balances with grand totals. Invoices by invoice number after deliveries (for specific date), including total charges and balances with grand totals. In The Field: • Each truck goes out on it’s route. • Each package is dropped off. • The customer may or may not have product to return. • Returned product is detailed by the customer and added up using our return sheet (attached to every invoice, each week) • The amount of the customer’s return is manually (on-the-spot, based on the return sheet amount) deducted from the customers current bill. • The current invoice is annotated with a credit or debit amount (invoice total minus returns for the day). • The customer gets a copy of the annotated invoice. • The delivery person returns to the warehouse with an annotated invoice, and returns with associated return sheet if returns were given.

7. Back From Field: • Counting Returns/Rejects, • Closing Invoices, • Various Reports, • Etc. The delivery person returns to the warehouse with an annotated invoice, and returns with associated return sheet if returns were given. 5. Reconciliation (Semi-Dynamic) Inventory (Static) 4. Invoicing (Static) • Reconcile Product Returns: • Use mounted hand scanners to input bar codes for each piece returned. • Scanning employees provided with a copy of the day’s invoice and a copy of the return sheet filled out by the customer.. • Products returned that we do not distribute are unable to scan because their upc is not in our database. • These products are labeled ‘Not Ours’, and are not added to the return amount (merchandise is physically sent back to customer). • Products returned that are over the amount we shipped out are unable to scan because their upc and quantity shipped to the customer • is compared to our data. These products are labeled ‘Not Ours’, and are not added to the return amount • (merchandise is physically sent back to customer). • Products returned that are too old for us to send back to our supplier are detected (scanner or visual). • These products are labeled ‘Too Old’, and are not added to the return amount (merchandise is physically sent back to customer). • . • Product is determined as reuse based on date from scanning of upc. The proper Reuse date means that the product • can be shipped out again in a later ‘run’. • Reuse product is automatically counted and added back to inventory upon scanning and goes down it’s own physical path. • [This effects the Invoicing Function] • Product is determined as non-reuse based on date from scanning of upc. The proper Non-reuse date means that the product • can still be shipped back to our supplier for our refund. • Products past the non-reuse date should have already been picked up by the scanners as ‘Too Old’ (just to account for 0 product). • Reuse product is automatically counted and totaled for reporting to our supplier and goes down it’s own physical path. • Report for returns to supplier (format specified by the supplier). This form has to be editable for printing • (Changes nothing in our accounting system). Scanning Function Inventory (Static) Reuse Non-Reuse Undistributed Product Rejects : Not Ours Too Old

8. 5. Reconciliation (Continued) • Personnel doing scanning must be able to close out invoice. • Need better pop-ups for reject error messages. • Amount of returns annotated on invoice copy should match total amount of returns scanned in for customer. • Return sheet is just used for comparison of what customer thought they had returned compared to what they physically gave back. • Discrepancy in returns generates a returns short form. The return short form does not specify what or how many magazines were miscounted, it just shows the total of what was scanned vs. the total claimed by the customer. • Any discrepancy in return amounts means that there is a discrepancy in the total balance of the invoice. If the customer claimed more than the actual return and this amount was subtracted from the invoice (on the road), the result would be that the customer paid less than was required. This results in a charge-back to the customer, seen as a debit on the next invoice. If the customer claimed less than the actual return and this amount was subtracted from the invoice (on the road), the result would be that the customer paid more than was required. This results in a credit to the customer, seen as a credit on the next invoice. • Employees doing scanning are authorized to make minor adjustments to invoice discrepancies to ‘close-out’ invoices. • Closed-out invoices automatically update the customer’s running balance. [This effects the Invoicing Function] • This whole process is determined by the date function (previously noted in step 1) has to be set up to account for different frequencies of product expiration and 2 important dates, the date that we can no longer distribute the productand the date that we can no longer return the product to our supplier. This is critical for almost every phase of the operation. Date function must have manual overrides. [This has to function to automate the Inventory Function] Main Reports/Screens: Invoices by invoice number after deliveries and reconciliation (for specific date). Returns by customer. Total returns for each supplier (Return scan report). Return sheets (different sheets depending on Customer Pricing Matrix). Returns short form (per customer). Reject summary by customer, day, route. Reused Report (includes: premature returns, unused product, etc.).