I just need someone to look over my work. It is completed just need someone to double check if I did it correctly. These are
I just need someone to look over my work. It is completed just need someone to double check if I did it correctly.
These are the instructions (below) and I am attaching my completed assignment. If there are any changes to be made please change them. I need this checked within an hour. I am not sure I did this correctly so please double check my work.
1. Financial Data
Using the same company you selected in Module Three, add another two years of financial statement data so that you have three years of annual data to review for historical analysis. In all, your Excel file must include the following:
o Three worksheets of annual balance sheet data
o Three worksheets of annual income statement data
o Three worksheets of annual statement of cash flow data
Important Note: Be sure to label each worksheet in Excel with the appropriate year, as you did in the Module Three assignment.
2. Ratio Calculation
On each data tab, use formulas to calculate the following financial indicators for each year of data:
o Current ratio
o Debt/equity ratio
o Free cash flow
o Earnings per share
o Price/earnings ratio
o Return on equity
o Net profit margin
Thanks in advance.
Vera Bradley Inc. (NMS: VRA) Exchange rate used is that of the Year End reported date As Reported Annual Balance Sheet Report Date Currency Audit Status Consolidated Cash & cash equivalents Accounts receivable, net Raw materials Work in process Finished goods Inventories Income taxes receivable Prepaid expenses & other current assets Deferred income taxes Total current assets Land & land improvements Buildings & building improvements Furniture, fixtures, leasehold improvements & computer equipment Production equipment & vehicles Construction in progress Property, plant & equipment, gross Less: accumulated depreciation & amortization Property, plant & equipment, net Deferred income taxes Other assets Total assets Accounts payable Accrued employment costs Other accrued liabilities Income taxes payable Total current liabilities Deferred income taxes Other long-term liabilities Total liabilities Additional paid-in capital Retained earnings (accumulated deficit) Accumulated other comprehensive income (loss) Treasury stock Total shareholders' equity 01/30/2016 USD Not Qualified Yes 97681000 31294000 151000 113439000 113590000 785000 10292000 253642000 5981000 46145000 127913000 19931000 8034000 208004000 94293000 113711000 11363000 1963000 380679000 24606000 14937000 16924000 10085000 66552000 28872000 95424000 85436000 244009000 -43000 44147000 285255000 Free Cash Earnings Per Price Earning Return on Debt Ratio Flow Share Ratio (PER) Equity (ROE) 3.8111852386 0.3345217437 -14611000 807266.66667 1.0290131307 0.0966082978 Current Ratio 267171000 Net Profit Margin (NPM) -0.068301107 Vera Bradley Inc. (NMS: VRA) Exchange rate used is that of the Year End reported date As Reported Annual Balance Sheet Report Date Currency Audit Status Consolidated Cash & cash equivalents Accounts receivable, net Raw materials Work in process Finished goods Inventories Income taxes receivable Prepaid expenses & other current assets Deferred income taxes Total current assets Land & land improvements Buildings & building improvements Furniture, fixtures, leasehold improvements & computer equipment Production equipment & vehicles Construction in progress Property, plant & equipment, gross Less: accumulated depreciation & amortization Property, plant & equipment, net Deferred income taxes Other assets Total assets Accounts payable Accrued employment costs Other accrued liabilities Income taxes payable Total current liabilities Deferred income taxes Other long-term liabilities Total liabilities Additional paid-in capital Retained earnings (accumulated deficit) Accumulated other comprehensive income (loss) Treasury stock Total shareholders' equity 01/31/2015 USD Not Qualified Yes 112292000 31374000 5542000 470000 92391000 98403000 3208000 9100000 13320000 267697000 5867000 45423000 109087000 20850000 7054000 188281000 79278000 109003000 584000 377284000 32906000 14595000 15548000 63049000 5297000 24467000 92813000 80992000 216451000 -15000 12957000 284471000 Price Return on Net Profit Current Free Cash Earnings Earning Equity Margin (ROE) (NPM) Ratio Debt Ratio Flow Per Share Ratio (PER) 1.107608 0.2847109 53077000 1520840 1.01231936 0.13515965 0.01665652 Vera Bradley Inc. (NMS: VRA) Exchange rate used is that of the Year End reported date As Reported Annual Balance Sheet Report Date Currency Audit Status Consolidated Cash & cash equivalents Accounts receivable, net Raw materials Work in process Finished goods Inventories Income taxes receivable Prepaid expenses & other current assets Deferred income taxes Total current assets Land & land improvements Buildings & building improvements Furniture, fixtures, leasehold improvements & computer equipment Production equipment & vehicles Construction in progress Property, plant & equipment, gross Less: accumulated depreciation & amortization Property, plant & equipment, net Deferred income taxes Other assets Total assets Accounts payable Accrued employment costs Other accrued liabilities Income taxes payable Total current liabilities Deferred income taxes Other long-term liabilities Total liabilities Additional paid-in capital Retained earnings (accumulated deficit) Accumulated other comprehensive income (loss) Treasury stock Total shareholders' equity 02/01/2014 USD Not Qualified Yes 59215000 27718000 10772000 850000 125301000 136923000 9952000 13094000 246902000 3677000 31108000 88859000 20421000 7312000 151377000 66437000 84940000 1085000 332927000 27745000 10586000 20403000 1625000 60359000 4643000 12778000 77780000 78153000 178002000 -1008000 255147000 Current Ratio 4.090558 Price Free Earnings Earning Return Net Profit Ratio Debt Cash Per on Equity Margin (PER) Ratio Flow Share (ROE) (NPM) 0.304844 -3681500 2222840 0.99981 0.230502 0.058563 Vera Bradley Inc. (NMS: VRA) Exchange rate used is that of the Year End reported date As Reported Annual Income Statement Report Date Currency Audit Status Consolidated Net revenues Cost of sales Gross profit Selling, general & administrative expenses Other income Operating income Interest expense, net Income before income taxes - domestic Income before income taxes - foreign Income from continuing operations before income taxes Current federal income tax expense Current foreign income tax expense Current state income tax expense Total current income tax expense Deferred federal income tax expense (benefit) Deferred state income tax expense (benefit) Total deferred income tax expense (benefit) Income tax expense Income from continuing operations Income (loss) from discontinued operations, net of taxes Net income Weighted average shares outstanding - basic Weighted average shares outstanding - diluted Year end shares outstanding Income per share from continuing operations - basic Income (loss) per share from discontinued operations - basic Net income per share - basic Income per share from continuing operations - diluted Income (loss) per share from discontinued operations - diluted Net income per share - diluted Total number of employees Number of common stockholders Foreign currency translation adjustments 01/30/2016 USD Not Qualified Yes 502598000 221409000 281189000 236836000 2369000 46722000 263000 46386000 73000 46459000 19823000 18000 2400000 22241000 -2813000 -527000 -3340000 18901000 27558000 27558000 38795000 38861000 37701171 0.71 0.71 0.71 0.71 2950 30 -28000 Vera Bradley Inc. (NMS: VRA) Exchange rate used is that of the Year End reported date As Reported Annual Income Statement Report Date Currency Audit Status Consolidated Net revenues Cost of sales Gross profit Selling, general & administrative expenses Other income Operating income Interest expense, net Income before income taxes - domestic Income before income taxes - foreign Income from continuing operations before income taxes Current federal income tax expense Current foreign income tax expense Current state income tax expense Total current income tax expense Deferred federal income tax expense (benefit) Deferred state income tax expense (benefit) Total deferred income tax expense (benefit) Income tax expense Income from continuing operations Income (loss) from discontinued operations, net of taxes Net income Weighted average shares outstanding - basic Weighted average shares outstanding - diluted Year end shares outstanding Income per share from continuing operations - basic Income (loss) per share from discontinued operations - basic Net income per share - basic Income per share from continuing operations - diluted Income (loss) per share from discontinued operations - diluted Net income per share - diluted Total number of employees Number of common stockholders Foreign currency translation adjustments 01/31/2015 USD Not Qualified Yes 508990000 239981000 269009000 208675000 3736000 64070000 407000 63445000 218000 63663000 20715000 54000 1631000 22400000 -19000 447000 428000 22828000 40835000 -2386000 38449000 40568000 40632000 40074310 1.01 -0.06 0.95 1 -0.06 0.95 2800 25 - Vera Bradley Inc. (NMS: VRA) Exchange rate used is that of the Year End reported date As Reported Annual Income Statement Report Date Currency Audit Status Consolidated Net revenues Cost of sales Gross profit Selling, general & administrative expenses Other income Operating income Interest expense, net Income before income taxes - domestic Income before income taxes - foreign Income from continuing operations before income taxes Current federal income tax expense Current foreign income tax expense Current state income tax expense Total current income tax expense Deferred federal income tax expense (benefit) Deferred state income tax expense (benefit) Total deferred income tax expense (benefit) Income tax expense Income from continuing operations Income (loss) from discontinued operations, net of taxes Net income Weighted average shares outstanding - basic Weighted average shares outstanding - diluted Year end shares outstanding Income per share from continuing operations - basic Income (loss) per share from discontinued operations - basic Net income per share - basic Income per share from continuing operations - diluted Income (loss) per share from discontinued operations - diluted Net income per share - diluted Total number of employees Number of common stockholders Foreign currency translation adjustments 02/01/2014 USD Not Qualified Yes 536021000 240589000 295432000 205957000 4776000 94251000 382000 94975000 -1106000 93869000 34578000 77000 3643000 38298000 -2540000 -701000 -3241000 35057000 58812000 40599000 40648000 40606731 1.45 1.45 2900 25 - Vera Bradley Inc. (NMS: VRA) Exchange rate used is that of the Year End reported date As Reported Annual Cash Flow Report Date Currency Audit Status Consolidated Net income Depreciation & amortization of property, plant & equipment Depreciation of property, plant & equipment Provision for (recovery of) doubtful accounts Loss (gain) on disposal of property, plant & equipment Stock-based compensation Deferred income taxes Discontinued operations Accounts receivable Inventories Prepaid expenses & other assets Other assets Accounts payable Income taxes Accrued & other liabilities Net cash flows from operating activities Purchases of porperty, plant & equipment Net cash flows from investing activities Payments on financial-institution debt Borrowings on financial-institution debt Shares surrendered for tax withholding Tax withholdings for equity compensation Repurchase of common stock Other financing activities, net Net cash flows from financing activities Effect of exchange rate changes on cash & cash equivalents Net increase (decrease) in cash & cash equivalents Cash & cash equivalents, beginning of period Cash & cash equivalents, end of period Income taxes paid Interest paid 01/30/2016 USD Not Qualified Yes 27558000 22173000 515000 141000 5027000 -3340000 -435000 -15187000 -2571000 -8665000 12508000 5546000 43270000 -26322000 -26322000 -583000 -30870000 -78000 -31531000 -28000 -14611000 112292000 97681000 9302000 259000 Vera Bradley Inc. (NMS: VRA) Exchange rate used is that of the Year End reported date As Reported Annual Cash Flow Report Date Currency Audit Status Consolidated Net income Depreciation & amortization of property, plant & equipment Depreciation of property, plant & equipment Provision for (recovery of) doubtful accounts Loss (gain) on disposal of property, plant & equipment Stock-based compensation Deferred income taxes Discontinued operations Accounts receivable Inventories Prepaid expenses & other assets Other assets Accounts payable Income taxes Accrued & other liabilities Net cash flows from operating activities Purchases of porperty, plant & equipment Net cash flows from investing activities Payments on financial-institution debt Borrowings on financial-institution debt Shares surrendered for tax withholding Tax withholdings for equity compensation Repurchase of common stock Other financing activities, net Net cash flows from financing activities Effect of exchange rate changes on cash & cash equivalents Net increase (decrease) in cash & cash equivalents Cash & cash equivalents, beginning of period Cash & cash equivalents, end of period Income taxes paid Interest paid 01/31/2015 USD Not Qualified Yes 38449000 15216000 -148000 21000 3513000 428000 996000 -2052000 38520000 1353000 2873000 -4833000 9476000 103812000 -37128000 -37128000 -674000 -12841000 -89000 -13604000 -3000 53077000 59215000 112292000 25957000 275000 Vera Bradley Inc. (NMS: VRA) Exchange rate used is that of the Year End reported date As Reported Annual Cash Flow Report Date Currency Audit Status Consolidated Net income Depreciation & amortization of property, plant & equipment Depreciation of property, plant & equipment Provision for (recovery of) doubtful accounts Loss (gain) on disposal of property, plant & equipment Stock-based compensation Deferred income taxes Discontinued operations Accounts receivable Inventories Prepaid expenses & other assets Other assets Accounts payable Income taxes Accrued & other liabilities Net cash flows from operating activities Purchases of porperty, plant & equipment Net cash flows from investing activities Payments on financial-institution debt Borrowings on financial-institution debt Shares surrendered for tax withholding Tax withholdings for equity compensation Repurchase of common stock Other financing activities, net Net cash flows from financing activities Effect of exchange rate changes on cash & cash equivalents Net increase (decrease) in cash & cash equivalents Cash & cash equivalents, beginning of period Cash & cash equivalents, end of period Income taxes paid Interest paid 02/01/2014 USD Not Qualified Yes 58812000 15104000 -153000 29000 2950000 -3241000 7246000 -5682000 1747000 12892000 -5469000 3629000 87864000 -22862000 -22862000 -45000000 30000000 -412000 99000 -15313000 -77000 49612000 9603000 59215000 42287000 161000 Vera Bradley Inc. (NMS: VRA) Issue: Vera Bradley Inc.(NMS:VRA) Time Period: 3 years Frequency: daily Adjusted Prices Historical Pricing Date Open High Low Close Volume Price Move (%) 09/08/2016 09/07/2016 09/06/2016 09/02/2016 09/01/2016 08/31/2016 08/30/2016 16.63 16.7 16.53 16.58 15.96 14.66 14.86 16.99 16.72 16.67 17.06 17.2 15.05 14.97 16.37 16.42 16.01 16.32 15.41 14.34 14.67 16.38 16.68 16.66 16.39 16.65 14.96 14.75 195192 175566 336377 663014 1727985 975151 219993 -1.8 0.12 1.65 -1.56 11.3 1.42 -0.61 Rate of Return over sevenday period 11.050847458 Current ratio=total current assets/total current liablities CR(2014)=246902000/60359000=4.0906 CR(2015)=5867000/5297000=1.1076 CR(2016)=253642000/66552000=3.8112 Debt/equity ratio=total debt/total equity DER(2014)=77780000/255147000=0.3048 DER(2015)=80992000/284471000=0.2847 DER(2016)=95424000/285255000=0.3345 free cash flow =operating cashflow-capital expenditure FCF(2014)=5921500-9603000=49612000 FCF(2015)=112292000-59215000=53077000 FCF(2016)=97681000-112292000=-14611000 Earnings per share(EPS)= (profit after tax-prefered dividends)umber of shares outstanding EPS(2014)=(58812000-3241000)/25=2222840 EPS(2015)=(38449000-428000)/25=1520840 EPS(2016)=(27558000-3340000)/30=807266.67 Price earning ratio(PER)= current market price per share/after tax earnings per share PER(2014)=40599000/40606731=0.9998 PER(2015)=40568000/40074310=1.0123 PER(2016)=38795000/37701171=1.0290 Return on equity=profit after tax/hareholders equity ROE(2014)=58812000/255147000=0.2305 ROE(2015)=38449000/284471000=0.1352 ROE(2016)=27558000/285255000=0.0966 Net profit margin(NPM)= (cost of sales-expenses)et sales NPM(2014)=(240589000-209198000)/536021000=0.0586 NPM(2015)=(239981000-231503000)/508990000=0.0167 NPM(2016)=(221409000-255737000)/502598000=-0.0683