computer

Module 7: Assignment:   Data Sheet - Weekly Online Sales

1. Download the DataSheet (Word document).

2. Copy the information and insert it into an Excel document.

3. Use the correct formulas and functions to carry out the following operations:

·

· Calculate the subtotal of each individual sale (Total column)

· Calculate the grand total of all sales (Grand Total)

· Create a chart showing the total sales (Profit) distributed by item category

· Create a chart showing the distribution of items sold

4. After doing the calculations, answer the following questions:

·

· How much is the grand total of sales?

· Which item was sold the most?

· Which item generated the most profit?

IMPORTANT: Use Excel to perform the assignment.

(See document  A7-2.DataSheet  Download A7-2.DataSheet   and  Instructor Workbook Download Instructor Workbook

 

Be sure to review the  academic expectations  for your submission.

Submission Instructions:

· Submit your assignment by 11:59 p.m. ET on Saturday.

 

Module 7: Spreadsheets and Databases

I. What is an electronic spreadsheet? An electronic spreadsheet is an interactive document for organizing, analyzing, and storing data in table form. A spreadsheet is a document that stores data in a grid of horizontal rows and vertical columns. Rows are typically marked with numbers (1, 2, 3, etc.), while columns are marked with letters (A, B, C, etc.). Individual row/column locations, such as C3 or B12, are called cells. Each cell can store a single instance of data. By entering data into a spreadsheet, information can be stored in a more structured way than using plain text. The row/column structure also allows you to analyze your data using formulas and calculations.

A spreadsheet program operates on the data entered the cells of a table. Each cell can contain numeric or text data or the results of formulas that automatically calculate and display a value based on the content of other cells.

The most commonly used spreadsheet application is Microsoft Excel, but there are other spreadsheet programs:

• Apple Numbers • Google Sheets • Smartsheet • LibreOffice • WPS Spreadsheets

II. Database vs. Spreadsheet

In a spreadsheet, data can be stored in a cell, while in a database, data is stored in a record of a table, meaning you must count the records in a table to express how long the data table is, not the number of the cells. And that is it – you cannot pick a font color or size. All you care about is the stored information; you don’t care about formatting. Our main goal is to save the numbers.

Another substantial difference is that, in a spreadsheet, different cells can contain calculations, such as functions and formulas. If you want to combine two integers, the result will be stored in another cell. In a database, all calculations and operations are based on the existing data and are done after its retrieval. There is a specific feature, called “views,” similar to the tables, in which you can do a calculation. These objects also contain columns that can be normal columns like the ones in the tables, or could have a particular type of calculation. There is no way you can mistake a data record with a calculation.

Data integrity is a strong advantage when working with databases. It is unlikely someone will mistake a data value for an outcome of a calculation, especially in large data sets. The database features mentioned so far improve data integrity – you can’t store different types of data in the same field.

Naturally, you might think a spreadsheet can contain multiple worksheets, so one can create tables in the worksheets and then use the worksheets to create relations between the tables. Why bother using relational databases? Well, in a spreadsheet, such relations will be logically limited. Instead of setting up spreadsheets or worksheets, one can set up relations between the tables, which will boost the performance of operations, increasing the speed with which you could manipulate your dataset.

Databases provide a stable structure, controlling access permissions and user restrictions. One person can make a change that is visible to everybody instantly. This feature increases efficiency and data consistency when using databases.

Considering data integrity and data consistency, using databases eliminates duplicate information, which is another way to save space and increase efficiency. Look at the “Customers” table. You know a certain first, and the last name corresponds to a unique email address. So, if you know John McKinley has changed his email, and you are using a spreadsheet flooded with data, you may change the email address once and accidentally miss updating the same address in another record. This may lead to inadvertent mistakes. They can be avoided when using a relational database - an accredited user only needs to access the “Customers” table and change John McKinley’s email address there. Just once. This operation will save time, but it will also anticipate inconsistencies.

References

Cable, S., Freund, S. M., Monk, E., Sebok, S. L., & Vermaat, M.E. (2020).  Shelly Cashman Series Microsoft Office 365 & Office 2019 Introductory. Cengage Learning.

Vermaat, M. E., Sebok, S. L., Freund, S. M., Campbell, J. T., & Frydenberg, M. (2017).  Discovering Computers 2018: Digital Technology, Data, and Devices (1.a). Cengage Learning.