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:
- 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 determining 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
Getting Started with Excel
Cell Basics
Formatting Cells
Introduction to Formulas
More videos can be found here: GCF Microsoft Excel
Assignment 1 - Working with Tables
Open a new workbook in Microsoft Excel and enter the data below:
Directions:
1. Turn the text data in cells A1:F8 into a Table
2. Modify Table Style
3. Sort the Table
4. Filter the Table
5. Add a New Column
6. Inserting Sparklines (these are mini line or column charts that fit inside a cell)
When you are finished please submit your assignment to EngradePro.
1. Turn the text data in cells A1:F8 into a Table
- Insert tab --> Table
- Select range on worksheet A1-F8
- Check off 'my table has header' (this tells Excel that the first row of data contains the table headings)
- Click OK
- Name the table MathGradesTable in the upper left corner (Table Tools menu - Design tab - Properties - Table name)
2. Modify Table Style
- Select Table --> Design
- Choose design - "Table style medium 9"
- Find the checkbox for First column within the activating table style options group (this makes the first column with the last names different shade of blue for easy viewing)
3. Sort the Table
- Place cursor inside the table
- Go to the Data tab
- Click "sort"
- Choose to sort by: grade level - smallest to largest
- Add another level (+ sign) and sort by last name - A-Z
4. Filter the Table
- Choose the down arrow next to the grade level column. Turn off the check mark next to 9th graders to not include this in the filter then click OK.
- Next, choose the down arrow next to the 'Math Ch 1 Test' Column. Go to Number Filters - Less than 70, click ok. Now you can see all the students who struggled on this math test.
- In order to clear filters you should go back to the Data tab, click Sort & Filter Group - Clear filter button.
5. Add a New Column
- Click in cell G1 and type the word Progress
6. Inserting Sparklines (these are mini line or column charts that fit inside a cell)
- Click in cell G2
- Go to Insert tab - Sparklines Group - Lines Sparkline
- The data you want your sparkline to show is in cell range D2:F2
- Click OK
- Now fill down to the rest of the cells in your table's column G so they have the sparkline function. Click on the Auto Fill Down handle from cell G2 and drag it all the way down to G8. Now there are spark lines showing your students math test score progress in an easy to view line chart.
When you are finished please submit your assignment to EngradePro.
Assignment 2 - Working with Graphs
Download the PowerPoint below to find your directions for this assignment.
excelactivityfootballspreadsheet.pptx | |
File Size: | 181 kb |
File Type: | pptx |
Assignment 3 - Conditional Formatting
Directions:
Download the file below. All directions for this assignment will be located in the first tab.
microsoft_excel_conditional_formatting.xlsx | |
File Size: | 183 kb |
File Type: | xlsx |
Assignment 4 - Functions
functions_and_formulas.xlsx | |
File Size: | 170 kb |
File Type: | xlsx |
Assignment 5 - Payroll
excel_payroll_assignment_.doc | |
File Size: | 48 kb |
File Type: | doc |
Project 1 - Buying a Car
Background:
For your birthday, your family says they will give you $5,000 towards purchasing a car, but you need to present an argument for the best car to purchase. You will be keeping the car for at least 5 years.
YOUR JOB IS...
Part 1:
Use the internet to research 5 cars you would want to buy. The cars can be new or used, but they must cost under $35,000. (Advice: Use car manufacturer websites to search for new cars and cars.com as a reference for used cars)
For each of the 5 cars you select, create an Excel spreadsheet that contains the following:
Here is a general example of what your table should look like. Please copy & paste a picture of your car choices on the spreadsheet as well.
For your birthday, your family says they will give you $5,000 towards purchasing a car, but you need to present an argument for the best car to purchase. You will be keeping the car for at least 5 years.
YOUR JOB IS...
Part 1:
Use the internet to research 5 cars you would want to buy. The cars can be new or used, but they must cost under $35,000. (Advice: Use car manufacturer websites to search for new cars and cars.com as a reference for used cars)
For each of the 5 cars you select, create an Excel spreadsheet that contains the following:
- Make (ex: Honda, Subaru, etc.)
- Model (Civic, Impreza, etc.)
- Cost (Must be $35,000 and under)
- New or Used
- If used, how many miles
- Gas Mileage (This is how many miles the car gets per gallon. Look up the city and highway mileage for each car and calculate the average.)
- Color
Here is a general example of what your table should look like. Please copy & paste a picture of your car choices on the spreadsheet as well.
Part 2:
In the Excel document you have started, create a new sheet. Label it "Cost Analysis"
You will be analyzing the cost of each of the 5 cars you have chosen over a 5 year period. You will need to create a Table in Excel that shows the yearly cost for each of the following:
- Gasoline
- Insurance
- Brakes
- Tires
- Transmission Fluid
- Registration
- Inspection
- Car Payment (if you have one)
Here is an example of the table you will need for each of the 5 cars. (Hint: Consider making one table with borders and before you put in all your information copy & paste 4 more tables to save time)
INSTRUCTIONS FOR FILLING IN THE DATA FOR THE ABOVE TABLE:
GASOLINE COSTS - We will assume you are driving the car 12,000 miles each year. We will also assume that gas is now $3 a gallon. Look back at your first sheet in Excel to find out the miles per gallon (gas efficiency) of the car.
To calculate gasoline costs the formula is 12,000 multiplied by 3 divided by the miles per gallon
(12,000*3/cars mpg)
Example: =12,000*3/25
INSURANCE COSTS - You will be going under your family's plan for insurance. The cost per month will be $225 for females and $350 for males. If you choose a sports car, the price will be double (considered higher liability).
To calculate the cost of insurance you need to multiply the monthly cost by 12.
BRAKES - You will need a new set of brakes approximately every 30,000 miles. The average cost of brakes is $400.
TIRES - You will ned a new set of tires approximately every 30,000 miles. The average cost of tires is $500.
TRANSMISSION FLUID - You will need to change the transmission fluid approximately every 50,000 miles. The average cost of transmission fluid is $200.
REGISTRATION - You will need to renew the registration on your car every year, the cost for this is around $200.
INSPECTION - You will need to get your car inspected each year which costs around $40.
CAR PAYMENT - If a car you are choosing costs over $5,000 you will need to make a car payment on the difference between the cost of the car and the $5,000.
Please calculate the payment at 60 months (5 years). The interest is 0% for all cars.
Step 1:
- Take the cost of the car and subtract the $5,000 cash (ex: $20,500 - $5,000 = $15,500 is the amount of the loan)
- Take the total cost of the loan and divide it by 60. This will be your monthly payment. Then multiply that number by 12 to get your yearly payment.
Part 3:
Create a third sheet in your Excel document and label it "CHARTS"
Create a third sheet in your Excel document and label it "CHARTS"
Part 4:
It is decision time. Label Sheet 4, MY CAR. Put a picture of the car you would choose for yourself after your analysis and explain in a text box why you chose it.
Submit your completed project on EngradePro.
Project 2 - Planning a Vacation
In this project, you will plan a vacation for you and your immediate family (ex: two adults and two children). You will be leaving from New York. Your vacation will be 5 days long and you are allowed an additional 2 days for travel if necessary for a total of 7 days. You have a budget of $3,000 for food, lodging, and activities and an additional $1,500 for travel including gas, car rentals, taxis, trains, buses, and air travel. The project will be organized using several budget spreadsheets in Excel.
What needs to be included:
- Travel arrangements from your city of origin to your destination and back. Include pricing, dates, times, and travel to and from airports and bus/train stations. If you are driving in a family or rented car, include mileage to and from your destination with calculations for the price of gasoline.
- Ages of all family members at the time of travel. Tip: Ages of children will influence many of your activity choices.
- Detailed daily plan including all transportation, activities, and meals.
- Each daily plan should begin no later than 9:00am and end no sooner than 7:00pm. Keep this in mind to avoid running out of things to do! Tip: Try to schedule some restful activities in each day such as visiting a city park to sit and feed the ducks, catching a movie in a theater, or walking on the boardwalk with ice cream.
- Include gratuity, specific menu items and prices for all meals. You may include copies of menus in your PowerPoint. Tip: Breakfast is often included in hotel room rates so check to be sure!
- Include ticket prices, entry fees, parking fees, and any equipment rental fees such as scuba gear, skis, or kayaks.
- Include a visit to at least one museum or historical landmark on your vacation.
Step 1:
- Research a list of 5 locations you would like to visit and place them in a Microsoft Word Document.
- Select dates for your trip
- Narrow down your list by using any of the following websites to research transportation and hotel costs:
Step 2:
Make a final decision about the location you will be traveling to.
**Once you reach this step you will be creating an Excel document to track your research***
THE SPREADSHEET WILL BE FILLED IN PIECE BY PIECE AS YOU COMPLETE YOUR RESEARCH.
Movie Theater Assignment
Your task is to prepare a financial statement for a movie theater. Read the following definitions before beginning.
Definitions:
Gross Profit
We need to know two things to calculate net income: gross profit and operating expenses. In terms of a formula, gross profit equals revenues minus the cost of goods. The theater revenues is the money the theater takes in from selling movie tickets and concessions.
Operating Expenses
Operating expenses are the sacrifices the business makes in order to get sales. For our theater, operating expenses include the building rent, salary, and employee benefits, like medical insurance.
Cost of Goods
Remember, gross profit equals revenues minus cost of goods. The concession cost of goods is the cost for ingredients and packaging for the concession items. The movie cost of goods is the distributer costs and the movie lease. Since we don’t own the movies we show, we work with a distributor to get movies. For each movie ticket we sell, we pay the distributor a fee. Also, once a year, we pay a fee to the distributor as part of our agreement to show their movies. Even if we don’t sell a single movie ticket, we still have to pay the yearly fee. That fee is called the movie lease.
Gross Profit
We need to know two things to calculate net income: gross profit and operating expenses. In terms of a formula, gross profit equals revenues minus the cost of goods. The theater revenues is the money the theater takes in from selling movie tickets and concessions.
Operating Expenses
Operating expenses are the sacrifices the business makes in order to get sales. For our theater, operating expenses include the building rent, salary, and employee benefits, like medical insurance.
Cost of Goods
Remember, gross profit equals revenues minus cost of goods. The concession cost of goods is the cost for ingredients and packaging for the concession items. The movie cost of goods is the distributer costs and the movie lease. Since we don’t own the movies we show, we work with a distributor to get movies. For each movie ticket we sell, we pay the distributor a fee. Also, once a year, we pay a fee to the distributor as part of our agreement to show their movies. Even if we don’t sell a single movie ticket, we still have to pay the yearly fee. That fee is called the movie lease.
income.xlsx | |
File Size: | 9 kb |
File Type: | xlsx |
Part 1
Directions:
1. Open the “Income.xlsx” spreadsheet and complete the following calculations
- Total Revenues
- Total Cost of Goods
- Gross Profit
- Total Operating Expenses
- Net Income
2. Format the spreadsheet without creating a table.
- Format the title. Include a border around the data.
- Include at least one shade or pattern area.
- Align and format the data.
Example:
Directions:
1. Open the “Income.xlsx” spreadsheet and complete the following calculations
- Total Revenues
- Total Cost of Goods
- Gross Profit
- Total Operating Expenses
- Net Income
2. Format the spreadsheet without creating a table.
- Format the title. Include a border around the data.
- Include at least one shade or pattern area.
- Align and format the data.
Example:
3. Create a line graph on the spreadsheet to represent the net income.
4. Review and revise your work.
Part 2
Directions:
Look at the downward trend of the net income and profit in the graph you just created. This is a business problem that can be solved. Your task is to run the numbers for two different options to see how to reverse the downward trend of the net income.
Option 1 is to increase gross profits by raising the price of the tickets.
Option 2 is to increase gross profits by cutting the cost of the goods. Now, this would be done by showing second run movies instead of the popular new releases. If you can increase gross profit, and keep a hold on operating expenses, then the theater should see an upward trend in net income.
Option 1:
1. Using the spreadsheet you have just created, insert a new "sheet" into it. Label it Sheet 2.
2. Set up your spreadsheet in this way:
3. Fill the missing data into the spreadsheet. Use the formulas box for help.
4. Compare the gross profit of the various ticket prices.
5. Choose the ticket that results in the highest gross profit and color that cell a unique color.
Option 2:
1. In Sheet 2, add a column to the right and label it' 2nd run movies."
2. Using the information below, calculate the gross profit for second run movies. Use a ticket price of $5.50.
3. Compare the gross profit for the second run movies option to the gross profit of the ticket price you chose from option 1.
4. Select the option with the best gross profit and choose a unique cell color to represent it.
5. Save your work and submit it to Engradepro.
4. Select the option with the best gross profit and choose a unique cell color to represent it.
5. Save your work and submit it to Engradepro.