Class Time Required: 2 weeks

This spreadsheet lesson is a culminating activity for a unit on percents. The percents should be automatically calculated (9% for the tax rate and 12% for the interest rate). Students will select the type of dream car they wish to buy with all of the options added. They could purchase a used car which would reduce their monthly payment. After tax and discounts are calculated students can begin adjusting their monthly payment by the number of years needed to finance the dream car. Guidelines will be given by the teacher as to how much they can spend monthly which corresponds to their class grade.

Students will need the first week of computer use to research the sticker price of their dream car and all of the extras they want on the car. The end of the first week will also be used to learn how to use spreadsheets and then create their own specific spreadsheet for their car. The teacher will act as their “financial advisor” letting them know the tax rate, discount rate, finance rate and affordability of their monthly payment. The second week will require students to rework their spreadsheet to make their dream car more affordable. All of the students will contribute to a class Hyperstudio “car lot” to be presented and assessed at the end of the second week.

Specific Objectives:
The students will input the car, it’s base price and options they want on their car from their homework research. Students will skip the years old column for a discount the first time calculating by hand. After the spreadsheet calculates the tax, and arrives at a principal for the loan, then the number of years of financing will be selected. After manipulating these columns, then the student may lower the sticker price by choosing a used car discount or choosing less options or even choosing a different car. The goal is for them to be able to afford a monthly payment based on their grade.

Base price + all of the Options = Sticker price
Sticker price + Tax (at 9%) = Sales Price
Sales price - (Years old x $1000) = Discounted price
Principal of Loan = Sales price + Tax - Discount
Interest = Principal x Rate (12% as an example)
Loan Amount = Principal + (Interest x Number of Years of loan)
Monthly Payment = Loan Amount ÷ Number of Years of Loan
Show how you can reduce the monthly payment. (i.e. show a line or bar graph of the monthly payment vs years old the car is OR monthly payment vs the numbers of years of the loan)

Materials Needed:
•Computers with internet access
•Spreadsheet
•Hyperstudio or other presentation tool

Activities:
Day 1 & 2 - Students do internet research to find the price of their dream car and extras they want.

Day 3 - Students practice making a spreadsheet with directions from teacher for calculating formulas, and making charts. The teacher will also change data to see results in the monthly payment.

Day 4 - Students make their own spreadsheet showing the Dream Car with all extras added, tax calculated, and financing set up, ending with a monthly payment.

Day 5 - Students adjust the data to include a discount when realizing the monthly payment is too high. This is determined by the “financial advisor’s” input i.e. grade in class (A=$800, B=$600, C=$400, D=$200). Also, years of financing is used to manipulate the payment downward.

Day 6 - Students practice making charts and line graphs with teacher directions to show what changes in data has on the line graphs and charts.

Day 7 - Students make their own charts and line graphs showing changes in years financing, discounts and price of car. Students collaborate to compare factors affecting the monthly payment and the best way to reduce their payments.

Day 8 & 9 - Students individually make a three card Hyperstudio stack showing internet pictures of their dream car, spreadsheet calculations, and line graphs

Day 10 - Students present their Hyperstudio stacks to class and summarizes how price, tax rates, interest rates and years of financing all effect affordability of cars.

Teaching Strategies:
Students research collaboratively on the internet to find base prices of dream cars and the extras that they want. Teacher gives instructions on the construction of the basic spreadsheet. Teacher additionally supplies the parameters of tax rates, interest rates, and discount rates as a “financial advisor”. Students also work collaboratively on creating the Hyperstudio “car lot” project. Students present the Hyperstudio projects to entire class on “Car Lot Day”.

Resources:

PowerPoint Presentation

Assesment Rubric (pdf file)

Spreadsheet Example (excel file)

Monthly Payment Comparasions (excel file)

Handout (pdf file)