USA: +1(669)289-8683 
Sign in
Rubric
DQR 100 Mini-Project 1 Rubric
| Criteria | Ratings | Pts |
20 pts |
|---|---|---|
|
30 pts |
||
| 30 ptsAll correct25.35 pts3 out of 4 correct22.35 pts2 out of 4 correct19.35 ptsAttempted but mostly incorrect0 ptsDid not attempt | ||
| 20 ptsAnswered both with thoughtful answers16.9 ptsAnswered both with minimal effort14.9 ptsAttempted but too far off topic12.9 ptsAttempted but mostly incorrect0 ptsDid not attempt | ||
|
Total Points: 100 |
Previous
Next
Excel Mini-Project 1: Data Organization
Name:
Overview
Directions
Instructor:
We are going to learn how to organize data using some of Excel’s tools and m
important budgeting concepts. To get full credit you need to include a
Step 1: Organize the data on Sheet2 by utilizing the sort tools (by following steps
Turn on the filter buttons on each column header.
Sort the data by amount.
Answer questions 1 and 2 on Sheet4.
Step 2: Using Excel’s math functions to summarize data (follow steps below):
Sort the data by category.
Complete the table on Sheet3 by computing the sum of each category.
Answer questions 3, 4, and 5 on Sheet4.
Step 3: On Sheet3 create a bar graph to represent the categories.
On Sheet3 create a pie chart to represent the categories.
Answer questions 6 and 7 on Sheet4.
Step 4: Answer questions 8, 9, and 10 on Sheet4.
You are done!
Tips!
Look for color-coded tips on each step if you are stuck on how to use Excel. They w
If you venture outside these thick black bars you have gone too far! All of the mate
But feel free to use this area for notes or calculations or experimentation!
Date:
Excel’s tools and math functions. Simultaneously, we will learn about some
ou need to include a bar graph, pie chart, and answer the questions.
(by following steps below):
w steps below):
of each category.
to use Excel. They will be highlighted in green.
far! All of the material is inside of these bars.
mentation!
Below is the data that represents our sample person, Lin
Financio’s, spending during the month of September.
Date
1-Sep
1-Sep
2-Sep
2-Sep
3-Sep
4-Sep
4-Sep
4-Sep
5-Sep
5-Sep
5-Sep
7-Sep
7-Sep
8-Sep
8-Sep
9-Sep
10-Sep
11-Sep
11-Sep
12-Sep
12-Sep
15-Sep
16-Sep
16-Sep
16-Sep
16-Sep
17-Sep
20-Sep
21-Sep
21-Sep
22-Sep
23-Sep
23-Sep
23-Sep
26-Sep
26-Sep
28-Sep
28-Sep
28-Sep
Item
Rent
Utilities
McDonalds
Spotify
PSN Games
Progressive Insurance
Steam Games
Burger King
Publix
Panera Bread
KFC
Microsoft Games
Chipotle
ACA Health Plan
Wendy’s
Club Miami
Boca Mall
Walmart
Moe’s
Lynn Dining Center
Netflix
Phone Bill
Auto Shark LLC
Amazon
Taco Bell
Hulu
McDonalds
Burger King
Trader Joe’s
KFC
Panera Bread
Wendy’s
Disney+
Chipotle
Boca Beach Party
Moe’s
Whole Foods
CVS
Lynn Dining Center
Category
Rent
Utilities
Eating Out
Streaming
Gaming
Car Insurance
Gaming
Eating Out
Groceries
Eating Out
Eating Out
Gaming
Eating Out
Health Insurance
Eating Out
Going Out
Shopping
Groceries
Eating Out
Eating Out
Streaming
Phone
Car Loan
Shopping
Eating Out
Streaming
Eating Out
Eating Out
Groceries
Eating Out
Eating Out
Eating Out
Streaming
Eating Out
Going Out
Eating Out
Groceries
Beauty/Hygiene
Eating Out
Amount
$ 1,200.00
$ 188.16
$
18.89
$
9.99
$
89.99
$ 189.99
$
19.99
$
14.45
$ 139.60
$
18.42
$
17.73
$
59.99
$
13.03
$ 119.99
$
15.81
$
76.58
$
62.41
$ 101.80
$
12.99
$
13.18
$
8.99
$
49.99
$ 382.84
$
95.62
$
18.01
$
7.99
$
17.16
$
19.14
$
79.50
$
18.66
$
19.36
$
16.69
$
14.99
$
12.05
$
40.00
$
14.48
$ 135.67
$
24.18
$
14.54
30-Sep Taco Bell
Eating Out
$
16.78
Step 1:
Organize the data on Sheet2 by utilizing the sort tools (by following steps below):
Turn on the filter buttons on each column header.
Sort the data by amount.
Answer questions 1 and 2 on Sheet4.
Tips for Step 1:
first:
second:
third:
fourth:
fifth:
sixth:
To turn on the sort/filter buttons:
Highlight the columns Date, Item, Category, Amount.
Find the “AZ Sort/Filter” button near the top right of the Home menu bar.
In that “AZ Sort” button drop-down menu select the “Filter” option.
You will know it worked when you see little “drop-arrows” on the column
headers now (i.e. on Date, Item, Category, Amount)
now you can click those arrows and sort either by A to Z, Z to A, smallest to
largest, or largest to smallest.
now sort by amount from smallest to largest to answer question 1.
Step 2:
Using Excel’s math functions to summarize data (follow steps below):
Sort the data by category.
Complete the table on Sheet3 by computing the sum of each category.
Answer questions 3, 4, and 5 on Sheet4.
Here is Lin Financio’s summary of their current spending, separated by Required
Expenses and Discretionary Expenses.
Required Expenses
Category
Amount
Rent
Utilities
Car Loan
Car Insurance
Phone
Health Insurance
Groceries
Beauty/Hygiene
Total:
first:
second:
third:
first:
second:
third:
Discretionary Expenses
Category
Amount
Eating Out
Going Out
Gaming
Streaming
Shopping
Total:
Tips for Step 3:
Using Excel’s math functions.
To use Excel as a calculator, or to call one of its functions
you must start the cell with an “=”
For example if you type “=sum” into a blank cell you should see
the option to calculate the sum of some items.
Once selected you can simply highlight the cells which you want
to add together, then hit enter.
More tips:
Creating graphs:
highlight the data (usually two columns next to each other)
in this case category and amount.
Once the data is highlighted, go the “insert” tab near the top
and find “charts”
From there you can select many different types of graphs
and you can edit them too.
Step 3:
On Sheet3 create a bar graph to represent the categories.
On Sheet3 create a pie chart to represent the categories.
Answer questions 6 and 7 on Sheet4.
Create a Bar Graph to show visually which categories Lin is spending the most/least in. Put t
bar graph in the box below, and make at least one design change to it.
Category
Rent
Utilities
Car Loan
Car Insurance
Phone
Health Insurance
Groceries
Beauty/Hygiene
Eating Out
Going Out
Gaming
Streaming
Shopping
Amount
Create a Pie Chart to show visually what percentage of total expenses each category represe
Put the pie chart in the box below, and make at least one design change to it.
ding the most/least in. Put the
esign change to it.
nses each category represents.
ne design change to it.
Questions for you to answer. Write out your answer in the box below and make sure to explain yo
reasoning if prompted.
Q. 1
What was Lin’s second cheapest expense? Write the item and amount.
Answer:
Q. 2
What was Lin’s fourth biggest expense? Write the item and amount.
Answer:
Q. 3
What was the total Required Expenses and total Discretionary Expenses. Add
those together and what was Lin’s total expenses for the month?
Answer:
Q. 4
If Lin’s monthly income is usually only two paychecks for $1572.47 each, how much
did Lin spend over their budget this month?
Answer:
Q. 5
Experts say the key to a succesful budget is to ensure that you do not cut out all of
the fun in your life. What do you recommend Lin cut back on, i.e. which categories
and by how much?
Answer:
Q. 6
By looking at the bar graph, which is Lin’s second most expensive category?
Answer:
Q. 7
By looking at the pie chart, what percentage of total expenses does the category
“Eating Out” make up?
Answer:
Q. 8
Which of the two graphs that we made (bar graph or pie chart) is more useful in
comparing Lin’s spending habits. Explain why.
Answer:
Q. 9
Describe at least one new thing you learned about Excel’s organizational tools and
one new thing you learned about Excel’s math functions.
Answer:
Q. 10
Describe at least one new thing you learned about budgeting. Do you currently
have a budget that you follow? If not, would you like to start one?
Answer:
make sure to explain your
mount.
unt.
penses. Add
Perform the calculations here:
required:
discretionary:
total:
(try using excel as a calculator)
7 each, how much
Perform the calculations here:
income:
total spending:
difference:
(try using excel as a calculator)
not cut out all of
which categories
category?
es the category
more useful in
ational tools and
you currently
?
