Click
on the Sheet 2 tab at the bottom of the page.
To
set up the page, click File, Page Setup and portrait (if not already selected)
Click
OK
Merge
cells A1 and B1 and centre the information
Type
the title, Trip Expenses in the cells that you just merged (cell
A1) and tap Enter
Bold
and underline this title
Naming
the Worksheet Tabs (page 135)
Rename
Sheet 1 to Budget (if you didn’t already do this in the previous lesson)
Rename
Sheet 2 to Trip
Add
Color to the Worksheet Tabs
Right
click on the Budget Tab and click on tab color.Choose
the color you want and click on it. Follow the same procedure for the Trip
tab (this feature may not be available for earlier versions of Excel)
Set
up the Rows and Columns for Item Description and Amounts
Click
A2and type Expenses – tap
Enter
Click
B2 and type Spring Break Budget –
tap Enter
Centre
align the titles in Cells A2 and B2.Bold
the titles in both of these cells.
Resizing
row height
Resize
the row height to 22 for rows 2 to 15
Format
Cells for Amounts
Format
the cell range B2:B15 to reflect the “currency” style with 2 decimal places
Right
justify the values in the cell range B2:B15
Formatting
Items Column
Left
justify the values in the cell range A2:A15
Adjusting
automatic curser movement ((Page
127)
Click
in Cell A3
Click
Tools and then Click Options
Click
on the Edit Tab
Near
the middle left you will see a box beside “move selection after enter”
If
there is not a check mark in this box, click on it to show the check mark
Click
on the down arrow beside it and click on “down” (if necessary)
Click
OK
Enter
Trip Expenditure Item Names
Click
in Cell A4 and type Airfare (round trip) – tap enter
In
Cell A5, type Hotel – tap enter
In
Cell A6 type Ground Transportation (buses and taxis) - tap enter
In
Cell A7 type Car or Scooter rental– tap enter
In
Cell A8 type Medical Insurance – tap enter
In
Cell A9 type Food – tap enter
In
Cell A10 type Sports (snorkel, scuba, golf, parasailing, etc.)– tap enter
In
Cell A11 type Tours (tour guides, museums, etc)
In
Cell A12 type Entertainment (clubs, dancing, etc) – tap enter
In
Cell A13 type Souvenirs
IN
Cell A14 type Miscellaneous
In
Cell A15 Type TOTAL – tap enter
Adjust
the width of column A so that all text values in this column are completely
visible.
Enter
Amount Items
In
Column B - Enter the amounts listed below:
Cell
B4:399
Cell
B5:140
Cell
B6:87
Cell
B7:122
Cell
B8:35
Cell
B9:220
Cell
B10:140
Cell
B11:0
Cell
B12:200
Cell
B13:50
Cell
B14:76 (Underline the value in this
cell)
Create
Total of Expenses Using AutoSum
(page 157)
Click
in Cell B15 and sum the values of Column B
Put
a double underline for the value in this cell
Create
a Pie Chart
Using
the data that you have just compiled, create a pie chart to show the expenses
and associated values that you have just entered (you do NOT want to include
the TOTAL value in your chart)
Give
your chart an appropriate title
You
may move the Legend if you choose
In
your pie chart, show either the value or the percent amount for each section
of the pie (you don’t need to select any option that will show the labels
because you have the legend which shows the labels for each section)
Place
the chart in the same worksheet (ie. your chart should appear in the Trip
sheet, not a new worksheet)
Move
the Charts to Sheet 3
Rename
Sheet 3 to “Charts” and give it a colour
Copy
the bar chart from the Budget sheet (that you created in the last lesson)
and the pie chart from the Trip sheet that you just created and paste them
into the Charts sheet.If necessary
arrange the charts so that both charts are completely visible and beside
each other or above/below each other.
In
the Chart sheet, you will add a comment regarding how long it will take
you to save for the trip and calculate it.
Add
Comment
Right
Click in a blank area of the toolbar at the top of the Window
Click
on Drawing
This
will bring up the Drawing toolbar at the bottom of your screen (which is
the condensed version of the PowerPoint toolbar)
From
this toolbar click on the square icon to draw a square
As
your cursor moves up into the worksheet, you will see that your cursor
has changed to a cross
This
is telling you that when you click, hold and drag the mouse button you
will be drawing a square
When
you get the size you want, release the mouse (remember, you can resize
the shape at any time by clicking on it)
Right
click on the square and click on Add Text
Final
Numbers – Conclusion about Your Trip
Add
the following text in the box you created (see example below)
Note:X
= the number of months that it will take to save for the trip (you will
change this value later to the actual # of months once you’ve calculated
it in the steps below).
Play
with the colours, weight and line style of the box.
Computing
the Figures (dividing formula)
To
compute this figure (how many months to save for the trip)
Continue
in the Chart sheet and click in cell L22 (this is where the answer will
appear)
Enter
a formula that will divide (divide symbol is the / on the number pad) the
TOTAL Expenses amount shown in the Trip sheet by the Savings amount in
the Budget sheet.
Your
answer appears in Cell L22
Take
this number, round it up and put the rounded number in the text box you
created earlier (replace the “X” that entered earlier)
Congratulations!
You just finished budget lesson 1 and 2.
Before
saving your work, Spell check each worksheet (Tap F7 or Click Tools,Spelling)
Save
your file as Budget Lesson 1 & 2
Submit
your Budget Lesson 1 & 2 file to the Digital Dropbox on Blackboard.
Addenda
You
found out today that your Dad will chip in $200 for your Airfare, you have
decided that there is a tour that you really want to take that costs $60,
and you now have a roommate to share Hotel costs, which means your Hotel
costs are cut in half.
Go
to your Trip worksheet and adjust these amounts accordingly beside the
appropriate items and then Click on your Charts tab to see how the number
of months has changed for you to save up for the trip.
Change
the number in your text box to show the number of months you have to save
now that your figures have changed.
Learning
Outcomes for Project 2 Part 2:
How
to name worksheet tabs (page 135)
How
to add colour to the tabs (page 135)
How
to create a pie chart (page 163)
How
to choose the location of the chart (page 166)
How
to copy and paste charts (page 116)
How to turn on the Drawing toolbar (pages 10 and 11)