Scenario: You are the owner of a small business who wants to project potential profits for the next four years and know that Excel is the perfect tool for the task. Here is the data you have for this past fiscal year:

Income: for this year was from Gross Sales and were $525,000

Business expenses were: Salaries $205,000 Building costs: $45,250 Insurance cost: $28,000 Supply cost: 11% of gross sales All other: $67,000

Gross Income: this is Gross Sales minus the Total Business Expenses.

Taxes: These are 18% of Gross Income

Post Tax Income: This is Gross Income minus taxes

Profit Sharing: Additionally, you have a profit-sharing agreement with your employees that states you will share $10,000 with them whenever your Post Tax Income exceeds $110,000 and $5,000 if it does not. This should change automatically based upon the value of the Post Tax Income.

Net Profit: This is Post Tax Income minus Profit Sharing A general description of your task: 1. Create a spreadsheet [Worksheet1] that shows the net profit for this year and the profit projections for the next 4 years given the following assumptions that will be used in your formulas: sales increase is 5% per year salaries increase is 5.5% per year building costs increase is 2% per year insurance costs increase is 3% per year supply costs remain steady at 11% of Gross Sales per year all other costs increase is 3.5% per year Show the sum of the total profit you expect over the next 4 years. Have the $10,000 bonus cell show with a red background and the $5,000 bonus show a green background. {Note: this shading needs to change automatically if the bonus value changes} Format the worksheet with colors, $ sign with no decimal places, % set to two decimal places, merge and center the title, etc. to give it a professional look. Add a company name of your choice. Rename Worksheet 1 with your name.

2. Copy the spreadsheet with your name and rename the copy to What If. Project what your profits would be if the sales increase is 6% and salaries increase only 4% 3. Copy the spreadsheet with your name and rename the copy to Goal Seek. Determine what the Sales increase per year would be to have a sum of total profit over the next 4 years of $500,000 4. Create a fourth worksheet and name it Charts. Using the data on the your name worksheet, create a pie chart that shows the ratio of expenses for the last year of the 4 projected years. On the chart use appropriate titles and legends, explode the slice for supplies, and show just percentages for each slice. Move the pie chart to the Charts worksheet. 5. Using the data on the your name worksheet, create a second chart. This chart will be a line chart that plots gross sales and net profit for the current and projected years. Insure you have appropriate labels and a title. Move the line chart to the Charts worksheet.