Microsoft Office Tutorials and References
In Depth Information
Tutorial 2
Tutorial 2
Working with Formulas and Functions
Developing a Family Budget

Case
Tyler Family Budget
As a newly married couple, Amanda and Joseph Tyler are trying to balance career, school, and family life.
Amanda works full time as a legal assistant, and Joseph is in a graduate program at a nearby university. He
recently was hired as a teaching assistant. In the summer, he is able to take on other jobs that bring additional
income to the family. The couple also just moved into a new apartment. Although Joseph’s and Amanda’s salaries
for the past year were greater than the years before, the couple seemed to have less cash on hand. This financial
shortage has prompted them to take a closer look at their finances and figure out how to best manage them.
Because Amanda has agreed to take the lead role in the management of the family finances, she has set up an
Excel workbook. Amanda has entered their salary amounts, which are their only income, and she has identified
and entered several expenses that the family pays on a monthly basis, such as the rent and Joseph’s tuition. She
wants to calculate how much money they bring in and how much money they spend. She also wants to figure out
their average monthly expenses and identify their greatest financial burden.
Amanda has asked for your help in completing the workbook. She wants you to insert formulas to perform the
calculations she needs to get a better overall picture of the family’s finances, which, in turn, should help the cou
ple manage their money more effectively. Because the values entered cover a 12month span, you will be able to
copy and paste the formulas from one month to another and fill in series of data, such as the months of the year,
rather than retyping formulas or entering each month individually. Finally, Amanda also wants the current date in
her workbook, which you can enter using one of Excel’s Date functions.

Objectives

SAM Training Tasks
• AVERAGE function
• Change zoom settings
• Copy cells
• Create formulas using
the DATE function
• Create formulas using
the MAX function
• Create formulas using
the MIN function
• Create formulas using
the NOW function
• Create formulas using
the SUM function
• Create formulas using
the TODAY function
Session
Session 2.1
• Learn about the syntax
of an Excel function
• Use the SUM, AVERAGE,
and MAX functions
• Copy and paste formulas
• Work with relative,
absolute, and mixed
references
• Change the
magnification of the
workbook window
• Insert a function using
the Insert Function
dialog box
• Use Auto Fill to insert
formulas and complete
a series
• Insert the current date
using a Date function
• Include a formula
within a database
or list
• Use absolute
references
• Use relative references
• Use the fill handle to
copy a cell
• Use the fill handle to
copy multiple formulas
to adjacent cells at
one time
• Use the fill handle to
create a series
• Use the ROUND
function
Session 2.2
(optional)
• Work with Financial
functions
• Work with Logical
functions
• Create formulas using
the IF function
• Create formulas using
the PMT function
• Create formulas using
the PV function
• Use logical functions
(FALSE)
• Use logical functions
(TRUE)
For a complete list of the Data Files needed for this tutorial, see page EX 2.
Student Data Files