create order reports in excel

User Generated

unlqra69sna

Business Finance

Description

Use the attached instructions, spreadsheet, data, etc. to enable you to complete an orders report for a fictional skiing equipment company as specified.

Unformatted Attachment Preview

DYS542: Harvesting Spreadsheet Da Dyson, Cornell SC Johnson College of Busine Harvesting Data Using Microsoft Excel's Logic and Data R Course Project Instructions: To submit this assignment, please refer to the instructions in the course. Copyright © 2018 eCornell. All rights reserved. All other copyrights, trademarks, trade names, and lo DYS542: Harvesting Spreadsheet Data Dyson, Cornell SC Johnson College of Business Harvesting Data Using Microsoft Excel's Logic and Data Retrieval Course Project Instructions: This is the workbook for your course project. It contains all instructions and data to complete your work throughout the modules in the course. Complete each part as you reach the related project page. Make sure to save all progress often, as you will be submitting this workbook for review later. Working through this project, you will have the opportunity to put your dataretrieving and logic function skills to the test. In Part One of the project, you will analyze a fictional insurance agency's business to determine whether altering the distribution of ages in their client pool might impact the financial outcomes. In Part Two of the project, you will determine which applications to approve for a To submit this assignment, please refer to the instructions in the course. Copyright © 2018 eCornell. All rights reserved. All other copyrights, trademarks, trade names, and logos are the so es, and logos are the sole property of their respective owners. Downslope Ski Gear is a ski apparel company that provides apparel to all the ski mountains in the Northeast. They have decided to expand to the rest of the United States and have started an online store where they will continue selling their core skiaccessory items: snow pants, hats, gloves. They will also start selling goggles and helmets in bulk to stores. Discounted prices will be applied to bulk orders. Downslope Ski Gear charges different shipping rates by region of the country and shipping method. This policy is making invoicing a bit tricky, so you've been called in to help complete the orders report. 1 2 3 4 Part Three Instructions: Downslope Ski Gear is a ski apparel company that provides apparel to all the ski mountains in the Northeast. They have decided to expand to the rest of the United States and have started an online store where they will continue selling their core skiaccessory items: snow pants, hats, gloves. They will also start selling goggles and helmets in bulk to stores. Discounted prices will be applied to bulk orders. Downslope Ski Gear charges different shipping rates by region of the country and shipping method. This policy is making invoicing a bit tricky, so you've been called in to help complete the orders report. Using the Pricing worksheet, you will need to determine the total order cost for each order before calculating shipping. Begin by calculating the cost per item according to the price schedule. To make referencing easier, apply the name "Price" to the data range you need on the Price Schedule worksheet and think about whether you will use the VLOOKUP or HLOOKUP function. Return to the Orders worksheet and write your function such that it can be copied down to calculate the order cost for each item based on the quantity ordered. Then calculate total order cost in Column M. Next, use the State-Region Table on the Region tab and the VLOOKUP function to identify the correct region for each order. Place this information in Column N. The shipping information is based on three different methods: Truck, Rail, or Air. Use the HLOOKUP function and the first table on the Shipping tab to find the correct number reference for the shipping method. Place in Column P. Apply the name "Ship1" and "Ship2" to the two shipping tables to make referencing easier. Then use an INDEX function to determine shipping cost. In Column S, add shipping cost to total cost before shipping to find total cost after shipping. Order Quantitiy Order Number State 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 ND OH OK OR PA RI MI AL NY MA MO VT VA NM NY WI WY KS FL GA HI KY LA OH OK NH NJ Snow Pants 1850 1187 1536 648 261 1928 935 1981 732 78 838 77 1753 349 761 1841 259 33 1570 1499 741 784 147 762 322 406 1231 Hats 1333 553 1928 1099 299 1009 304 282 953 256 596 701 1846 838 227 1799 424 1431 1777 1724 1996 384 909 10 60 1757 924 Product Cost Gloves Helmets 1784 330 1493 824 1936 1839 699 1412 932 1889 276 1235 31 161 1637 87 688 1990 195 185 1743 790 1314 1325 1678 393 299 334 752 956 1927 1463 291 1313 1244 1282 343 954 1865 96 1116 224 520 230 739 1217 1179 27 195 1997 55 1000 735 344 Goggles 1430 1324 518 1002 333 1076 580 970 644 659 1307 1101 1750 334 299 329 1707 891 73 1272 1218 878 1935 1774 1058 1937 64 Snow Pants Total Hats Total Product Cost Gloves Total Helmets Total Goggles Total Total Cost Region Number Method of Shipping Truck Rail Rail Air Truck Air Air Rail Truck Air Air Air Truck Rail Air Truck Truck Air Air Rail Air Truck Air Rail Air Truck Rail Method of shipping number Customer Preference 2 2 2 1 1 2 2 1 2 1 2 2 1 2 1 1 1 2 1 2 1 2 2 1 2 1 1 Shipping Cost Total Cost Price Schedule for Bulk Orders Qty 0 250 500 1000 2000 Snow Pants Hats $ 45.00 $ 15.00 43.50 13.50 40.00 11.00 35.50 9.00 31.00 7.75 Gloves $ 29.95 28.45 27.55 26.96 25.46 Helmets $ 23.95 22.75 22.03 21.56 20.36 Goggles $ 14.95 14.20 13.75 13.46 12.71 Name Abbreviation Region Region # ALABAMA AL SE 2 ALASKA AK W 5 ARIZONA AZ SW 4 ARKANSAS AR SE 2 CALIFORNIA CA W 5 COLORADO CO W 5 CONNECTICUT CT NE 1 DELAWARE DE NE 1 NE 1 DISTRICT OF DC FLORIDA FL SE 2 GEORGIA GA SE 2 HAWAII HI W 5 IDAHO ID W 5 ILLINOIS IL MW 3 INDIANA IN MW 3 MW 3 IOWA IA MW 3 KANSAS KS SE 2 KENTUCKY KY SE 2 LOUISIANA LA NE 1 MAINE ME NE 1 MARYLAND MD NE 1 MASSACHUSET MA MW 3 MICHIGAN MI MW 3 MINNESOTA MN SE 2 MISSISSIPPI MS MW 3 MISSOURI MO W 5 MONTANA MT MW 3 NEBRASKA NE W 5 NEVADA NV NE 1 NEW NH NE 1 NEW JERSEY NJ SW 4 NEW MEXICO NM NE 1 NEW YORK NY NORTH NORTH DAKOTA OHIO OKLAHOMA OREGON PENNSYLVANIA RHODE ISLAND SOUTH SOUTH DAKOTA TENNESSEE TEXAS UTAH VERMONT VIRGINIA WASHINGTON WEST VIRGINIA WISCONSIN WYOMING NC ND OH OK OR PA RI SC SD TN TX UT VT VA WA WV WI WY SE MW MW SW W NE NE SE MW SE SW W NE SE W SE MW W 2 3 3 4 5 1 1 2 3 2 4 5 1 2 5 2 3 5 Truck Rail 1 Air 2 3 Ship Table 1 - Regular Customer Orders $/CF Region 1 2 3 4 5 1 $ 20.00 $ 23.00 $ 24.00 $ 25.50 $ 28.00 $ $ $ $ $ 2 22.00 27.00 31.00 33.00 36.50 3 $ $ $ $ $ 51.00 53.50 54.00 58.00 60.00 Ship Table 2 - Preferred Customer Orders $/CF Region 1 2 3 4 5 1 $ 18.00 $ 19.50 $ 21.00 $ 23.50 $ 27.00 $ $ $ $ $ 2 24.00 26.00 28.50 30.00 32.50 3 $ $ $ $ $ 44.00 46.00 49.75 52.00 55.75
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

This question has not been answered.

Create a free account to get help with this and any other question!

Related Tags