LAB 3 - SPREADSHEETSR

Java Python LAB 3 - SPREADSHEETS

In this lab, you will learn to:

● Work with the MS Excel

●   Create a Spreadsheet

Creating a Spreadsheet

1.  Create a blank Microsoft Excel spreadsheet

2.  In cell A2, enter “<Last Name> Mobile Concepts”

a.  Replace  with your last name

3.  In cell A3, enter “Yearly Sales”

4.  In cells B4, C4, D4 and E4, enter the product titles:

a.  Alarm

b. Audio

c.  Light

d.  Ultimate

5.  In cells A5, A6, A7, and A8, enter the regions (states):

a.  California

b.  Nevada

c.  Oregon

d.  New York

6.  Fill in the sales figures by copying (or cut and pasting) from the following table:

	Alarm	Audio	Light	Ultimate
California	860,358.71	431,758.35	375,708.22	786,253.39
Nevada	345,024.13	863,814.87	500,255.40	272,220.20
Oregon	396,157.67	326,159.07	247,826.28	511,277.11
New York	395,428.36	804,908.29	383,514.73	342,965.38

7.  Add the title “Total” in cell F4, and create formulas using the SUM function to determine the total revenue for each state.

8.  Add the title “Total” in cell A9, and create formulas using the SUM function to determine total revenue for each of the four packages, as well as a grand total for the entire company.

< LAB 3 - SPREADSHEETSR p > 9.  Format the Title in cell A2 and subtitle in cell A3 as follows:

● Merge cells A2:F2 and A3:F3 and center the result

● Apply the Title cell style.

● Format as 18-point Arial Black.

●   Format with a font color of your choosing. 10.Type your name in cell A11

11. In cell A12, type the text: =cell(“filename”)

● Don’t copy and paste the above line. There are incompatible characters when you copy from formatted documents that will break your code.

● Copy the text exactly as above. Don’t ‘replace’ the filename.

● Until you save your file, this may show an error or invalid result

12. Format the product names in B4:F4 with a style. of your choice and center the text in the cells.

13. Make the following changes to the data:

● Format cells B5:F5 and B9:F9 with the accounting number format.

● Format cells B6:F8 with the Comma style. format.

● Format the range A9:F9 with the Total cell style.

i.   If necessary, resize all columns to fit the data. 14.Change the name of the sheet1 tab to “Yearly Sales”

15. Save the Spreadsheet using the file name, “Lab 3-.xlsx.

● Replace  with your last name

Submission

Required file naming:

Lab3-lastname.xlsx

Submit this file on Brightspace as an excel spreadsheet. Do NOT submit as a pdf!

Note: It is a good idea to submit early (you are allowed extra submits). It seems the

network goes down right about the time of the deadline. The network going down is not an excuse for not submitting your lab on-time.

All labs must be submitted through Brightspace. If a lab is not submitted properly, it will not be graded. Emailing your lab to the professor does NOT count has properly submitting your lab.

NOTE: You may submit as many times as you like until the deadline. We will only review your final submission. If you submit during the late period, your assignment is late and will lose 10% as a penalty.

Guidelines and Policies

This is an individual lab assignment. You must do most of the work on your own. It is permissible to consult with classmates to ask general questions about the assignment, to help discover and fix specific bugs, and to talk about high level approaches in general terms. It is not permissible to give or receive answers or solution details from fellow students.

You may research online for additional resources; however, you may not use material that was written specifically to solve the problem you have been given, and you may not have anyone else help you write the material or solve the problem. You may use resources found online, providing that they are appropriately and clearly cited, within your submission.

By submitting this assignment, you agree that you have followed the above guidelines regarding collaboration and research.

Grading Rubric

TOTAL: 20 points

● Part A: 18 points

○   (2 pt) Cell A2: contains “<Last Name> Mobile Concepts” and cell A3 contains “Yearly Sales”

○ (2 pt) Cells B4, C4, D4 and E4, contain the product titles

○ (2 pt) Cells A5, A6, A7, and A8 contain states

○ (1 pt) Sales Figures entered into the correct location

○ (1 pt) “Total” in cell F4 and A9

○   (2 pt) Formulas use the SUM function to determine the total revenue for each state

○   (2 pt) Formulas use the SUM function to determine total revenue for each of the four packages

○ (2 pt) Calculates grand total for the entire company