Question:

Must be completed in an excel document. The following table summarizes historical income statement performance for Company XYZ. Utilizing the historical information provided and the assumptions outlined below, prepare a budget for the upcoming fiscal year and a projection for the following year. In other words, you should have budgets for two years.

Company XYZ Historical Income Statement
Prior Year 1 Actual	Prior Year 2 Actual
Revenue
Sales	$2,000,000	$2,135,000
Other Operating Revenue	175,000	166,000
Total Revenue	2,175,000	2,301,000
Operating Expenses
Salaries	750,000	772,500
Fringe Benefits	187,500	193,125
Supplies	37,500	38,438
Utilities	55,000	56,650
Insurance	125,000	125,000
Depreciation	200,000	210,000
Other Operating Expenses	275,000	280,500
Total Operating Expenses	1,630,000	1,676,213
Operating Income	545,000	624,788
Interest	185,000	190,000
Earning before Taxes	360,000	434,788
Income Tax (@30%)	108,000	130,436
Net Income	$252,000	$304,351
Operating Margin	25.1%	27.2%
Net Income %	11.6%	13.2%

Additional Historical Information

• Total number of FTEs (full-time equivalent) employees was 14.4
• The Board of Directors approves salary increases each year
• Fringe benefits have not increased over the past few years; however, information suggests that this is not a trend that will continue
• Total net fixed assets for the organization were $2,500,000 in prior year 1 and $2,600,000 in prior year 2

Assumptions for Budget Year 1

• Management has requested an increase in FTEs of 2.5
• Salary increases will be given at 2.5%
• Fringe benefits as a % of total salaries are anticipated to increase 2%
• Inflation for all non-salary expenses is projected to be 2.5%
• Capital purchases for the budget year are projected to be $300,000
• Sales are projected to increase 2.5%
• Other operating revenue is projected to increase $25,000

Assumptions for Budget Year 2

• Management has requested an increase in FTEs of 1.5
• Salary increases will be given at 1.5%
• Fringe benefits as a % of total salaries are anticipated to increase 1%
• Inflation for all non-salary expenses is projected to be 1.5%
• Capital purchases for the budget year are projected to be $150,000
• Sales are projected to increase 3%
• Other operating revenue is projected to increase $25,000