Case study analysis. Read the Case below regarding ABC Company. Use the information contained in the Case to fill out the Excel Document found in Moodle. (ABCCompAssignment1.xlsx) Work in your Group to follow the steps below for Assignment 1.

Steps:

1. Construct Financial Statements using Excel.

Your groups Job is to fill out the values for the years 2014 (current) and the 2015 (projected) in the Statement of Income and Statement of Financial Position (fill all the green cells). If no information is given about a particular value assume it is the same as last years. Pay close attention to the potential sources of financing, adjust the Statement of Income and Statement of Financial Position accordingly in excel. When you are finished with the Excel Document save it as Group#ABCCorpA1. All Values shown in Excel are in thousands of dollars

THE CASE: It is currently Q4 in 2014 and ABC corporation has placed your team in charge of preparing the companys Income Statement and Balance Sheet (Statement of Financial Position) for 2014, as well as the projected statements for 2015. You are told that sale revenue has increased 6% from the 2013 values. ABC Company also plans to expand their inventory to meet growing demand and they forecast sales revenue to increase by 35% in 2015 (from current values). 40,045.00$ of additional purchases were made in 2014 (Paid for with Retained Earnings), with another 230,545.00 planned to be purchased in the future to expand inventory in 2015. Along with the increase in inventory, Freight expenses are expected to double. ABC has been preapproved a short-term loan in the amount of $100,000 today to be repaid in full at the end of 2015 to finance this expansion, the rest will be paid for using the companies Retained Earnings. The total financing cost of the loan will be $9,500. Labour cost is expected to be the same as last year and are expected to increase by 15% in 2015. ABC is also planning to hire 2 new employees on salary, one for Distribution and one as Administrative staff in 2015, they will be paid $40,000/year each. Commissions are expected to increase by $30,000 in 2015.

Plant Property and equipment are also the same as last year (2013), however, in the coming year (2015) the company is faced with a choice to buy or rent a warehouse to house the additional inventory. The building may be purchased outright for 350,000$ or leased on a one-year term for $90,000 spilt into 12 equal monthly payments.

Statement of Income
	2011	2012	2013	2014(Current)	2015(Projected)
Revenue	1,083	1,058	1,564
Cost of sales
Purchases	369	355	630
Freight in	30	29	44
Labour	380	366	529
Depreciation/amortization	3	4	4
Other charges	5	0	0
Total cost of sales	787	754	1,207	________	________
Gross profit	296	304	357
Other income	0	0	0
Distribution costs
Salaries	34	37	41
Commissions	33	32	47
Travelling	12	11	16
Advertising	7	9	10
Depreciation/amortization	2	2	3
Other charges	10	0	0
Total distribution costs	98	91	117
Administrative expenses
Salaries	94	102	107
Leasing	20	20	26
Depreciation/amortization	13	9	24
Other charges	20	11	16
Total administrative expenses	147	142	173
Financing costs	3	7	14
Total expenses less other income	248	240	304
Profit before taxes	48	64	53
Income taxe expense	10	18	12
Profit for the year	38	46	41