Print
Rubric
Lesson
Example

Projects.Excel.EC.03

Excel Extra Credit 3:
Executive Chairs

Problem

A company that sells cheap, low-quality chairs to CEOs wants to determine how much they will make over the next five years if their current trends continue. You will use an Excel spreadsheet with an assumption table to calculate the data they require.

Click To Enlarge

Instructions

  1. Create a spreadsheet that that contains the information above. The formula for Sales in 2008 is Units Sold 2007 * (Unit Cost/(1 - Margin))
  2. The rest of the Sales cells are created with the formula Sales for the Previous Year * (1 + Annual Sales Growth) * (1 - Annual Price Decrease)
  3. The formula for Total Cost of Sales is Sales for Year * (1 - Margin)
  4. The formula for Gross Income is Sales for Year - Total Cost of Sales
  5. The formula for Advertising is 1000 + 15% * Sales for Year
  6. The formula for Rent is Rent for Previous Year * (1 + 10%)
  7. The formula for Salaries is 18% * Sales for Year
  8. The formula for Supplies is 1.5% * Sales for Year
  9. Sum all the expenses for the totals.
  10. The formula for Gross Profit is Gross Income - Total Expenses
  11. The formula for Income Taxes is 50% * Gross Profit
  12. The formula for Net Income is Gross Profit - Income Taxes
  13. Sum the Net Income row and check your total below.. Type the net income total below to check your answer.

    Type your answer without commas:

  14. Make your spreadsheet look professional by using color and borders. Format all the numbers correctly.
  15. Make sure your name is in the title of the Excel document and turn it in to Mr. Miller's Neighborhood.