Fast please

BUS 150 Exam 1 - Summer B, 2020

By taking this exam, you agree to the following:

• I will adhere to the UM honor code that I will not cheat in the exam or share the exam.

• I will check that my completed exam file has been correctly uploaded to Blackboard and all the work I did was viewable on Blackboard. If my file has an error, such as empty file or if I fail to upload it to blackboard, I will get

“zero” points for this exam.

All items below potentially carry points, especially document handling (e.g. renaming files) and formatting (e.g. data formatting, positioning graphs). Many of the steps are independent of each other, if having difficulty in one work on next.

-20% of grade if exam is uploaded within 10 mins pass deadline; “zero” points if exam is uploaded thereafter. General requirements:

a) MS Excel 2016 is the software to be used to resolve all steps below and ONLY open files given in Exam 1. b) For all MS Excel calculations, you must be efficient - use functions, formulas, and always make cells reference. c) Perform ONLY the tasks specified below. Any extra steps, like adding worksheets or cols, could impact your grade.

Step 1) Manage exam data file

a) Download the “BUS150 Exam 1.xlsx” MS Excel file to your computer DESKTOP. b) Save as <Last Name-First Name> BUS150 Exam 1 (e.g. PEREZ-GERY BUS150 Exam 1).

Step 2) On the saved data file, perform these tasks: TASK 1: In the Documentation worksheet

a) Enter your name and today’s date (MM/DD/YYYY) in the appropriate boxes. Format as needed and align right. b) All text should show, without changing column width or font size. c) Change fill color of cells A3:A5 to company blue color and white font. d) Add borders to all cells in range A3:B5.

TASK 2: In the A-Markets worksheet

a) Per region, use all yearly data to insert sparklines (Line), with markers in orange, in the respective highlighted cell. TASK 3: In the A-Products worksheet

a) Complete the Statistic Summary table. HINT: Use functions and range reference b) Format the Product List so for any price below $10 that cell color changes to Red. c) Format the Product List to highlight any duplicate Product ID. d) Populate “Discounted Price” column so it automatically changes if we were to modify the Discount % (F10).

TASK 4: In the A-Order worksheet a) Properly format zip and phone (B13,B14), so existing data displays correctly. Values should fully show in columns. b) Given the Delivery Type and Order Date, populate Deliver Days and Est. Delivery Date cells (B18,B19). c) Complete all gray out cells in Customer Order Details table. Properly format, if needed.

d) Calculate Subtotal and Sales Tax; making a reference to Sakes Tax given.

e) Populate Shipping & Handling charges based on Delivery Type.

f) If the order Subtotal is over or equal to $500, the client gets a discount equal to Shipping charge (free shipping). g) Calculate order TOTAL. h) Format printing to be in one landscaped page, showing only the Order Form (no Product Table); the Footer should display

“ORDER FORM” in the center and, on the right side, the date the order is printed.

TASK 5: In the A-Historical Sales worksheet

a) Create a pie chart that displays the total product sales in 2019 by quarter, title the chart “2019 Quarterly Sales” change to Bold font, only the percentage of each slice should be showing outside the chart. Legend on the right of chart.

b) Move this chart to A-Markets worksheet, A11. c) Create one column per quarter chart comparing the total quarter sales of each year (i.e. 2018 vs 2019 Q1, 2018 vs 2019 Q2,

etc.), title the chart “Quarterly Sales Comparison”. Ensure the axis/legend is correctly listing quarter/year. d) Move this chart to A-Markets worksheet, J11. Color chart area a light gray and plot area in white.

Step 3) Wrap-up a) Ensure cell A1 is selected, document should open to Documentation worksheet. b) Save your document, so file opens to A1 of Documentation worksheet, close it, and then upload your file.

This is a closed-book, closed-note exam. You are NOT allowed to use any other content (e.g. opening any other MS Excel files).