PART 1 Historical Financial Statements

Prepare an Income Statement and Balance Sheet for 2016 and 2017 for Apple Pie Ltd., using the information provided below. Assume a tax rate of 29%.

Apple Pie Ltd.
	2016	2017
Cost of goods sold	326,078	426,928
Cash	13,170	24,872
Depreciation	50,144	74,319
Interest Expense	11,032	13,791
Operating expenses	39,203	48,138
Accounts payable	48,396	49,741
Net Fixed Assets	225,052	244,627
Sales	495,275	589,307
Accounts Receivable	46,892	66,630
Notes payable	35,158	92,039
Long-term debt	130,273	170,254
Inventory	31,547	81,603
Common Shares	55,623	55,623
Dividends	14,448	15,689

PART 2 Pro Forma Financial Statements

Using the financial statements you prepared in PART 1 and managements assumptions below, prepare a Pro-Forma Income Statement and Pro-Forma Balance Sheet for 2018.

Clearly show what the EFN (external financing needed) will be under this set of 2018 pro forma financial statements.

Using 2017 as the base year, management projects Apple Pies sales will increase 15% in 2018. In addition, management has also assumed the following:

• Cost of goods sold and operating expenses will increase with sales
• Management has assumed interest expense and depreciation will not change
• Tax rate is expected to increase to 34%
• Management wants to increase dividends paid by 4% (so the Div Payout Ratio will increase by 4%).
• Current assets and accounts payable will increase in direct proportion to sales
• Fixed assets are currently operating at 100% capacity (any increase in sales will mean an increase in FA)
• Notes payable and common shares will not change

NOTE: You have the choice to either add another column to your PART 1 financial statements or create an entirely new set of financial statements.

PART 3 Plug Variable

Prepare one final set of pro-forma financial statements using LT Debt as the plug variable to ensure balanced financial statements.

NOTE: Again, you have the choice to either add another column to your prepared financial statements or create an entirely new set of financial statements.

PART 4 Ratio Calculations

Assume Apple Pie Ltd. has 18,800 common shares outstanding, and that the market price at the end of 2015 was $44. Assume that the number of outstanding shares and market price will not change.

Compute all ratios listed below for all 3 years (2016, 2017 and 2018), using the financial statements from PARTS 1 and 2. You MUST use cell references to your financial statements (NO HARD KEYS!).

Current Ratio

Quick ratio

Total debt ratio

Debt-equity ratio

Equity multiplier

Times interest earned

Profit margin

Return on assets

Return on equity

Total asset turnover

Receivables turnover

Inventory turnover

Earnings per share

Price earnings

Book value per share

Market-to-book ratio

Dividends per share

NOTE: This can be below your financial statements (same worksheet) or it can be on a separate worksheet in the same Excel workbook. Presentation / professional appearance is key!

PART 5 Analysis

1. Prove that the DuPont Identity is working. Calculate ROE using this identity for all 3 years and show that it matches to your earlier calculation of ROE.
2. Analyze the projected ROE for this company using all 3 years of data, all components of the DuPont Identity, and any other ratios you feel are relevant. Dont simply quote the calculations you did in part (a) above, USE this data to EXPLAIN what is happening (what these ratios mean).
3. Using all 3 years of data, how do you feel about the short-term and long-term liquidity of this company? Identify the specific ratios you are referring to in your analysis.
4. Apple Pie Ltd. is operating at a 100% fixed asset capacity, meaning any increase in sales would require an increase in fixed assets. If Apple Pie Ltd. was operating at 80% FA capacity in 2017, would the company have needed to increase Fixed Assets in 2018? Why or why not?
5. Management made several assumptions in the 2018 projection. Share your thoughts on any strengths or weaknesses in these assumptions as used to build the pro forma financial statements for 2018.
6. You are a potential investor (shareholder). Would you invest in this company? Refer to specific ratio calculations in your answer here.
7. Briefly, what additional information, if any, would you say would allow for a more complete financial analysis of Apple Pie Ltd.?