I have this simple small part, I couldn't do the validation, but I did the sales and beginning cash balance for the common size of cash flow:

Extra credit:

Creat Common size cash flow statements that toggles between dividing all cash flow account by sales or dividing all cash flow accoutns by beginning cash

1. Sales

2. beginning cash balance

to get credit you need ti use a function Choose and a data validation

Blue Sky, Inc. is a small manufacturer of a specialty parashuting equipment. Using the data presented below for Blue Sky, Inc.: 2014 Sales 7550000 Cost of Goods 5750000 Depreciation 120000 Selling and G&A Expenses 820000 Fixed Expenses 200000 Lease Expense 150000 Interest Expense 152000 Tax Rate 40% Shares Outstanding 100000 Dividend per share 0.48 Cash 108000 Marketable Securities 150000 Accounts Receivable 450000 Inventory 1250000 Prepaid Expenses 120000 Plant & Equipment 5350000 Accumulated Depreciation ? Long Term Investments 450000 Accounts Payable 420000 Notes Payable 150000 Accrued Expenses 150000 Other Current Liabilities 200000 Long-term Debt 1900000 Common Stock 100000 Additional Paid-in-Capital 3281200 Retained Earnings ? 2013 6150000 4550000 100000 730000 200000 150000 121000 40% 80000 0.45 50000 100000 350000 850000 40000 4800000 890000 360000 380000 100000 100000 180000 1520000 80000 2800000 500000 Note: All numbers are in US Dollars, except for the tax rate a. Construct Blue Sky's income statement and balance sheet using formulas wherever possible. Each statement should be on a Improve the readability of the data by selecting the format explained on page 51, so that Excel will display the numbers as if Make the appropriate note on the heading of each financial statement. b. On another worksheet, create a statement of cash flows. All formulas should be linked directly to the source on previous wo c. Using Excel's outlining feature, create an outline on the cash-flow statement and balance sheet such that, when collapsed, th subtotals for each section. d. Construct common-size income statement and balance sheet for both fiscal years. These statements should be created on a separate worksheets with all formulas linked directly to the income statement and Extra credit Creat Common size cash flow statements that toggles between dividing all cash flow account by sales or dividing all cash flow a 1. Sales 2. beginning cash balance to get credit you need to use a function Choose and a data validation h statement should be on a separate worksheet. l display the numbers as if they had been divided by 1,000. the source on previous worksheets. ch that, when collapsed, the statements show only the the income statement and balance sheet. es or dividing all cash flow accoutns by beginning cash Blue Sky, Inc. Income Statement In $Thousands Fiscal Year Sales Cost of Goods Gross Profit Selling and G&A Expenses Fixed Expenses Lease Expense EBITDA Depreciation EBIT Interest Expense Pre-tax Income Income Taxes Net Income Notes: Tax Rate Shares Outstanding Dividend per share Total Dividends Earnings per share 2014 7,550.0 5,750.0 1,800.0 820.0 200.0 150.0 630.0 120.0 510.0 152.0 358.0 143.2 214.8 2013 6,150.0 4,550.0 1,600.0 730.0 200.0 150.0 520.0 100.0 420.0 121.0 299.0 119.6 179.4 40% 100,000 0.48 48,000 2.15 40% 80,000 0.45 36,000 2.24 Operating Income Before Depreciation Operating Income fore Depreciation Blue Sky, Inc. Balance Sheet In $Thousands Assets Fiscal Year Cash Marketable Securities Accounts Receivable Inventory Prepaid Expenses Current Assets Gross Plant & Equipment Accumulated Depreciation Net Plant & Equipment Long Term Investments Total Assets 2014 108.0 150.0 450.0 1,250.0 120.0 2,078.0 5,350.0 1,010.0 4,340.0 450.0 6,868.0 2013 50.0 100.0 350.0 850.0 40.0 1,390.0 4,800.0