BUS299: Practice Answer following questions 1-11 on "Sales" worksheet in appropriate columns. 1. Compute the sales tax on sales (define the cell of the sales tax rate on the sheet as "tax_rate") 2. Compute the total sales including the sales tax for each order. 3. Calculate the shipping date (= 2 months after the order date) 4. Combine the first name, last name of the contacts into: FirstName LastName (Note: a SPACE is needed between first name and last name) 5. What is the last word of the customer name? 6. What is the first word of the customer name?
7. Calculate the discounted total sales. The discount given on each order based on the following practice: If the customer is from USA and the total sales are greater than $5000 then the customer gets a discount of 8.5% on total sales, if the customer is from USA and the sales is between $3000 and $5000 (both inclusive), the customer gets a discount of 4% on total sales. Otherwise, the customers get no discount at all. 8. Find out the day the order was placed for each order. For example, for Order Number 10107, day would be 24. 9. Calculate how many years has it been the order was placed for each order? 10.Calculate all the descriptive statistics in cells F22 through F30 based on information provided on the worksheet. 11.Use conditional formatting to show international customers (i.e., from countries other than USA) and USA customers. Use colors of your choice. 12.In the worksheet named "Sales by Country": a. Use SUMIF and external reference to summarize sales for each country b. Format the sales as currency c. Use conditional formatting to highlight the highest sales. Use colors of your choice. d. Develop a pie chart that compares sales percentage by each country and use data explosion for Norway. e. Develop a column chart that compares sales amount by each country.
Page 1 > of 2 ZOOM + BUS 299 Practice Sample Exam 1 To help you prepare for the Exam 1. Complete this sample exam and bring your solution file with any questions to class. It is Open Book/ open notes/ open Excel files. No use of cell phone or email. Please note that actual exam may cover material that may not be on this particular sample exam. This is simply one tool to give you more practice. You need to make sure that you have completed all the chapter/unit exercises and other material covered in class. Best Practice Tips: Simulate the actual exam scenario by timing yourself, and make sure you will not be interrupted for that time. And then repeat this simulation as many times as you can. Repetition is the key for success! Save a copy of the exam file in case your database get corrupted for some reason, you can then still get to the original database. Save a copy of your working solution file periodically in another drive, in case your computer "dies" in the middle. It happens more often than you think! Save frequently. File: Download SampleExaml.xls and rename it to your_last_name_SampleExaml.xls. You are provided a database of sales data with customer information on the SALES worksheet. A11 column labels tell you what the data is about. For example, Order Number is order number on each invoice for each order, etc. Answer following questions 1-11 on "Sales" worksheet in appropriate columns. 1. Compute the sales tax on sales (define the cell of the sales tax rate on the sheet as "tax rate" ) 2. Compute the total sales including the sales tax for each order. 3. Calculate the shipping date (= 2 months after the order date) 4. Combine the first name, last name of the contacts into: FirstName LastName (Note: a SPACE is needed between first name and last name) 5. What is the last word of the customer name? 6. What is the first word of the customer name? Page of 2 ZOOM + 7. Calculate the discounted total sales. The discount given on each order based on the following practice: If the customer is from USA and the total sales are greater than $5000 then the customer gets a discount of 8.5% on total sales, if the customer is from USA and the sales is between $3000 and $5000 (both inclusive), the customer gets a discount of 4% on total sales. Otherwise, the customers get no discount at all. 8. Find out the day the order was placed for each order. For example, for Order Number 10107, day would be 24. 9. Calculate how many years has it been the order was placed for each order? 10. Calculate all the descriptive statistics in cells F22 through F30 based on information provided on the worksheet. 11. Use conditional formatting to show international customers (i.e., from countries other than USA) and USA customers. Use colors of your choice. 12. In the worksheet named "Sales by Country" : a. Use SUMIF and external reference to summarize sales for each country b. Format the sales as currency c. Use conditional formatting to highlight the highest sales. Use colors of your choice. d. Develop a pie chart that compares sales percentage by each country and use data explosion for Norway. e. Develop a column chart that compares sales amount by each country. Page of 2 ZOOM + a Sales Tax Sales Database 9.25% % Q1 Q2 Q3 04 Q5 Contact's Full Name LAST word of (FirstName CUSTOMER Shipping Date LastName) NAME Q6 Q7 DISCOUNTED FIRST word of Total Sale (in CUSTOMER NAME SS) Q8 09 YEARS DAY of since the Order Date order SALES TAX Total Sales San Francisco CUSTOMER ORDER QUANTITY CUSTOMER CUSTOMER CONTACT CUSTOMER NUMBER ORDERED SALES ORDER DATE CUSTOMER NAME CUSTOMER CITY STATE COUNTRY LASTNAME FIRSTNAME 10107 30$ 2,871.00 2/24/2003 0:00 Toys Land NYC NY USA Yu Kwai 10121 341 $ 4.765,90 5/7/2013 0:00 Reims Collectables Reims France Henriot Paul 10134 41 $ 3,884.34 7/1/2003 0:00 Lyon Souveniers Paris France Da Cunha Daniel 10145 45$ 3,746.70 8/25/2020 0:00 Toys4Grown Ups Inc. Pasadena |CA USA Young Julie 14 10159 49 $ 5,205.27 10/10/2018 0:00 Corporate Giftideas San Francisco CA USA Brown Julie 404 10168 36$ 3,479.76 10/28/2003 0:00 Technics Stores Burlingame CA USA Hirano Juri 1941 19/2002 ledalue ang 10180 m. IM 29 $ 2,497.77 11/11/2003 0:00 Daedalus Designs Lille France Rance Martine 20 1407010 noludku alte M 10188 11/18/2019 0:00 Herkku Gifts 48$ 5,512.32 Bergen Norway Oeztan Veysel 10201 22 $ 2,168.54 /$ 12/1/2003 0:00 Mini Wheels |CA USA Murphy Julie 10211 41 $ 4,708.44 1/15/2021 0:00 AutoCanal Petit Paris France Perrier Dominique 10223 37 $ 3,965.66 12/20/2014 0:00 Australian Collectors Melbourne Victoria Australia Ferguson Peter 10237 23 $ 2,333.12 4/5/2016 0:00 Vitachrome Inc. NYC NY USA Frick Michael 10251 28 $ 3,188.64 5/18/2014 0:00 Tekni Collectables Newark NJ USA Brown William 10263 34 $ 3,676.76 6/28/2014 0:00 Gift Depot Bridgewater CT USA King Julie 10275 45$ 4,177.35 7/23/2019 0:00 La Rochelle Gifts Nantes France Janine 10285 36 $ 4,099.68 8/27/2010 0:00 Marta's Replicas Cambridge MA USA Hernandez Marta Q10 Average order quantity Average of California Sales L Number of customers from NYC = Highest Total Sales = Minimum Total Sales Order = Total number of orders = Number orders with more than 30 order quantity = 3rd quartile total sales = What does it mean? 40th percentile total sales order = What does it mean? Use conditional formatting to show international customers Q11 (i.e., from countries other than the USA). Use colors of your choice. Labrune Page 2 > of 2 - ZOOM + CA Victoria 10188 10201 102111 10223 10237 10251 10263 10275 10285 NY NJ CT Norway USA France Australia USA USA USA France JUSA Oeztan Veysel Murphy Julie Perrier Dominique Ferguson Peter Frick Michael Brown William King Julie Labrune Janine Hernandez Marta MA 481 $ 5,512.32 11/18/2019 000 Herkku Giits Bergen 22 $ 2,168,54 12/1/2003 0:00 Mini Wheels San Francisco 41 $ 4,708.44 1/15/2021 0:00 AutoCanal Petit Paris 371 $ 3,965.66 12/20/2014 0:00 Australian Collectors Melbourne 23 S 2,333.12 4/5/2016 0:00 Vitachrome Inc. NYC 28 $ 3,188,64 5/18/2014 0:00 Tekni Collectables Newark 34$ 3,676.76 6/28/2014 0:00 Gift Depot Bridgewater 45 S 4,177.35 7/23/2019 0:00 La Rochelle Gifts Nantes 36 $ 4,099.68 8/27/2010 0:00 Marta's Replicas Cambridge 010 Average order quantity - Average of California Sales Number of customers from NYC = Highest Total Sales - Minimum Total Sales Order = Total number of orders = Number orders with more than 30 order quantity = 3rd quartile total sales = 40th percentile total sales order = Use conditional formatting to show international customers 011 (i.e., from countries other than the USA). Use colors of your choice. What does it mean? What does it mean? Sales Q12 Country Australia France Norway USA Page 1 > of 2 ZOOM + BUS 299 Practice Sample Exam 1 To help you prepare for the Exam 1. Complete this sample exam and bring your solution file with any questions to class. It is Open Book/ open notes/ open Excel files. No use of cell phone or email. Please note that actual exam may cover material that may not be on this particular sample exam. This is simply one tool to give you more practice. You need to make sure that you have completed all the chapter/unit exercises and other material covered in class. Best Practice Tips: Simulate the actual exam scenario by timing yourself, and make sure you will not be interrupted for that time. And then repeat this simulation as many times as you can. Repetition is the key for success! Save a copy of the exam file in case your database get corrupted for some reason, you can then still get to the original database. Save a copy of your working solution file periodically in another drive, in case your computer "dies" in the middle. It happens more often than you think! Save frequently. File: Download SampleExaml.xls and rename it to your_last_name_SampleExaml.xls. You are provided a database of sales data with customer information on the SALES worksheet. A11 column labels tell you what the data is about. For example, Order Number is order number on each invoice for each order, etc. Answer following questions 1-11 on "Sales" worksheet in appropriate columns. 1. Compute the sales tax on sales (define the cell of the sales tax rate on the sheet as "tax rate" ) 2. Compute the total sales including the sales tax for each order. 3. Calculate the shipping date (= 2 months after the order date) 4. Combine the first name, last name of the contacts into: FirstName LastName (Note: a SPACE is needed between first name and last name) 5. What is the last word of the customer name? 6. What is the first word of the customer name? Page of 2 ZOOM + 7. Calculate the discounted total sales. The discount given on each order based on the following practice: If the customer is from USA and the total sales are greater than $5000 then the customer gets a discount of 8.5% on total sales, if the customer is from USA and the sales is between $3000 and $5000 (both inclusive), the customer gets a discount of 4% on total sales. Otherwise, the customers get no discount at all. 8. Find out the day the order was placed for each order. For example, for Order Number 10107, day would be 24. 9. Calculate how many years has it been the order was placed for each order? 10. Calculate all the descriptive statistics in cells F22 through F30 based on information provided on the worksheet. 11. Use conditional formatting to show international customers (i.e., from countries other than USA) and USA customers. Use colors of your choice. 12. In the worksheet named "Sales by Country" : a. Use SUMIF and external reference to summarize sales for each country b. Format the sales as currency c. Use conditional formatting to highlight the highest sales. Use colors of your choice. d. Develop a pie chart that compares sales percentage by each country and use data explosion for Norway. e. Develop a column chart that compares sales amount by each country. Page of 2 ZOOM + a Sales Tax Sales Database 9.25% % Q1 Q2 Q3 04 Q5 Contact's Full Name LAST word of (FirstName CUSTOMER Shipping Date LastName) NAME Q6 Q7 DISCOUNTED FIRST word of Total Sale (in CUSTOMER NAME SS) Q8 09 YEARS DAY of since the Order Date order SALES TAX Total Sales San Francisco CUSTOMER ORDER QUANTITY CUSTOMER CUSTOMER CONTACT CUSTOMER NUMBER ORDERED SALES ORDER DATE CUSTOMER NAME CUSTOMER CITY STATE COUNTRY LASTNAME FIRSTNAME 10107 30$ 2,871.00 2/24/2003 0:00 Toys Land NYC NY USA Yu Kwai 10121 341 $ 4.765,90 5/7/2013 0:00 Reims Collectables Reims France Henriot Paul 10134 41 $ 3,884.34 7/1/2003 0:00 Lyon Souveniers Paris France Da Cunha Daniel 10145 45$ 3,746.70 8/25/2020 0:00 Toys4Grown Ups Inc. Pasadena |CA USA Young Julie 14 10159 49 $ 5,205.27 10/10/2018 0:00 Corporate Giftideas San Francisco CA USA Brown Julie 404 10168 36$ 3,479.76 10/28/2003 0:00 Technics Stores Burlingame CA USA Hirano Juri 1941 19/2002 ledalue ang 10180 m. IM 29 $ 2,497.77 11/11/2003 0:00 Daedalus Designs Lille France Rance Martine 20 1407010 noludku alte M 10188 11/18/2019 0:00 Herkku Gifts 48$ 5,512.32 Bergen Norway Oeztan Veysel 10201 22 $ 2,168.54 /$ 12/1/2003 0:00 Mini Wheels |CA USA Murphy Julie 10211 41 $ 4,708.44 1/15/2021 0:00 AutoCanal Petit Paris France Perrier Dominique 10223 37 $ 3,965.66 12/20/2014 0:00 Australian Collectors Melbourne Victoria Australia Ferguson Peter 10237 23 $ 2,333.12 4/5/2016 0:00 Vitachrome Inc. NYC NY USA Frick Michael 10251 28 $ 3,188.64 5/18/2014 0:00 Tekni Collectables Newark NJ USA Brown William 10263 34 $ 3,676.76 6/28/2014 0:00 Gift Depot Bridgewater CT USA King Julie 10275 45$ 4,177.35 7/23/2019 0:00 La Rochelle Gifts Nantes France Janine 10285 36 $ 4,099.68 8/27/2010 0:00 Marta's Replicas Cambridge MA USA Hernandez Marta Q10 Average order quantity Average of California Sales L Number of customers from NYC = Highest Total Sales = Minimum Total Sales Order = Total number of orders = Number orders with more than 30 order quantity = 3rd quartile total sales = What does it mean? 40th percentile total sales order = What does it mean? Use conditional formatting to show international customers Q11 (i.e., from countries other than the USA). Use colors of your choice. Labrune Page 2 > of 2 - ZOOM + CA Victoria 10188 10201 102111 10223 10237 10251 10263 10275 10285 NY NJ CT Norway USA France Australia USA USA USA France JUSA Oeztan Veysel Murphy Julie Perrier Dominique Ferguson Peter Frick Michael Brown William King Julie Labrune Janine Hernandez Marta MA 481 $ 5,512.32 11/18/2019 000 Herkku Giits Bergen 22 $ 2,168,54 12/1/2003 0:00 Mini Wheels San Francisco 41 $ 4,708.44 1/15/2021 0:00 AutoCanal Petit Paris 371 $ 3,965.66 12/20/2014 0:00 Australian Collectors Melbourne 23 S 2,333.12 4/5/2016 0:00 Vitachrome Inc. NYC 28 $ 3,188,64 5/18/2014 0:00 Tekni Collectables Newark 34$ 3,676.76 6/28/2014 0:00 Gift Depot Bridgewater 45 S 4,177.35 7/23/2019 0:00 La Rochelle Gifts Nantes 36 $ 4,099.68 8/27/2010 0:00 Marta's Replicas Cambridge 010 Average order quantity - Average of California Sales Number of customers from NYC = Highest Total Sales - Minimum Total Sales Order = Total number of orders = Number orders with more than 30 order quantity = 3rd quartile total sales = 40th percentile total sales order = Use conditional formatting to show international customers 011 (i.e., from countries other than the USA). Use colors of your choice. What does it mean? What does it mean? Sales Q12 Country Australia France Norway USA
