CS130/230 Assignment 3
Graphing and Logic
Date Assigned: Thursday, February 19, 2004
Date Due: Thursday, March 4, 2004
Total Points: 60
Add the following worksheets to the same Excel workbook you created for assignment 1. Do not delete the assignment 1 worksheets.
The site http://cdiac.esd.ornl.gov/epubs/ndp019/ushcn_r3.html contains data on the monthly mean average temperature for various weather stations around the United States.
From this website, find the weather station that is nearest your hometown, and copy the monthly mean average temperature to an Excel spreadsheet. The format of the data is:
Station number Ð year- jan, - feb Ð mar Ð apr Ð may Ð jun Ð jul Ð aug Ð sep Ð oct Ð nov Ð dec Ð winter (dec-feb) Ð spring (mar Ð may) - summer (jun Ð aug) Ð fall (sep Ð nov) Ð annual average temperature (jan Ð dec). All values are in degrees F and Ð99.99 indicates missing data. The number of years will depend on the data that you use. The data for Vancouver, Washington ranges from 1891 to 1994.
Copy this data into your worksheet. You will need to use the (Text to Columns) command in the (Data) menu to split the data into columns.
Once you have all your data in place, do the following:
- Add a row at the end of the table that will contain the average temperature for each month for the whole period.
- Add a row to store the maximum average temperature for each month.
- Add a row to store the minimum average temperature for each month.
Insert a line chart into your worksheet for the first year in your data and the last year in your data. The x-axis will be the months of the year (make sure that these are labelled correctly) and the y-axis will be the temperature in Fahrenheit.
Your uncle runs a custom drapery shop. HeÕs decided to send his son to private school. He has job orders at his shop for the next six months: $800 in January, $750 in February, $550 in March, $665 in April, $388 in May, and $767 in June. Each month your uncle spends 40.25% of the orders on material, 3.5% on patterns, 3.25% on his retirement and 44% on food and clothing. The remaining profits will be put aside for his sonÕs education. Also, your aunt has agreed to provide an additional $25 whenever your uncleÕs monthly profits exceed $50.
- Create a worksheet that shows orders, expenses, profits and savings for the next six months, and totals for each category.
- Goal seek to determine what percentage of profits to spend on food and clothing if $800 is needed for the school.
Everything in the worksheet should be right justified and formatted correctly (i.e. currency, percentage, etc) except the text that asks the user to enter in the values.
You are buying a car. You donÕt have any money to put down, but you do have your old car to trade in. YouÕve gotten 3 different quotes from 3 different dealers. At first glance, you think that you should go with the lowest car price, but then you realize that maybe you should create a spreadsheet to make sure.
The input to the spreadsheet is:
- Car price
- Yearly Interest rate
- Trade in value
- Length of loan in years
The three different quotes are:
1. Price: $23, 500.00
Interest rate: 6%
Trade in: $500,00
2. Price: $24, 999.00
Interest Rate: 3.5%
Trade In: $400.00
3. Price: $24,000.00
Interest Rate: 4.2%
Trade In: $200.00
A) Create a general spreadsheet that allows the user to input the 3 values at the top. Create an amortization table for the loan assuming that the duration of the loan is 3 years. This table should include the payment period, beginning balance, the monthly payment, the interest payment, and the ending balance.
B) At the bottom of the spreadsheet, specify which of the three quotes is the best, and why it is the best.
C) Your favourite salesman (the one who offered you the best deal) is still concerned that the loan payment per month is too high for you. He offers you a 4-year loan at 1 percentage point higher interest rate (the price and the trade in remain the same). What do you think of this offer? Why would you want to accept it? Why wouldnÕt you want to?
Add these worksheets to the Excel workbook you created for your previous assignment and place it in the ÔCS130 DropÕ folder. Your workbook must be in the drop box by 6pm on the day the assignment is due for full credit.