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