Computer sciences and Information technology

Computer sciences and Information technology
Project description

You will create an Excel spreadsheet, which will introduce some additional Excel skills and features. 
 
Screenshots of the completed worksheets are at the end of this document. 
 
Your primary tasks in this assignment are to: 
1. complete an Excel spreadsheet exactly as described below; 
2. upload it to the Moodle assignment Drop Box before the deadline 
(emailed assignments or those submitted by any other means are not accepted); and 
3. complete the Moodle self-assessment indicating completion (or not) of each requirement in the assignment. 
 
The self-assessment will be available a week before the due date. The self-assessment is an essential component of the assignment. It is the step that records your grade on the assignment. I will spot check about 25% of the submissions against the self-assessment. If your self-assessment doesn’t match the actual work you turned in, it is cheating and it is an automatic 0 for the assignment. If you get caught cheating twice, it is a zero for the course. It is okay to go back and update your assignment to match the requirement on the self-assessment. However, the submitted version must contain anything you claim credit for in the self-assessment or it is cheating. 
 
You may work on this assignment together. However, you must turn in your own file. If you turn in a copy of another student’s file, both students receive a 0 for the assignment. 
 
Reading 
Microsoft® Office 2010 Bible by Walkenbach et al. 
Part III, chapters 12 – 26 on using Excel 
Make sure to know how to use Absolute, Relative and Mixed References, p.405 – 408 
 
Note on Readings for the Assignment: Test questions on Microsoft Excel will focus primarily on what was included in the assignments. That said, questions from the reading are fair game—they may be included in test questions. If you are familiar with Microsoft Excel, you can decide how much effort you want to put into doing the reading for this assignment. But be aware, information from the reading may appear on a quiz, a midterm or the final. 
Assignment 
 
1. Name your Excel file: BBUS1370_Assign4_LastnameFirstname.xlsx 
 
For example, if I were turning in the assignment I would name my file 
BBUS1370_Assign4_SwingleJim.xlsx 
 
Files must follow this naming convention to receive credit. If your filename doesn’t follow this format it is the same as not turning in any assignment. 
 
2. When you complete your assignment submit it before the deadline to the Drop Box set up on Moodle and take the Self-Assessment Quiz. 
 
To receive credit for this assignment it must be submitted before the deadline, it must be submitted via Moodle Drop Box and the Self-Assessment must be completed. Submissions will not be accepted via email or any other method. 
 
Screenshots of the completed worksheets are at the end of this document. 
 
3. Rename the worksheets as follows: 
Sheet1 becomes Sales 
Sheet2 becomes Bar Chart 
Sheet3 becomes Postal Codes 
 
4. You will import starting data for the Sales worksheet, columns A through D, and the Postal Codes worksheet. Download the Zip file (BBUS1370_Assign4_DataFiles.zip) from Moodle and unzip it. 
 
First import the data in the file Assign4_SalesData.csv into the Sales worksheet. 
• Go to Data ribbon 
• Where it says “Get External Data,” select “From Text” 
• Select “Delimited” and then click “Next” 
• Under Delimiters select “Comma” (and make sure any other delimiters are de-selected 
• Click Finished 
• Import Data into “Existing Worksheet,” cell A1 
• Click on Properties, which will bring up a dialog box 
• Uncheck the box labeled “Save query definition” and click OK 
• Click OK on the Import Data dialog box 
 
Repeat above steps to import Assign4_PostalCodes.csv into your Postal Codes worksheet. 
(see Sales and Postal Codes screenshots) 
 
 
Screenshots of the completed worksheets are at the end of this document. 
 
5. On the Sales worksheet, do the following: 
• Merge Cell F1 through H1 and enter “Revenue by Province” in the new merged cell. 
• Put “Abbrev.” in cell F2 
• List the 13 province abbreviations below it in order from West to East (see Sales screenshot for correct ordering of province abbreviations) 
• Put “Province” in cell G2 
• Put “Revenue” in cell H2 
 
6. On the Sales worksheet, in cells G3 through G15, use the VLOOKUP function and the data on your Postal Codes worksheet to populate the cells with the full names of the provinces. (see Sales screenshot) 
 
7. On the Sales worksheet, in cells H3 through H15, use the SUMIF function to calculate the total annual sales for each province. (see Sales screenshot) 
 
8. On the Sales worksheet, do the following: 
• Merge Cells G17 and H17 and enter “Descriptive Statistics for All Stores” in the new merged cell 
• Merge cells G18 and H18 and put “(in 1000s)” in the new merged cell 
• In cell G19 put “Total:” 
• In cell G20 put “Mean:” 
• In cell G21 put “Median:” 
• In cell G22 put “Min:” 
• In cell G23 put “Max:” 
• In cell G24 put “Range:” 
• In cell G25 put “Standard Deviation:” 
 
9. On the Sales worksheet, use the following formulas: 
• In cell H19 use the correct function to calculate the total annual sales across all stores 
• In cell H20 use the correct function to calculate the mean annual sales per store 
• In cell H21 use the correct function to calculate the median annual sales per store 
• In cell H22 use the correct function to identify the lowest annual sales a store had 
• In cell H23 use the correct function to identify the highest annual sales a store had 
• In cell H24 use the correct function(s)/formula to calculate the range—that is, the difference between the highest annual sales and the lowest annual sales 
• In cell G26 use the correct function to calculate the standard deviation 
 
10. On the Bar Chart worksheet, insert a Bar Chart (called Column Chart in Excel) that shows the revenue by province. Title the chart “Revenue by Province” and for Legend choose “None.” (see Bar Chart screenshot) 
 
 
Screenshots of Completed Worksheets 
 
Sales 
 
 
 
 
 
 
 
Bar Chart 
 
 
 
 
 
 
Postal Codes
PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET AN AMAZING DISCOUNT 🙂