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 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 $[$Worksheet$1]$ 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\mathrm{.}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\mathrm{.}5\%$ per year

Show the sum of the total profit you expect over the next $4$ years.

$2.$ Using the same spreadsheet $[$use Worksheet$2$ 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\mathrm{.}5\%[$Use Worksheet$3$ 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\mathrm{.}5\%,$ create two charts. The first is a pie chart $[$create worksheet$4$ 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.