Excel Help Proforma Income Statement for the years 2016, 2017, and 2018
Description	Explanation		2015	2016	2017	2018
Annual Sales Growth				8.0%	9.0%	10.0%
Annual Increase in Cost of Goods Sold				7.0%	6.0%	5.0%
Owner's Salary as a % of Income Before Taxes		15.00%
Employee Hourly Wage		$12.00
Number of Hourly Wage Employees		17
Number of Hours per Year		2000
Sales Revenue in $			$ 4,000,000	$ 4,320,000	$ 4,708,800	$ 5,179,680
Cost of Goods Sold in $			$ 3,000,000	$ 3,210,000	$ 3,402,600	$ 3,572,730
Gross Profit	Sales Revenue Cost of Goods Sold		$ 1,000,000	$ 1,110,000	$ 1,306,200	$ 1,606,950
Operating Expenses
Sales & Marketing	as a % of Sales Revenue	5.50%	$ 220,000	$ 237,600	$ 258,984	$ 284,882
General & Administrative	as a % Sales Revenue	8.75%	$ 350,000	$ 378,000	$ 412,020	$ 453,222
Depreciation	Constant each year	$20,000	$ 20,000	$ 20,000	$ 20,000	$ 20,000
Manager's Salary	Constant each year	$55,000	$ 55,000	$ 55,000	$ 55,000	$ 55,000
Asst Manager's Salary	Constant each year	$40,000	$ 40,000	$ 40,000	$ 40,000	$ 40,000
Wages of All Hourly Employees	Constant each year	$408,000	$ 408,000	$ 408,000	$ 408,000	$ 408,000
Total Operating Expenses			$ 1,093,000	$ 1,138,600	$ 1,194,004	$ 1,261,104
Income Before Taxes	Gross Profit Total Operating Expenses		$ (93,000)	$ (28,600)	$ 112,196	$ 345,846
Owner's Salary	Only if Income Before Taxes > 0		$ -	$ -	$ 16,829	$ 51,877
Taxable Income	Income Before Taxes - Owner's Salary		$ (93,000)	$ (28,600)	$ 95,367	$ 293,969
1. If "Annual Sales Growth" is 8% in each year, what will be the "Owner's Salary" in each year?
Owner's salary in 2016:
Owner's salary in 2017:
Owner's salary in 2018:
2. Reset "Annual Sales Growth" to original values of 8%, 9%, and 10% before you answer this question:
If "Owner's Salary" is increased to 20% and "Employee Hourly Wages" is reduced to $11.00, what will be the "Taxable Income" in each year?
Taxable Income in 2016:
Taxable Income in 2017:
Taxable Income in 2018:
3. Reset "Owner's Salary" to the original value of 15% and "Employee Hourly Wages" to the original value of $12 before you answer this question:
What should be the "Annual Sales Growth" in 2016 if the "Taxable Income" in 2016 should be 0 (zero)?
When that happens, what will be the Taxable Income in 2017 and 2018
Required Annual Sales Growth in 2016:
Taxable Income in 2017:
Taxable In come in 2018:
4. Reset the "Annual Sales Growth" to 8% before you answer the following question:
Insert a Column Chart to compare the Sales Revenue and Cost of Goods Sold in 2015, 2016, 2017, and 2018
Use labels as appropriate
5. Consider the Cost of Goods Sold, Sales & Marketing Expenses, General & Administrative Expenses, Depreciation, Manager's Salary,
Asst Manager's Salary, and Wages of All Hourly Employees. Sow the distribution of these expenses among their total in a Pie Chart for the year 2015.
Use labels as appropriate