Spreadsheet Make

fanti

Assignment3-SpreadsheetInstructions1.pdf

Home >Computer Science homework help >Spreadsheet Make

Assignment 3: Spreadsheet

Overview

The purpose of this assignment is to learn how to create and use a spreadsheet.

You are the Junior Accountant of the consulting firm Axxon Corporation in the third year of a

four year business cycle. Since your third year is not yet complete, your sales and expenses are

still unknown and have to be estimated. You will use a spreadsheet to prepare your budget.

You must use Microsoft Excel for this assignment. The instructions for this assignment use

Microsoft Excel 2016. Other versions of Microsoft Excel are similar.

Task

A spreadsheet has sheets (i.e. the tab(s) near the bottom of the spreadsheet window that control

which sheet is visible). Each sheet has a table and/or a chart. In this assignment, you will make

two sheets: one for a table of data and one for a column chart.

To do this worksheet, you will need to:

 Change the name of the sheet;

 Adjust column widths and row heights;

 Merge cells;

 Input text and numbers;

 Set text colour;

 Set background colour of cells;

 Set border colour of cells;

 Make text bold;

 Align data horizontally and vertically;

 Wordwrap text within a cell;

 Enter formulas (including cell ranges);

 Format numbers.

Step 1) Save your file as “SS Assignment 3 YourFirstName YourLastName” Replace YourFirstName with your first name and YourLastName with your last/family name.

“Budget” Sheet Requirements

Step 2) Rename the sheet from Sheet1 to “Budget” (without the quotes).

Setting/Renaming the Sheet Name

1. At the bottom of the spreadsheet window you will see the sheet tab with the default name of "Sheet1" (N.B. You may see more than one sheet tab in other earlier versions.)

2. Right-click on the tab/sheet you want to rename and click Rename. 3. Type in its new name. 4. Click on any cell in the sheet.

The completed sheet will be displayed in cells A1 through F21. You will need to use six (6)

columns (i.e. A through F) and twenty-one (21) rows (i.e. 1 through 21) to do the table below.

All text headings in the entire table must have the capitalization as it appears in the table below.

Your sheet must look like this table (subject to requirements given):

Step 3) The title must be centred across columns A-F (i.e. merge cells) in cell A1 (row one (1)).

The subtitles must also be centred across columns A-F (i.e. merge cells) so that the next

two (2) rows must be in cell A2 (row two (2)) and A3 (row three (3)).

Replace YourFirstName with your first name; YourLastName with your last (family)

name; and ### ### ### with your student number.

N.B. You will be adjusting row height later. So don’t worry if all text on the rows do

not fit.

Merging/Unmerging/Multiline Instructions

Merging Cells

1. Click-and-drag the mouse over all cells to be merged. (This highlights all of those cells.) 2. In the Home tab click the Merge and Center button in the Alignment group.

Unmerging Cells

N.B. You can unmerge the cells you merged if you make a mistake in merging.

1. Click the cell to be unmerged. 2. In the Home tab click the down arrow of the Merge and Center button in the

Alignment group.

3. Click on the Unmerge Cells option.

Typing Multi-line Text In One Cell

N.B. These are instructions for when you key in row 4 text

1. Double-click in a cell. (This allows you to type in text in that cell.) 2. Type in a line of text. 3. Instead of pressing the Enter key, press Alt-Enter (i.e. press and hold down the Alt key,

press Enter, and then lift up the Alt key) to type another line of text in the same cell.

Step 4) Change the height of rows 1, 2 and 3 to 30 (40 pixels) in MS Office.

Adjusting Row Height (To Make A Row Taller Or Shorter)

1. Right-click on the row title you want to change (i.e. the column with the 1, 2, 3, …, etc. numeric row labels).

2. Choose Row Height... to set the row height to a numeric value.

N.B. You can also do the following to change the row height: In the row title column (i.e.

the column with the 1, 2, 3, ..., etc. numeric row labels), click-and-drag the line

between two rows to resize the row above the line.

Step 5) Merge the cells in rows 5, 12, 13, 19, and 20 across columns A-F. (N.B. Instead of choosing the Merge & Center icon, click the down arrow and choose Merge Cells so

that when you key in text it is left aligned and not centred.)

Step 6) Change the width of column A to 3 (26 pixels) in MS Office. It is only used to indent the individual cities and expenses.

Adjusting Column Widths (To Make A Column Wider Or Narrower)

1. Right-click on the column title you want to change (i.e. the row with the A, B, C, D, …, etc. column labels).

2. Choose Column Width... to set the column width to a numeric value. 3. Click OK.

N.B. You can also do the following to change the column width: In the column title row

(i.e. the line with the A, B, C, D, ..., etc. column labels), click-and-drag the line

between two columns to resize the column to the line's left or right.

Step 7) Merge the cells in column A with column B in rows 4, 9, 10, 11, 17, 18, and 21 as shown on the table above.

Step 8) Change column B width to 30 (215 pixels) characters in MS Office.

Don’t worry if the white text disappears. It will reappear when we change the

background colour of the cells.)

Changing Text Colours 1. Click-and-drag the mouse over all the text or cells to be changed. 2. In the Home tab click the down arrow of the Font Color icon in the Font group. 3. Choose the appropriate font colour.

Step 10) Choose background colours that are close to the shades in the table above. Rows 1, 2, and 3 are a darker shade of purple. Rows 4, 9, 10, 11, 17 and 18 are a lighter shade of

purple. Rows 5, 13, and 20 are in a grey shade.

Changing Background Colours 1. Click-and-drag the mouse over all the cells to be changed. 2. In the Home tab click the down arrow of the Fill Color icon in the Font group. 3. Choose the appropriate font colour.

Step 11) Key in the rest of the text found on the above worksheet, if you haven’t already done so. (N.B. All text must match the word wrapping in the above sheet. You are not

allowed to add rows or columns to the above table.) Adjust column widths for columns

C, D, E, and F to 25 (180 pixels) in MS Office.

Step 12) All labels must be horizontally aligned according to the above table (i.e. left, centre, or right-aligned).

Aligning Text Horizontally (Left, Center, Right) 1. Click-and-drag the mouse over all the cells to be changed. 2. In the Home tab click the appropriate alignment icon (i.e. Align Text Left, Center,

Align Text Right) in the Alignment group.

Step 13) All text headings in the entire worksheet must be bold (i.e. rows 1, 2, 3, 4, 5, 13, 20 and cells A9, A10, A11, A17, A18, and A21).

Bolding Text

1. Click-and-drag the mouse over all the text or cells to be bolded. 2. In the Home tab click the Bold icon in the Font group.

Step 14) The data in cells A1, A2, and A3 must be vertically centred within their cells as shown in the table above.

Aligning Text Vertically

1. Click-and-drag the mouse over all cells to be vertically centred. 2. Right-click somewhere on the highlighted cells and choose Format Cells... . 3. On the Alignment tab: Set the Vertical text alignment to Center. 4. Click OK.

N.B. You can vertically centre text using the Top Align, Middle Align, and Bottom

Align icons in the Alignment group of the Home tab.

Step 15) Set the cell border colour to a similar shade of yellow/orange as the above table. All text in the above table has all rows and columns indicated. Thus, the light grey lines in the above table

indicate the cell borders are not set (i.e. leave them alone, do not change their cell borders).

(N.B. This applies to: The first two columns of the rows with the individual cities and

expenses.)

Setting Border Colours (To Set Cell Borders)

1. Click-and-drag the mouse over all cells to be set. 2. Right-click on the selected area. 3. Click the Format Cells... menu item. 4. Click on the Border tab in the pop-up dialog box. 5. In the Color area, choose the appropriate border colour. 6. In the Border area, click on the line(s) that need to be set. 7. Click OK when done.

Step 16) The font and size of the entire worksheet is Book Antiqua, 15 point (i.e. A1 through F21).

Changing Font

1. Click-and-drag the mouse over all the text or cells to be changed. 2. In the Home tab click the down arrow of the Font icon in the Font group. 3. Choose the appropriate font.

Changing Font Size

1. Click-and-drag the mouse over all the text or cells to be changed. 2. In the Home tab click the down arrow of the Font Size icon in the Font group. 3. Choose the appropriate font size.

Step 17) All numbers in the table must be right-justified and set to Currency with two (2) decimal places. (N.B. You will be adding numbers in the steps below. At this point,

format the cells so when you add the numbers, they will already be formatted. Format

cells C6 through F11; C14 through F18; and C21 through F21.)

(N.B. Numbers are NOT in bold.)

Formatting Numbers (To Set Cells into Currency/Monetary Values)

1. Click-and-drag the mouse over all cells to be set. 2. Right-click on the selected area. 3. Click the Format Cells... menu item. 4. Click on the Number tab. 5. Select Currency in the Category section. 6. Select -$1,234.10 in the Negative numbers section. 7. Ensure Decimal places is set to 2. 8. Click OK.

Using Formulas

The power of a spreadsheet is in its formulas. You do not need to calculate anything as the

software will do it for you when you provide the correct formula. Within a spreadsheet, a

formula is entered by having a cell's first character be an '=' (equals) symbol followed by the

desired formula. Within a formula you may use cell references, functions, + (addition), -

(subtraction), * (multiplication), / (division), and parentheses (to enforce order-of-operations).

Cell references must be done as follows:

• To refer to a specific cell, simply type in the cell's location with the column first, the row second with no intervening space. For example, to compute five (5) times the value that

is in cell A2, you would write either, "=A2*5", or, "=5*A2" (without the quotes) in a cell.

• To refer to a contiguous range of cells, type in the starting cell location, a colon (i.e. ':'), then the final cell location. For example, suppose you have values in cells B7 to B15 and

you want to compute the minimum, maximum, average, sum, and count of the values in

the range. Since you can only write one formula per cell, you would write the following

formulas in empty cells:

o =MIN(B7:B15) o =MAX(B7:B15) o =AVERAGE(B7:B15) o =SUM(B7:B15) o =COUNT(B7:B15)

All numerical values in the table must be set to Currency with two (2) decimal places (see above

steps). When this is properly done, the spreadsheet will add a '$' (dollar sign) in front of all

amounts and ensure there are two (2) decimal places.

If number signs appear (i.e. ###) in a cell, it means the column width is too narrow and you

must widen the column to show the entire number. For this assignment, if your number is

too big to fit in the cell (i.e. the # signs may appear in the cell depending on the version you

are using) change your number to a smaller number so it fits in the cell.

Completing the Spreadsheet

RANGE(s) INSTRUCTIONS

C6:C8 Make up sales amounts for these fields. (All values must be positive.)

D6:D8 Make up sales amounts for these fields. (All values must be positive.)

E6:E8 These fields are estimated by adding 3% to Year 1 sales for that particular city.

N.B. Remember that adding 3% to a number is the same as multiplying the number

by 1.03. You must use cell references in the formula wherever possible.

F6:F8 These fields are estimated by adding Year 2's and Year 3's sales from the same

row.

N.B. You must use cell references in the formula wherever possible.

C9:F9 Use the MAX function to compute the maximum of each year’s column's values for

the three (3) cities.

C10:F10 Use the MIN function to compute the minimum of each year’s column's values for

the three (3) cities.

C11:F11 Use the SUM function to compute the total of each year’s column's values for the

three (3) cities.

C14:C16 Make up expense amounts for these fields. (All values must be positive.)

D14:D16 Make up expense amounts for these fields. (All values must be positive.)

E14:E16 These fields are estimated by adding 4% of Year 2's expenses to Year 1’s expenses

for that particular expense.

N. B. You must use cell references in the formula wherever possible.

F14:F16 These fields are estimated by adding Year 1’s and Year 2's expenses from the

same row and then adding 2% to that sum

N.B. Remember that adding 2% to a number is the same as multiplying the number

by 1.02. You must use cell references in the formula wherever possible.

C17:F17 Use the AVERAGE function to compute the average of each year’s column's values

for the three (3) expenses.

C18:F18 Use the SUM function to compute the total expenses for each year’ column’s values

for the three (3) expenses.

C21:F21 Compute the Net Income for each year (i.e. Total Sales minus Total Expenses for

each year’s column’s values).

N.B. The Net Income for Year 1 and 4 must be positive.

Although most numerical values must be different, this is a sample screen capture of the

resulting Budget sheet in MS Office:

“Sales” Worksheet Requirements

Step 18) You will be creating a new sheet in the same spreadsheet file next to your Budget sheet called "Sales". This sheet will only contain a column chart (graph) showing the four

years of the sales amounts.

Adding a Column Chart

1. To create your chart, select (i.e. click-and-drag the mouse) the cells from Burlington to Oshawa Year 4 on the Budget sheet (i.e. cells B6 through F8).

2. On the Insert tab click Insert Column or Bar Chart of the Charts group. 3. Click the Clustered Column chart in the 2-D Column section of the drop-down

menu that appears.

4. This will create a chart with the X-axis labels 1, 2, 3, and 4. If you right-click one of those X-axis labels and choose Select Data, a pop-up will appear. Simply click on

Switch Row/Column to swap the rows/columns. This will cause the X-axis to

become Burlington, Niagara, and Oshawa. Click OK.

5. Ensure the chart is selected (if it is, the box around the chart is highlighted). 6. In the Chart Tools tab, you will see two subtabs: Design and Format.

a. Click the Design tab. b. Double click “Chart Title” in the chart until the cursor appears in the chart title

area. Delete the text “Chart Title” and type in the new title (i.e. Axxon

Corporation Sales).

c. In the Design tab, click the top left icon called Add Chart Element. Click Axis Titles and then click Primary Horizontal. Click in the Axis Title area of the

column chart and type in the title that appears in the chart (i.e. Sales).

d. In the Design tab, click the top left icon called Add Chart Element. Click Axis Titles and then click Primary Vertical. Click in the Axis Title area of the column

chart and type in the title that appears in the chart (i.e. Amount ($CAD)).

e. Make sure the legend is at the bottom of the chart, if it’s not already there. N.B. This depends on the version you are using.

7. Now you will need to update your legend as follows: a. Right-click on the legend. b. Click Select Data…, a Select Data Source dialog box appears. c. In the Legend Entries (Series) area, there are four fields: Series1, Series2,

Series3 and Series4. You will need to repeat the following steps for each field:

i. Select the Series# field one at a time (i.e. Series1, Series2, etc.). ii. Click on the Edit button.

iii. The cursor should be in the Series name field box, click cell C4 in the Budget sheet. The Edit Series box should now say: =Budget!$C$4. Click

OK in the Edit Series dialogue box. Series1 in the legend now says Year 1

(Actual). Repeat step c for the rest of the series (i.e. Series2, Series3 and

Series4).

8. Click the OK button. 9. Right-click on any white space in the column chart. 10. Click Move Chart on the pop-up menu that appears. 11. Click the New sheet radio button and key in Sales in place of Chart1 12. Click OK. The chart will now be on its own sheet called “Sales”.

Although your spreadsheet must use different numerical values, this is an example of what the

resulting chart will look like on the Sales worksheet:

MS Office 2016: