Excel Lab

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:

Note: Read the entire lab instructions prior to beginning the lab. You may use this template or build one of your own. The Excel Lessons cover all of the steps required to complete this lab.

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 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:

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%

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 [Use 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.  

Specific details and requirements:

Rename Worksheet1 with your name, Worksheet2 to What-If, Worksheet3 to Goal-Seek, Worksheet4 to Charts and color the tabs

Use this current calendar year as your starting year

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

Ensure the current year and following 4 years appear as labels

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}

Use appropriate titles and legends for the Line and Pie charts. For the Pie Chart %u2018explode%u2019 the slice for supplies and have the slices show just percentages.

Make sure each worksheet will fit on a single printed page with an appropriate heading that appears on each sheet.

Submission Requirements: Upload the Excel file. Name the file lab5