Download - Group 5 Final
Click on the Hyperlink on the Levels to go to the respective Result sheets
Level Level 1
Level 2
Level 3
Click on the Hyperlink on the Levels to go to the respective Result sheets
DescriptionUsage of Vlookup & Hlookup
In addition to Level 1, Usage of weekday(), Index(), Round(), Sum() & IF function
In addition to Level 1, Usage of Isblank(), Dropdown, Match(), Choose
Flat Tax RateBase Income $ 0 $ 25,000 $ 42,000 $ 72,000 $ 129,500 Alternate 1 0.0% 4.5% 8.0% 12.5% 28.0%Alternate 2 0.0% 5.0% 9.0% 15.0% 25.0%
State AllowanceMA $ 1,100 NH $ 400 VT $ 200
ID #1 $ 97,800 MA $ 10,235 $ 0 $ 19,584 $ 12,225 2 $ 91,900 MA 16,423 0 17,932 $ 11,488 3 $ 51,500 NH 6,384 0 3,975 $ 4,120 4 $ 371,500 VT 0 93,225 130,415 $ 104,020 5 $ 77,200 VT 9,402 0 13,816 $ 9,650 6 $ 23,300 MA 1,254 0 0 $ - 7 $ 292,100 MA 59,629 15,000 102,625 $ 81,788 8 $ 52,700 NH 0 7,200 4,155 $ 4,216 9 $ 96,400 NH 18,000 0 19,192 $ 12,050
10 $ 16,200 OH 227 0 0 $ -
Totals: $ 311,694 $ 239,557
Taxable Income
State of Residence
Actual Withhold-ing Paid
Estimated Taxes Paid
Actual Tax Owed
Flat Tax Alternate 1
Case is about a Fedral Govt who has hired Tokoma considered to be think tank for presenting the analyis to the Govt who take strategic decision and come out with the policies. Fedral govt want to check whether the existing taxation method which is based on graduation method of tax calculation (which varies with the salary amount)is ideal or alternative methods of flat tax shall be preferable. They also want to see what would be the total tax amount in case of Alternate taxations and what would be more preferable by the higher income groups.
FUNCTIONS USED-VLOOKUP- penalties and city AllowanceHLOOKUP- Finding the Tax amount for two diff Alternative.Iferror- To display #N/A values Conditional formatting - To highlight the data
Penalty
Amount Owed Penalty % $ 0 0 100 3% 1,000 5% 5,000 7% 10,000 10% 50,000 15%
$ 14,670 $ 9,349 $ 1,990 $ 4,435 654.43 99.50 221.75 1100 $ 13,785 $ 1,509 $ (4,936) $ (2,638) 75.45 No Penalty No Penalty 1100 $ 4,635 $ (2,409) $ (2,264) $ (1,749) No Penalty No Penalty No Penalty 400 $ 92,875 $ 37,190 $ 10,795 $ (350) 3,719.00 1,079.50 No Penalty 200 $ 11,580 $ 4,414 $ 248 $ 2,178 220.70 7.44 108.90 200 $ - $ (1,254) $ (1,254) $ (1,254) No Penalty No Penalty No Penalty 1100 $ 73,025 $ 27,996 $ 7,159 $ (1,604) 2,799.60 501.13 No Penalty 1100 $ 4,743 $ (3,045) $ (2,984) $ (2,457) No Penalty No Penalty No Penalty 400 $ 14,460 $ 1,192 $ (5,950) $ (3,540) 59.60 No Penalty No Penalty 400 $ - $ (227) $ (227) $ (227) No Penalty No Penalty No Penalty #N/A
$ 229,773 $ 74,715 $ 2,578 $ (7,206) $ 7,529 $ 1,680 $ 331
Flat Tax Alternate 2
Actual Unpaid
TaxUnpaid Tax Alternate 1
Unpaid Tax Alternate 2
Actual Penalty Owed
Penalty Alternate 1
Penalty Alternate 2
State Allowance
Case is about a Fedral Govt who has hired Tokoma considered to be think tank for presenting the analyis to the Govt who take strategic decision and come out with the policies. Fedral govt want to check whether the existing taxation method which is based on graduation method of tax calculation (which varies with the salary amount)is ideal or alternative methods of flat tax shall be preferable. They also want to see what would be the total tax amount in case of Alternate taxations and what would be more preferable by the higher income groups.
FUNCTIONS USED-VLOOKUP- penalties and city AllowanceHLOOKUP- Finding the Tax amount for two diff Alternative.Iferror- To display #N/A values Conditional formatting - To highlight the data
TO GO BACK TO INDEX SHEET PRESS THE
ARROW BELOW
Corporate Request Form
Date Traveler Departure City Arrival City Weekday6/20/2013 Frasier, John 1049 $ 1,875 London Boston 1555 56/25/2013 Simpson, Amanda 1016 $ 499 NY Miami 505 36/30/2013 Wexel, Thomas 1023 $ 175 San Francisco Seattle 339 1
7/3/2013 Wang, Robert 1007 $ 1,199 London NY 1650 4
Flight No.
Corporate Fare
Base Fare
Case is about a travel agency Texto Travel, which books airline tickets for its clients, and has direct tie up with airline to get discounts etc.Our client has asked us for quotes for a some trip. They have also sent us the price which they have recieved from elsewhere for each trip. We need to compute our price and compare it with the price which the client has recieved and in the end figure out if our pprice is lower or higher than theirs.
FUNCTIONS USED-VLOOKUP- Departure City, Arrival City, Base Fare, Airport FeeHLOOKUP- Discounted fareWEEKDAY- Weekday- enter the date it will give the day of week (1 to 7 1 means Sunday, 2 monday--7 Sat)INDEX- Discounts- used in 2 da tables, gives value on the intersection of row and columnROUND- Discounted Fare-To round of the value to nearest decimalSUM- Total Ticket Price- to get the final resultIF- Less than Corporate Rate- to check if the final value is less than or more than what they have.
Corporate Request Form
Discount3 AA 1555 125 1680 TRUE2 Y 253 25 278 TRUE1 AA 339 25 364 FALSE3 Z 578 40 618 TRUE
Fare Category
Discounted Fare
Airport Fee
America Travels Total Ticket Price
Less than Corp. Rate?
Case is about a travel agency Texto Travel, which books airline tickets for its clients, and has direct tie up with airline to get discounts etc.Our client has asked us for quotes for a some trip. They have also sent us the price which they have recieved from elsewhere for each trip. We need to compute our price and compare it with the price which the client has recieved and in the end figure out if our pprice is lower or higher than theirs.
FUNCTIONS USED-VLOOKUP- Departure City, Arrival City, Base Fare, Airport FeeHLOOKUP- Discounted fareWEEKDAY- Weekday- enter the date it will give the day of week (1 to 7 1 means Sunday, 2 monday--7 Sat)INDEX- Discounts- used in 2 da tables, gives value on the intersection of row and columnROUND- Discounted Fare-To round of the value to nearest decimalSUM- Total Ticket Price- to get the final resultIF- Less than Corporate Rate- to check if the final value is less than or more than what they have.
TO GO BACK TO INDEX SHEET PRESS THE
ARROW BELOW
Flight No. Departure City Arrival City Base Fare
1000 NY Denver 585
1001 NY Seattle 929
1002 NY Seattle 939
1003 NY Atlanta 939
1004 NY Atlanta 939
1005 NY Atlanta 839
1006 New York Detroit 516
1007 London NY 1650
1008 San Francisco Detroit 770
1009 London NY 860
1010 NY LA 812
1011 NY Seattle 739
1012 NY DC 270
1013 San Francisco Seattle 275
1014 NY Boston 410
1015 NY Boston 419
1016 NY Miami 505
1017 NY Boston 295
1018 NY Kansas City 762
1019 NY Boston 379
1020 NY Boston 286
1021 NY DC 289
1022 NY Boston 259
1023 San Francisco Seattle 339
1024 San Francisco Kansas City 955
1025 New York Dallas 755
1026 NY Miami 485
1027 San Francisco Atlanta 793
1028 NY Denver 739
1029 NY LA 1270
1030 Chicago Seattle 710
1031 NY DC 203
1032 NY LA 859
1033 San Francisco Denver 364
1034 San Francisco Denver 445
1035 NY LA 559
1036 San Francisco Miami 723
1037 NY DC 302
1038 NY Chicago 809
1039 NY Chicago 669
1040 London NY 1260
1041 NY Chicago 659
1042 NY Chicago 469
1043 NY Chicago 829
1044 London Miami 1319
1045 NY Chicago 682
1046 San Francisco Boston 1275
1047 NY DC 310
1048 San Francisco DC 929
1049 London Boston 1555
1050 San Francisco DC 1174
1051 San Francisco NY 815
1052 NY Indianapolis 765
1053 New York Dallas 559
1054 San Francisco Chicago 925
1055 San Francisco Chicago 919
1056 San Francisco Dallas 513
1057 San Francisco Indianapolis 915
1058 London Chicago 1639
1059 San Francisco LA 340
1060 London Chicago 1290
1061 New York Detroit 533
1062 NY Miami 739
1063 London Atlanta 1420
1064 NY Cleveland 459
1065 Chicago DC 379
1066 New York Houston 682
1067 San Francisco Cleveland 1135
1068 NY Miami 672
1069 San Francisco NY 1210
1070 London DC 1015
1071 New York Houston 889
1072 New York Columbus 755
1073 New York Columbus 319
1074 San Francisco NY 670
1075 San Francisco Houston 549
1076 San Francisco LA 310
1077 London Houston 1820
1078 San Francisco Columbus 949
1079 London LA 2300
1080 Chicago NY 509
Fare Category Ticket Price Airport Fees1 $ - $ 15.00 1 200.00 25.00 2 350.00 35.00 2 500.00 40.00 2 700.00 50.00 3 900.00 60.00 3 1,100.00 100.00 3 1,400.00 125.00
Discount CategoryFare Category
Weekday 1 2 31 AA AA X2 Y Y Y3 Y Y Z4 Y Z Z5 Z Y AA6 X X X7 Y Y Z
DiscountFare Discount Category AA X Y ZDiscount 0% 25% 50% 65%
CKG Auto Car Model InformationMaintenance Cost Base
Model # Car Model Description Engine Weight Class 1st Year 2nd Year 3rd Year1 Pony Compact sedan 4 1 $ 225 $ 275 $ 425 2 Pony Wagon Compact hatchback 4, 6 1 225 275 425 3 Nexus Mid-size sedan 4, 6 2 225 275 425 4 Nexus XL Mid-size luxury sedan 6, 8 2 0 0 0 5 Apollo SUV 6, 8, turbo 3 0 375 525 6 Pandora Sport Compact sports car 6, 8, turbo 1 275 325 450 7 Supra-V Nexus Mid-size sports car 8, turbo 2 275 325 450 8 ScoutAbout Full-size van 8, turbo 3 0 0 450 9 Zetter Mid-size truck 8, turbo 3 325 375 450
Oil Change Information:
5000Cost per Oil Change $ 29
Model # Engine1 42 4 63 4 64 6 85 6 8 turbo6 6 8 turbo7 8 turbo8 8 turbo9 8 turbo
Number of Miles per Oil Change:
Highway Driving: Miles per GallonEngine
Weight Class 4 6 8 turbo1 37 31 29 272 33 28 25 243 NA 24 22 21
Mixed Driving: Miles per GallonEngine
Weight Class 4 6 8 turbo1 33 27 24 232 29 24 20 193 NA 21 18 17
City Driving: Miles per GallonEngine
Weight Class 4 6 8 turbo1 30 24 20 182 25 21 18 173 NA 18 16 15
Name Abbreviation Region Region#ALABAMA AL SE 2 Region ListingALASKA AK W 5 SE 2ARIZONA AZ SW 4 W 5ARKANSAS AR SE 2 SW 4CALIFORNIA CA W 5 NE 1COLORADO CO W 5 MW 3CONNECTICUT CT NE 1DELAWARE DE NE 1
DC
NE 1FLORIDA FL SE 2GEORGIA GA SE 2HAWAII HI W 5IDAHO ID W 5ILLINOIS IL MW 3INDIANA IN MW 3IOWA IA MW 3KANSAS KS MW 3KENTUCKY KY SE 2LOUISIANA LA SE 2MAINE ME NE 1MARYLAND MD NE 1MASSACHUSETTS MA NE 1MICHIGAN MI MW 3MINNESOTA MN MW 3MISSISSIPPI MS SE 2MISSOURI MO MW 3MONTANA MT W 5NEBRASKA NE MW 3NEVADA NV W 5NEW HAMPSHIRE NH NE 1NEW JERSEY NJ NE 1NEW MEXICO NM SW 4NEW YORK NY NE 1NORTH CAROLINA NC SE 2NORTH DAKOTA ND MW 3OHIO OH MW 3OKLAHOMA OK SW 4OREGON OR W 5PENNSYLVANIA PA NE 1RHODE ISLAND RI NE 1SOUTH CAROLINA SC SE 2SOUTH DAKOTA SD MW 3TENNESSEE TN SE 2TEXAS TX SW 4
DISTRICT OF COLUMBIA
UTAH UT W 5VERMONT VT NE 1VIRGINIA VA SE 2WASHINGTON WA W 5WEST VIRGINIA WV SE 2WISCONSIN WI MW 3WYOMING WY W 5
Average Gas PricesRegion Average price per gallon
1 $3.00 2 2.89 3 2.97 4 3.25 5 3.29
Insurance Estimate Calculator
Region Base Cost1 $ 2,100 $ 350 $ 550 $ 375 $ 400 $ - $ - 2 1,570 325 475 375 220 - - 3 1,290 300 450 375 200 - - 4 1,070 300 450 375 100 - - 5 2,350 350 625 375 500 - -
Rural (1)City 1 Suburban - Excellent (1)Poor 1 Average -
Deduction for Excellent Driver
Addition for Poor Driver
Addition for City Residence
Deduction for Rural Residence
Addition/Deduction for suburban
Addition/deduction for average
Customer Input
Expected number of driving miles per yearType of DrivingState of ResidenceResidential StatusDriving Safety Record
Vehicle InputVehicle model numberEngine:Number of cylinders
Operating cost
Car Make Car DescriptionEstimated annual cost of gasEstimated annual insuranceEstimated average annual maintenance costEstimated total cost
Gas Price Adjustment percentage: (Calulate this by dividing the current gas price in your neighborhood by 3.0)
Case is about a Vehicle dealer, who wants to find out which model of teh car would suit to the customer based on the different driving habits and also find out the estimated expenses on the car based on the total run, maint espenses insurance etc. The Form should also tell which model & cylinder type would be idea for the particular customer.
FUNCTIONS USED-VLOOKUP & Hlookup - To decide the Make & models, Gas price, Insurance etc. INDEX- Type of driveMatch - To match type of drive with the MileageAverage- For finding the Average of the mileagae and other parametersSUM- Totalling the valuesISBLANK - to check the type of entry.
25000Mixed
ALASKASuburban
Average
1.09666666666667
Option 1 Option 2 Option 3 Option 41 4 5 74 6 8 turbo
Option 1 Option 2 Option 3 Option 4
Pony Nexus XL Apollo Supra-V NexusCompact sedan Mid-size luxury sedan SUV Mid-size sports car
$2,492 $3,427 $4,569 $4,329$2,350 $2,350 $2,350 $2,350
$453 $145 $445 $495$5,296 $5,922 $7,364 $7,174
Case is about a Vehicle dealer, who wants to find out which model of teh car would suit to the customer based on the different driving habits and also find out the estimated expenses on the car based on the total run, maint espenses insurance etc. The Form should also tell which model & cylinder type would be idea for the particular customer.
FUNCTIONS USED-VLOOKUP & Hlookup - To decide the Make & models, Gas price, Insurance etc. INDEX- Type of driveMatch - To match type of drive with the MileageAverage- For finding the Average of the mileagae and other parametersSUM- Totalling the valuesISBLANK - to check the type of entry.
TO GO BACK TO INDEX SHEET PRESS THE
ARROW BELOW
Option 534
Option 5
NexusMid-size sedan
$2,836$2,350
$453$5,640