Name: Anonymous 2011-03-16 22:00
Having a bit of trouble with this assignment. I thought I knew how to do it then i screwed up. Any help? :
Section 1 (Section 1 worksheet)
Create a spreadsheet to calculate a family's income. The spreadsheet should look like the one given to you, except that you should use your name instead of Smith. A copy of the spreadsheet given to you is on the S drive (A 08 – Income - Data.xlsx). Besides the numbers provided, you have to fill in the amount earned for each of the categories:
a) Baby Sitting: The baby sitting hourly rate is:
• $9.50 for 25 hours or more
• $9.25 for 22 or more hours
• $9.00 for over 18 hours
• $8.50 for 12 hours or more
• $8.25 for over 9 hours
• $8.00 for all others
You must use a lookup table to calculate the hourly rate
b) Mowing Lawns: The rate for mowing lawns is $18 per lawn if two or fewer lawns are mowed and $20 for all other cases.
c) Painting: The daily rate for painting is:
• $100 per day for 3 or less days of painting
• $90 for each day beyond the first 3. For example, if you paint for 5.5 days, you get paid $100 for each of the first three days and $90 for each of the remaining 2.5 days giving a total of $525.
You must use the “if” function to do these calculations.
d) Flipping Burgers: The hourly rate for flipping burgers is:
• $9.75 per hour for over 55 hours
• $9.25 per hour for 40 hours or more
• $8.75 per hour for all others
You must use the “if” function to do these calculations.
e) Pizzas Delivered: The amount earned per pizza delivered is:
• $3.00 for 4 or less pizzas
• $3.50 for less than 12 pizzas
• $3.75 for less than 20 pizzas
• $4.75 for all other amounts of pizza delivered
You must use a lookup table to do these calculations
f) Family Total: The total for each row for the entire family (Column G).
g) Total Earned: The total earned by each person in the family (Row 18).
h) Conditional Formatting (All the conditional formatting should work, even if the data is changed):
• Family Members who make over $900 should have their total income in dark red with a yellow fill (B18:F18).
• Family members who make above the average for mowing lawns should have their lawn mowing income in dark green with a light green fill (B14:F14).
• The flipping burgers income row should use the flag icon set with default characteristics (B16:F16).
• Use light blue data bars for the family totals for the amount earned (G13:G17).
i) Goal Seek – Use goal seek to figure out how many days of painting it would take Mary to earn $987. Once you have figured it out, type in the answer you get in cell A20 (with 3 decimal places) and reset the number of days painted for Mary to 5.5.
Section 2 (Section 2 worksheet)
Investing for retirement can never start too early. The formula for determining the future value of a stream of annual investments is: FV=A(annual savings) x ((1 + interest rate)n-1)/interest rate where n is the number of years saved and the interest rate is in decimal format i.e. 7% is .07.
Do the following:
1) Develop a spreadsheet based upon the expression provided above using the format provided.
2) The amount to be saved per year is $2000.
3) Develop a spreadsheet developing a single formula and copying it throughout the spreadsheet provided. To help you develop the correct formula the value in the 15 year column at 6% is $46,552 (accounting format).
Hand in your printed spreadsheet (2 above) and the printed selection which shows your formulas (3 above).
Handin the following:
Section 1 of the assignment
1) Print out the entire Section 1 worksheet (including the look up tables). Your formulae should always work, even if some of the numbers in the spreadsheet initial data are changed. Make sure your printout fits on one page and is formatted in the same way as the printout given to you.
2) Change your spreadsheet so that formulae are displayed, instead of their results. Print out the formulae in cells F4:G18 (Mary’s earnings and the totals). Make sure your column widths are wide enough to display all the formulae.
Section 2 of the assignment.
1). Print out the entire Section 2 worksheet. Keep the formatting as provided.
2). Change your worksheet so the formulae are displayed, instead of their results. Print out the formulae in cells F5:I13. Make sure the column widths are wide enough to display all the formulae. It should fit on one page.
Staple/clip the two pages together.
Section 1 (Section 1 worksheet)
Create a spreadsheet to calculate a family's income. The spreadsheet should look like the one given to you, except that you should use your name instead of Smith. A copy of the spreadsheet given to you is on the S drive (A 08 – Income - Data.xlsx). Besides the numbers provided, you have to fill in the amount earned for each of the categories:
a) Baby Sitting: The baby sitting hourly rate is:
• $9.50 for 25 hours or more
• $9.25 for 22 or more hours
• $9.00 for over 18 hours
• $8.50 for 12 hours or more
• $8.25 for over 9 hours
• $8.00 for all others
You must use a lookup table to calculate the hourly rate
b) Mowing Lawns: The rate for mowing lawns is $18 per lawn if two or fewer lawns are mowed and $20 for all other cases.
c) Painting: The daily rate for painting is:
• $100 per day for 3 or less days of painting
• $90 for each day beyond the first 3. For example, if you paint for 5.5 days, you get paid $100 for each of the first three days and $90 for each of the remaining 2.5 days giving a total of $525.
You must use the “if” function to do these calculations.
d) Flipping Burgers: The hourly rate for flipping burgers is:
• $9.75 per hour for over 55 hours
• $9.25 per hour for 40 hours or more
• $8.75 per hour for all others
You must use the “if” function to do these calculations.
e) Pizzas Delivered: The amount earned per pizza delivered is:
• $3.00 for 4 or less pizzas
• $3.50 for less than 12 pizzas
• $3.75 for less than 20 pizzas
• $4.75 for all other amounts of pizza delivered
You must use a lookup table to do these calculations
f) Family Total: The total for each row for the entire family (Column G).
g) Total Earned: The total earned by each person in the family (Row 18).
h) Conditional Formatting (All the conditional formatting should work, even if the data is changed):
• Family Members who make over $900 should have their total income in dark red with a yellow fill (B18:F18).
• Family members who make above the average for mowing lawns should have their lawn mowing income in dark green with a light green fill (B14:F14).
• The flipping burgers income row should use the flag icon set with default characteristics (B16:F16).
• Use light blue data bars for the family totals for the amount earned (G13:G17).
i) Goal Seek – Use goal seek to figure out how many days of painting it would take Mary to earn $987. Once you have figured it out, type in the answer you get in cell A20 (with 3 decimal places) and reset the number of days painted for Mary to 5.5.
Section 2 (Section 2 worksheet)
Investing for retirement can never start too early. The formula for determining the future value of a stream of annual investments is: FV=A(annual savings) x ((1 + interest rate)n-1)/interest rate where n is the number of years saved and the interest rate is in decimal format i.e. 7% is .07.
Do the following:
1) Develop a spreadsheet based upon the expression provided above using the format provided.
2) The amount to be saved per year is $2000.
3) Develop a spreadsheet developing a single formula and copying it throughout the spreadsheet provided. To help you develop the correct formula the value in the 15 year column at 6% is $46,552 (accounting format).
Hand in your printed spreadsheet (2 above) and the printed selection which shows your formulas (3 above).
Handin the following:
Section 1 of the assignment
1) Print out the entire Section 1 worksheet (including the look up tables). Your formulae should always work, even if some of the numbers in the spreadsheet initial data are changed. Make sure your printout fits on one page and is formatted in the same way as the printout given to you.
2) Change your spreadsheet so that formulae are displayed, instead of their results. Print out the formulae in cells F4:G18 (Mary’s earnings and the totals). Make sure your column widths are wide enough to display all the formulae.
Section 2 of the assignment.
1). Print out the entire Section 2 worksheet. Keep the formatting as provided.
2). Change your worksheet so the formulae are displayed, instead of their results. Print out the formulae in cells F5:I13. Make sure the column widths are wide enough to display all the formulae. It should fit on one page.
Staple/clip the two pages together.