Introduction to Microsoft Excel
Why is it important to learn how to use excel?
According to an article from Huffington Post:
"Understanding spreadsheet and word-processing software is a baseline requirement in nearly 80 percent of all middle-skill job openings, according to the report, first discovered by Lauren Weber at the Wall Street Journal.
The research defines middle-skill occupations as those that don’t necessarily require a college degree but pay more than the median national living wage of $15 an hour. These include store manager, retail supervisor and office/administrative assistant. The researchers looked at job listings posted in 2014."
Some examples of who might use Excel:
"Understanding spreadsheet and word-processing software is a baseline requirement in nearly 80 percent of all middle-skill job openings, according to the report, first discovered by Lauren Weber at the Wall Street Journal.
The research defines middle-skill occupations as those that don’t necessarily require a college degree but pay more than the median national living wage of $15 an hour. These include store manager, retail supervisor and office/administrative assistant. The researchers looked at job listings posted in 2014."
Some examples of who might use Excel:
- A teacher trying to figure out how their students are performing over time
- A small business owner wondering which product to keep and which to stop using
- A scientist trying to figure out the homogeneity of a dataset
- A housewife keeping track of household expenses and deterring what the family can afford
- A runner keeping track of their daily mileage and calories burned
Begin by watching the following introductory videos to Excel:
Getting Started with Excel
Cell Basics
Formatting Cells
Introduction to Formulas
More videos can be found here: GCF Microsoft Excel
Assignment 1
Part 1 - Create a Spreadsheet
Create a spreadsheet in Microsoft Excel to present information about items sold from a food truck over a six month period.
1.) Input the following data into your spreadsheet:
2.) Format your cells by selecting a color theme to make your information easy to read.
3.) Create two additional columns that contain the total amounts and average by using the formula tools.
4.) Title and save the spreadsheet into your folder.
Part 2 - Calculate the Gross Profit
Using the spreadsheet you have created, you will calculate the gross profit the food truck has made in the past six months.
Gross profit = the item sale price minus its cost times the number of items sold
1.) Below your original spreadsheet, skip a few cells and then copy and paste the item categories and months.
2.) Begin calculating the gross profit for each item.
Gross profit = the item sale price minus its cost times the number of items sold
1.) Below your original spreadsheet, skip a few cells and then copy and paste the item categories and months.
2.) Begin calculating the gross profit for each item.
This is an example of what your formula should look like:
= (item sale price - item cost)*number of items sold
=(C3-B3)*D3
3.) Add a row below the pretzel sticks item category and type total. Use this row to calculate the total gross profit earned each month for all of the items sold.
4.) Save your updated assignment and show a teacher.
Assignment 2: Grocery Shopping for a Party!
You are having a total of 6 people (including yourself) at your home for a party. You choose the occasion. Some ideas are: sports related party, birthday party, holiday party, or even just a "Hey we haven't seen each other in a long time" party.
You must:
1. Plan a menu.
- In Microsoft Word, type a list in bulleted form containing what you will be serving your guests. Do not forget appetizers, desserts, and soft drinks (Unless you are 21 - No Alcohol). Save this document with your name on it.
2. Set up a budget tracking sheet.
- In Microsoft Excel, set up the spreadsheet you will need to keep track of your expenses. (You will be held accountable for the money you spend!) You must spend between $120 and $160 - no more, no less. The spreadsheet should be set up like the example provided below. Make sure you save your spreadsheet in your folder with your name on it.
3. Go shopping on the Internet for the food, drinks, and accessories you will need. Assume you have common condiments like ketchup, salsa, salt, pepper, and butter. You may not be able to find everything you need on your menu and might have to revise it a little as you shop.
- Go to the web site www.peapod.com and use it to search for the items you will need.
- Each item you decide to buy must be typed into your spreadsheet.
- Be sure to include how many of each item you are buying and what size the package is so all of your columns and rows are filled out properly.
- Keep an eye on your Grand Total cell. Be sure not to exceed $160.
- Once you have purchased between $120 and $160 worth of food and supplies, make sure you save your document again.
Extra Credit will be awarded for:
- Coming within $1 of $160 without going over (Just like the Price is Right!).
- Correct spelling on the menu and spreadsheet.
Here is an example to guide you when creating your spreadsheet:
Grocery List | |
File Size: | 189 kb |
File Type: | jpg |
Assignment 3: College Budget
1. Create an excel spreadsheet in order to organize the following data related to college expenses:
Imagine you are a college student who....
- Works two jobs to earn $1,600 a month
- Receives $500 from your parents each month
- Is getting $650 a month in financial aid and has a loan that equals out to $500 a month
Your monthly expenses include:
- Room and Board for $1,100
- Cell phone bill for $75
- Grocery shopping costs of $250
- Expected auto expenses of $50
- A credit card bill of $175
- Insurance for $125
- $75 for going out for entertainment
- $80 in miscellaneous fees
Your semester expenses (a semester is 4 months) include:
- $2,600 for tuition
- $793 in school fees
- $550 for textbooks
- $200 for transportation
2. Once you put your information into the spreadsheet, calculate the totals for each section. Be sure to calculate the cost per month based on the semester expenses.
3. Calculate the following based on your information:
- Your net monthly income
- Your net monthly expenses
- The balance you have left over after everything you have spent
4. Create a 3-D Pie chart using the data from your monthly expenses.
View the following video for assistance creating a chart: Microsoft Excel Charts
5. Title your chart and label each section using percentages.
You can use this example below to guide you when designing your spreadsheet: