Tractor Status

Despite the advent of technology, many processes in business are still manual. The Order Conformation form (Example 1) is an example. In spite of the fact that most of the data for this form is in another file called Tractor Status, and the rest of the data can be assembled into small tables, the input for this is still a manual operation. Historically, it has taken several WEEKS to process all incoming orders at the beginning of the model year. However, this has been for 200 orders; the company has grown and now has 400+ orders in-house. Clearly, this needs to be automated in some way. Tractor Status is the Master Schedule for the organization. While most organizations may have this in some database, the advantage of using Excel is the flexibility in adjusting weekly and monthly builds, as well as accessibility and ease of use. The Serial Number field is the Primary Key. Each row is a specific record, and contains the necessary information to build each tractor to a specific configuration. It also contains order information: Who (Dealer), Series or Model, Date Ordered, Month to be Built, Requested Month to be Built and PO number. Much of this information can be used to populate the Order Confirmation form. In addition, there are tables provided in the Conformation Model Template, which includes Option Pricing, Dealer names and addresses, Standard configurations and base price for each model and a table for Terms by Date. The logic for the terms is as follows. An MSRP is calculated by adding the base price and option prices for each order. Each dealer receives a discount of 20%. In addition for US dealers, an additional discount is earned depending upon the quantity ordered; a number is set as a dealer goal at the beginning of each model year. There are three levels: Platinum, Gold and Silver. Depending upon the level for which a dealer has currently qualified, several options are listed on the form, as well as a box for selecting which discount percentage. For Canadian Dealer, the discount depends upon the model ordered and which calendar year (2013 or 2014) payment is received. For both using the 20% and additional discount, a wholesale price is calculated. The Goal: automate this form by linking into Tractor Status and using Data Retrieval functions. Use the 2014 data in Tractor Status; all Serial Numbers begin with 914. Insure that your model is dynamically linked to Tractor Status; change some data and see if those changes are reflected in your model. Deliverables: Study the data and business rules. Develop a plan to complete this analysis. Detail what need to be done on a high level, what specifically need to be done with the data, and what formulas/functions could be useful in the process. Update the plan as you proceed if there are issues that force you to change the plan. Do this in a Word document. Implement the plan in Excel. Make it as dynamic as possible to minimize the number of formulas and functions you need to write/update. Note: PDF files are provides as templates, but your work may not match the numbers on those files.