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