Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

K327 Exam 1: Using Data Retrieval functions to automate a form

Despite the advent of technology, many processes in business are still manual.  The INVOICE form (Example 1) is an example.  In spite of the fact that most of the data for this form is in another file called MASTER SCHEDULE, 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.

Master Schedule is the heart of 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 Invoice form.  In addition, there are tables provided in the Invoice 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 (2014 or 2015) payment is received.  For both using the 20% and additional discount, a wholesale price is calculated.

The Goal: automate this form by linking into Master Schedule and using Data Retrieval functions. Use the 2015 data in Master Schedule; all Serial Numbers begin with 2015. Insure that your model is dynamically linked to Master Schedule; change some data and see if those changes are reflected in your model.  This includes the options listed in the Pricing Rules document.  You may not change anything in, or add to, the Master Schedule.

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; use only functions and formulas which will be compatible with ANY version of Excel.   Make it as dynamic as possible to minimize the number of formulas and functions you need to write/update. Keep the Excel files which will be linked together in the current Excel folder.  Open the files from that folder and link them together.  BY doing so, the links should follow the folder as it moved from location to location.  NOTE: IF YOU LINK THE ENTIRE MASTER SCHEDULE INTO THE INVOICE MODEL, YOU WILL RECEIVE ZERO FOR THE FINAL SCORE

Note:  PDF files are provides as templates, but your work may not match the numbers on those files.  Confirm your work by looking at the options in Master Schedule for specific serial numbers.

A good plan but not implemented well means you have further work to do in understanding the analytical tool.   A poor plan but working model may imply that you either don’t need this course or that you were lucky.  “Lucky” is not a great career model.  Remember, there will come a time when these plans are not for analytical models, but for projects, new product development and new businesses.  Learn how to develop and implement a plan now.

Note: this is an individual project and the Kelley Honor Code is in effect. Sign the Kelley Honor Code in the Invoice workbook; failure to do will result in no credit for the exam.

To upload: Place all files (including Master Schedule) in a folder named K327_Exam1_yourusername.  Include the folder where the files were linked together; Master Schedule MUST be in the same folder as the Invoice model to test the links and assess that they are dynamic.

Select the folder and zip it: Right click=> Send to=> Compressed (Zipped) folder.  Upload to Canvas.