ase Study: Infoligent Education Limited
Case Scenario
Kevin is a regional sales manager for the Infoligent Education Limited. The company's sales force is responsible for promoting books to higher learning institutions around the globe. Kevin is one of Infoligent's many regional sales managers and has many responsibilities which include visiting with current and potential customers, supervising a growing sales staff, preparing numerous reports and tracking his sales region's expenses. Each week, members of Kevin's sales staff submit weekly expense claim forms. Currently, he scans through the expense forms, checks for anything out of the ordinary and then authorizes reimbursement cheques.
He needs to become more organized about tracking his sales staff's expenses and asks you to design an expense worksheet for him. By using a spreadsheet application to analyze his sales force's expenses, he feels that he will save time and make better decisions. You and your team members will be provided with the Expense worksheet (in [login to view URL]) to start off and are required to perform the following tasks:
BDS3014 Decision Support Systems for Knowledge Management Assignment
2
Excel functions and Nesting functions
Task 1:
Create a separate worksheet named "mileage lookup table" to store the mileage lookup table as shown below:
Infoligent Education Limited
Position
Allowable Miles
Rate
MN
SU
S2
S1
700
600
550
500
0.20
0.22
0.25
0.32
When a sales representative is hired, he is issued a company car. Each week, the sales representative reports the number of miles that he has driven that week. For any miles over mileage limit, the employee is charged an overage fee. The allowable mileage and charge rate vary by sales position (MN=Manager, SU=Supervisor, S2= Sales representative2, S1=Sales representative1) within the company.
Task 2:
Based on the given worksheet (i.e. Expense), you need to further improve the worksheet to make it appear more consistent, professional and well-organized. For example, apply proper formatting to the cells like Ringgit Malaysia sign with two decimal places.