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 less 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
• all other costs increase is 3.5% per year
• Show the sum of the total profit you expect over the next 4 years.

2. Using the same spreadsheet [use Worksheet2 for this], project what your profits may be if the sales increase is 6% and salaries increase only 4% 3. Using the initial assumptions of 5% sales and salary increase of 5.5% [Use Worksheet3 for this calculation.], determine what increase in sales would be required to have a cumulative profit over the next 4 years of $450,000 4. Again using the initial assumptions of 5% sales and salary increase of 5.5%, create two charts. The first is a pie chart [create worksheet4 as the location for the chart objects] that shows the ratio of expenses for the last year of the 4 projected years. The second chart will be a line chart that plots gross sales and net profit for the current and projected years.