# Project #25240 - Lab 4

## Excel Lab - Key Assessment

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.

Income for this year was from Gross sales and were \$525,000

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

Taxes

• Your tax rate was 18% of gross income [that is, the tax was paid on sales less expenses]

Profit Sharing

• Additionally, you have a profit-sharing agreement with your employees that states you will share \$10,000 with them whenever your profit exceeds \$110,000 and \$5,000 if it does not.

1.  Create a spreadsheet [Worksheet1] that shows the net profit [gross income less tax and any profit sharing] for this year and the profit projections for the next 4 years given the following asumptions:

• 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 WhatIF, 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 if the bonus value changes}
• Use appropriate titles and legends for the Line and Pie charts. For the Pie Chart ‘explode’ 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 Key Assessment

 Subject Computer Due By (Pacific Time) 03/16/2014 11:00 pm
TutorRating
pallavi

Chat Now!

out of 1971 reviews
amosmm

Chat Now!

out of 766 reviews
PhyzKyd

Chat Now!

out of 1164 reviews
rajdeep77

Chat Now!

out of 721 reviews
sctys

Chat Now!

out of 1600 reviews

Chat Now!

out of 770 reviews
topnotcher

Chat Now!

out of 766 reviews
XXXIAO

Chat Now!

out of 680 reviews