“Learning is not attained by chance, it must be sought
for with ardor and attended to with diligence.”
― Abigail Adams
My Life With Excel Point by Point Rubric – 5% of Final Mark
Follow the instructions below and make your
submissions in accordance with the your Professor’s
due date, which can be confirmed in your FOL
Course Calendar. Typically, the 5% custom
project is due to be submitted to the FOL
Submissions Box by 11:59pm on the Sunday at
the end of Week 13.
This is an assignment that will not be submitted on
SAM and cannot be emailed to your professor. It
must be submitted to the FOL Submissions Box.
Please note; while it is not at all prohibited to share ideas with your classmates for
this creative project; your work must be original. Plagiarism detection will be in
effect in the submissions box and any students sharing files, even copied and
pasted content, will be dealt with in accordance with college policy. So, create your
own work and have fun with it!
Similar to a SAM case, you have a list of requirements below. Each requirement
carries a specific weight out of 100 points. You will be marked based on your
inclusion of these requirements.
Topic & Content
Listen
This is the fun part! Within appropriate limits (nothing derogatory or of a sensitive
nature), you may choose any topic. The only requirement is that the chosen
subject matter provides you with numerical and also financial data where the
potential to borrow or loan money is involved. FYI – credit cards loan you money
and have interest rates! You need a topic where you could potentially have a
situation where money is borrowed or loaned, even if the data doesn’t exist yet in
the real world. To explain, some of (or all of) the data can be fictitious, as you will
be required in the latter part of the case to create a loan amount with an interest
rate and a loan period.
In terms of topic choice, remember that this is not an essay. You will not have to
write or create sentence structure, grammar, etc. What you need is data! So, be it
a daily spending routine, workout regiment & diet intake (health food is expensive),
fantasy sports league with money involved, a business you run or that involves you,
utility costs, grocery expenses, trip planning, mileage and gas consumption or other
driving related themes, etc., you should be able to come up enough numbers to
apply the skills as required in the list below. Any topic that can provide the level of
data that will enable you to complete this project is acceptable, and choosing a
topic and integrating your own data into Excel is your first step in this project. This
is not something your professor can do for you. Your knowledge going into Week
13 of this course should be more than enough to facilitate a variety of information
from your life being organized into an Excel Workbook.
You may use any of the ideas provided or come up with your own, as long as
the topic can generate enough data to accomplish the following:
A minimum of 8 categories of data that would fill an Excel table. Here are
some examples, but again, you are encouraged to come up with your own
ideas:
MONTHLY LIVING EXPENSES: a student could easily generate up
to 10 categories of data, with some obvious choices being groceries,
vehicle & gasoline or transit expenses, rent, home internet, cell,
electricity, natural gas, water, entertainment, etc.
WORKOUT ROUTINE: grouping your workouts into different regions
of the body and the types of food you may consume at different times
of day when you are working out, you could easily generate a
plethora of workouts, with at least 10 separate routines. This is a
good one to run with…no pun intended, and for the money portion,
you could tie into billing for meal supplements.
With the first column in the table identifying the data series, you should be
able to generate at least 8 columns of data related to these series. Here are
some examples:
I.E. workout 1, workout 2, workout 3…
I.E. School Year Expenses: September, October, November…
I.E. Game 1, Game 2, Game 3…
I.E. Order #, Cost, Tax, Balance Due, Interest Rate…
Refer to Weeks 8 and 11 for examples or see below:
Again, this is just an example! Get creative and come up with your
own data. Other ideas for data might include a private student loan
that is interest bearing, money lent to you to start a business, money
lent to you as a sponsorship for an artistic endeavor, a car loan, etc.
Bottom line, you need some numbers that will enable you to
complete financial functions, and these numbers (in the yellow
highlighted cells) can be completely independent of the data in the
table if they have to be. The instructions below will explain what you
need. Please note that the above example does not include or show
many of the requirements in the file, but is simply meant to show an
example of a table that would be acceptable for this project.
This next part is the part that gets a little tricky, and this is where you are
encouraged to be as creative as your heart desires! Even if money doesn’t
play a large role in the topic that you have chosen, you need to
conceptualize and create a scenario in which it could cost you money as a
result of having to make partial monthly payments due to the borrowing this
money, or, it could be a similar scenario where you have loaned money to
someone. Most providers of any type of service or product have late fees
and then interest rates against the money you owe. Even with the workout
routine example, you could imagine a scenario where you find yourself
involved with an expensive meal supplement program that has resulted in
an ongoing unpaid balance on your credit card. Case in point, you have to
come up with a scenario that involves interest being paid (or collected),
thereby allowing you make use of certain financial functions taught in Week
12.
A large part of this the project is demonstrating to your
professor not only that you know how to use Excel, but also
that you know when and why to use Excel! As found in the list of
steps below, you will be required to demonstrate the effective use of several
financial functions from Week 12 along several other Excel skills that you learned
this semester. While professors will still be holding a lab session in Week 13 (no
lecture hour, but the lab session will still occur for extra help) to provide assistance
with the project, professors will not be helping students to come up with scenarios
in which some of these functions may be used as this level of assistance would
simply defeat the purpose of the project. Again, if you have to make-up the data,
this is fine. Remember that you are now an advanced Excel user! If you can come
up with one month, one week, one set up data (i.e., one column), you could simply
use excel to work the math to provide different variations of that data across more
columns, with a few other columns that may help you to sort the data in different
ways.
Required Steps / Out of 100 Possible Points (might be helpful to copy and print this list)
- 5 Points – File Naming: Create a standard Excel file named as follows –
firstname_lastname_customproject.xlsx. Your own first and last name would
replace firstname and lastname in the file name. - 4 Points – Worksheet Naming & Tab Formatting: Provide a one or two
word name for your first worksheet that is related to its content. Change the
worksheet tab color to any color of your choosing that is not the default
color. - 12 Points – Creation of an Excel Table: Starting in cell A2, generate a
table of data on the first worksheet with at least 8 column headings and at
least 10 rows of data. - Format as an excel table using a style of your choosing. Do not
leave the default style (4 Points) - Name the table appropriately (2 Points)
- Using the Table Total Row tool, have at least one column totaled,
averaged, etc. (3 Points) - Have the table sorted with a sort within a sort just like step 2 in our
Week 8 case (3 Points) - 6 Points – Inputting Data + Merge and Center + Formatting:
- Input your full name into cell A1 followed by a dash and after the
dash, provide a 2 to 4 word description of the data in the table (2
Points) - Merge and center this text to be centered over the table created in
Step 3 (2 Points) - Choose a format that uses colors from the theme of your table that
affects the font or the fill color, or both (2 points) - 3 Points – Date Style Function: In row 1 but leaving 1 column in between
the table and your input cell, input the Today function. - 6 Points – Link to Web Page: Also in row 1 and just to the right of the cell
that contains the Today function from Step 5, insert a hyperlink to any
website that has relevance to your content. - Provide a working link (2 Points)
- Change the text to display (link text) to something other than the web
address, words that better describe the destination (2 Points) - Add screen tip text (hover text) that is not the same as the link text (2
points) - 5 Points – Number Formatting: Format any and all financial data (of which
you must have some) with Accounting format with 2 decimal places. - 5 Points – Conditional Formatting: Use Conditional Formatting to format
top or bottom percentage, at a level and format of your choosing,
somewhere within your table. - 5 Points – Image Insertion: Use an image or logo of your choosing (don’t
worry about copyright as these projects will never be published) and insert it
into the first worksheet in an appropriate location but kept small. Should not
take up more than 15 to 20 cells at most and likely just be positioned to the
right side of your table, or again, in a position that you feel is appropriate. - 12 Points – Chart Insertion: Based on a portion of or all of the data in your
table, insert an Excel chart as its own worksheet: - This worksheet can be made to be the second worksheet in your
workbook, again, named appropriately with a tab color of your
choosing (2 points) - Choose a chart type that is appropriate and choose a chart style. Do
not leave the default style (3 points) - Add data labels of your choosing on the data series (3 points)
- Give the chart an appropriate title centered above the chart (2
points) - Change the colors on the chart to a monochromatic color pattern of
your choosing (2 points) - 2 Points – Adding a Worksheet: Add a 3rd worksheet at the end after the
chart worksheet and name it Financial. The tab color for this worksheet
should maintain the default colors. - 14 Points – PMT Function with 3D Referencing:
- Go back to the data you created on the first worksheet. Underneath
the table, but only skipping 1 row, create a set of data on the first
worksheet (see visual example above where the project is being
explained) that provides a realistic monthly debt load that you are
carrying (or is paid to you, depends on the nature of your data) with
an interest rate and a total number of months in the loan period. You
should have 3 pieces of data formatted as you see fit in 3 separate
cells with information in the cells to the left identifying what the data is
(again, see the yellow highlighted cells in the visual example above).
You will not be marked on formatting for this step and the data that
you need is as follows, and you can refer to Step 4 in the Week 12
Case to see an example of the type of data that goes into a payment
function. Bear in mind however, the loan amount can be smaller (car
loan, personal loan, interest bearing money you have loaned to
someone to invest in their app project, etc.). It could be anywhere
from $5,000.00 to $500,000.00 to $5,000,000.00. (2 points) - The money owed (a loan amount)
- A monthly interest rate or an annual rate divided by 12 (rate)
- A number of months this loan will be permitted to remain
outstanding (number of payment periods, 12 months, 180
months, 60 months, etc.) - Now, go to the top of the new worksheet you created in Step 11 and
using 3D referencing, use a formula without a function that brings this
data from the first worksheet into the top of the new worksheet,
referencing each of the 3 pieces of data; the loan amount, the
interest rate, and the number of monthly periods in the loan; thereby
bringing this data into the top of the new worksheet into 3 separate
cells using 3D referencing. (3 points) - Create a PMT function referencing the data that is now present in the
new worksheet that will calculate the monthly payment based on the
chosen interest rate and number of periods in the loan. Use cell
references to the cells in which the data was just placed in the new
worksheet sheet – do not type in the raw data! - 8 Points – PV Function: You now want to know what the total loan will cost
you, with interest, if you ask for an extra 24 months. To do this, you will use
the PV function. Using the monthly payment calculated in Step 12 (HINT:
the answer to step 12 will be required in the PV function, for help you may
refer to Step 11 in the Week 12 Case) and any other required inputs that
were added to this worksheet in Step 12 (rate and number of months+24)),
calculate the present value of the loan for the length of the term that you
suggested (your total payment periods or number of months) + an additional
24 months, all at the same intertest rate. - 8 Points – FV Function: Now, looking at the present value of the loan with
an extra 24 months included, you want to compare it to the future value of
the loan at the length of term that you originally suggested (number of
months only, without adding anything to that figure). To do this, you will use
the FV function. Again, using the monthly payment calculated in Step 12
plus any of the other inputs required as generated at the beginning of Step
12 (HINT: for extra help, refer to Step 12 in the Week 12 Case), calculate the
future value of the loan at the end of the term. - 5 Point Creative Round – Formatting Financial Worksheet: Format the
final worksheet with any number of tools and formats, any way you see fit.
Get creative!
Save your file and submit the FOL Submissions Box!
DUE DATE: Always refer to the FOL Calendar and / or FOL Submission Box for
Exact Due Dates, but typically, this project will be due on Sunday evening at the
end of Week 13 of the course unless otherwise noted.
