{"text_format":"3","workbook":"xls","n_sheets":4,"sheets":["Introduction","Week 4","Week 5","Week 6"],"not_rendered":{"external_links":1,"defined_names":6},"read":"whole","date_system":"1900","formulas":"cached_only"}

{"sheet":"Introduction","index":0,"n_rows":11,"n_cols":5,"rows_kept":11,"merged":[[0,0,0,9],[1,0,1,9],[3,0,3,9]],"flags":["formulas_cached_only"]}
["BRYANT & STRATTON COLLEGE"]
["ACCT220 - FINANCIAL ANALYSIS"]
[{"$nulls":4},"PORTFOLIO PROJECT"]
["Introduction Page"]
[]
["Click Below to Access the spreadsheet you are looking for:"]
["Week 4",null,"This spreadsheet will give you the account balances for Week 4."]
[]
["Week 5",null,"The Ratios to calculate in Week 5."]
[]
["Week 6",null,"The management assumptions to use to complete the Pro Forma income statement in Week 6. "]

{"sheet":"Week 4","index":1,"n_rows":110,"n_cols":8,"rows_kept":110,"merged":[[0,0,0,7],[1,0,1,7],[2,0,2,7],[3,2,3,6],[4,0,4,7],[5,0,5,7],[12,1,12,4],[13,1,13,4],[14,1,14,4],[15,1,15,4],[16,1,16,4],[17,1,17,4],[18,1,18,4],[19,1,19,4],[20,1,20,4],[21,1,21,4],[22,1,22,4],[23,1,23,4],[24,1,24,4],[25,1,25,4],[26,1,26,4],[27,1,27,4],[28,1,28,4],[29,1,29,4],[30,1,30,4],[31,1,31,4],[32,1,32,4],[36,0,36,7],[37,0,37,7],[70,0,70,7],[71,0,71,7],[86,0,86,7],[87,0,87,7]],"flags":["formulas_cached_only"]}
["BRYANT & STRATTON COLLEGE"]
["ACCT220 FINANCIAL ANALYSIS"]
["PORTFOLIO PROJECT"]
[null,null,"Click here to get back to the Introduction Page"]
["WEEK 4"]
["ARRANGE THE FOLLOWING FINANCIAL INFORMATION INTO THE FOLLOWING:"]
[null,"1) Comparative Balance Sheets for 2010 vs. 2011."]
[null,"2) Comparative Income Statements for 2010 vs. 2011."]
[null,"3) Construct the 2011 Statement of Cash Flows using items 1 and 2. "]
[]
[{"$nulls":5},"2010 BALANCE",null,"2011 BALANCE"]
[]
[null,"Selling and Adminstrative Expenses",{"$nulls":3},{"v":650000,"d":"650,000"},null,{"v":600000,"d":"600,000"}]
[null,"Interest Expense",{"$nulls":3},{"v":54000,"d":"54,000"},null,{"v":52000,"d":"52,000"}]
[null,"Accounts Receivable (net)",{"$nulls":3},{"v":350000,"d":"350,000"},null,{"v":425000,"d":"425,000"}]
[null,"Taxes",{"$nulls":3},{"v":91000,"d":"91,000"},null,{"v":58000,"d":"58,000"}]
[null,"Notes Payable",{"$nulls":3},{"v":200000,"d":"200,000"},null,{"v":175000,"d":"175,000"}]
[null,"Capital paid in excess of par",{"$nulls":3},{"v":500000,"d":"500,000"},null,{"v":500000,"d":"500,000"}]
[null,"Cost of Goods Sold",{"$nulls":3},{"v":2300000,"d":"2,300,000"},null,{"v":2250000,"d":"2,250,000"}]
[null,"Cash",{"$nulls":3},{"v":120000,"d":"120,000"},null,{"v":95000,"d":"95,000"}]
[null,"Common Stock, $1 par value",{"$nulls":3},{"v":250000,"d":"250,000"},null,{"v":250000,"d":"250,000"}]
[null,"Sales",{"$nulls":3},{"v":3600000,"d":"3,600,000"},null,{"v":3300000,"d":"3,300,000"}]
[null,"Plant and Equipment",{"$nulls":3},{"v":2500000,"d":"2,500,000"},null,{"v":2800000,"d":"2,800,000"}]
[null,"Accumulated Depreciation",{"$nulls":3},{"v":850000,"d":"850,000"},null,{"v":950000,"d":"950,000"}]
[null,"Depreciation Expense",{"$nulls":3},{"v":90000,"d":"90,000"},null,{"v":100000,"d":"100,000"}]
[null,"Retained Earnings",{"$nulls":3},{"v":665000,"d":"665,000"},null,{"v":905000,"d":"905,000"}]
[null,"Bonds Payable, 2016",{"$nulls":3},{"v":700000,"d":"700,000"},null,{"v":700000,"d":"700,000"}]
[null,"Accounts Payable",{"$nulls":3},{"v":375000,"d":"375,000"},null,{"v":475000,"d":"475,000"}]
[null,"Operating Income",{"$nulls":3},{"v":560000,"d":"560,000"},null,{"v":350000,"d":"350,000"}]
[null,"Accrued Expenses",{"$nulls":3},{"v":80000,"d":"80,000"},null,{"v":60000,"d":"60,000"}]
[null,"Investments",{"$nulls":3},{"v":150000,"d":"150,000"},null,{"v":100000,"d":"100,000"}]
[null,"Inventory",{"$nulls":3},{"v":450000,"d":"450,000"},null,{"v":550000,"d":"550,000"}]
[null,"Prepaid Expenses",{"$nulls":3},{"v":50000,"d":"50,000"},null,{"v":45000,"d":"45,000"}]
[]
[]
[]
["Morgan corporation"]
["Comparative Balance Sheets"]
[{"$nulls":5},"Year-End 2010",null,"Year-End 2011"]
["Assets"]
["Current Assets"]
["     Cash…...............................................................................................................",{"$nulls":3}," ",{"v":120000,"d":"$120,000"},null,{"v":95000,"d":"$95,000"}]
["     Marketable Securities…........................................................................",{"$nulls":3}," "]
["     Accounts Receivable (net)….................................................................",{"$nulls":3}," ",{"v":350000,"d":"350,000.00"},null,{"v":425000,"d":"425,000.00"}]
["     Inventory….......................................................................................",{"$nulls":3}," ",{"v":450000,"d":"450,000.00"},null,{"v":550000,"d":"550,000.00"}]
["     Prepaid Expenses….........................................................",{"$nulls":3}," ",{"v":50000,"d":"50,000.00"},null,{"v":45000,"d":"45,000.00"}]
["          Total Current Assets…..........................................................................",{"$nulls":3}," ",{"v":970000,"d":"$970,000"},null,{"v":1115000,"d":"$1,115,000"}]
["Investments (long-term)….............................................................",{"$nulls":3}," ",{"v":150000,"d":"150,000"},null,{"v":100000,"d":"100,000"}]
["Plants and Equipment…......................................................",{"$nulls":3},{"v":2500000,"d":"$2,500,000"},null,{"v":2800000,"d":"$2,800,000"}]
["     Less: Accumulated Depreciation…..................................",{"$nulls":3},{"v":850000,"d":"850,000.00"},null,{"v":950000,"d":"950,000.00"}]
["Net Plant and Equipment….....................................................",{"$nulls":3}," ",{"v":1650000,"d":"1,650,000"},null,{"v":1850000,"d":"1,850,000"}]
["Total Assets….......................................................................",{"$nulls":3}," ",{"v":2770000,"d":"$2,770,000"},null,{"v":3065000,"d":"$3,065,000"}]
["Liabilities and Stockholders' Equity"]
["Current Liabilities:"]
["     Accounts Payable…........................................................",{"$nulls":3}," ",{"v":375000,"d":"$375,000"},null,{"v":475000,"d":"$475,000"}]
["     Notes Payable…............................................................",{"$nulls":3}," ",{"v":200000,"d":"200,000"},null,{"v":175000,"d":"175,000"}]
["     Accrued Expenses…......................................................",{"$nulls":3}," ",{"v":80000,"d":"80,000"},null,{"v":60000,"d":"60,000"}]
["          Total Current Liabilities…...........................................",{"$nulls":3}," ",{"v":655000,"d":"$655,000"},null,{"v":710000,"d":"$710,000"}]
["Long-term Liabilities:….......................................................",{"$nulls":3}," "]
["     Bonds Payable, 2016…..................................................",{"$nulls":3}," ",{"v":700000,"d":"700,000"},null,{"v":700000,"d":"700,000"}]
["          Total Liabilities….......................................................",{"$nulls":3}," ",{"v":1355000,"d":"$1,355,000"},null,{"v":1410000,"d":"$1,410,000"}]
["Stockholders' Equity:….......................................................",{"$nulls":3}," "]
["     Common Stock, $1 par value….......................................",{"$nulls":3}," ",{"v":250000,"d":"$250,000"},null,{"v":250000,"d":"$250,000"}]
["     Capital paid in excess of par…........................................",{"$nulls":3}," ",{"v":500000,"d":"500,000"},null,{"v":500000,"d":"500,000"}]
["     Retained Earnings….......................................................",{"$nulls":3}," ",{"v":665000,"d":"665,000"},null,{"v":905000,"d":"905,000"}]
["          Total Stockholders' Equity….......................................",{"$nulls":3}," ",{"v":1415000,"d":"$1,415,000"},null,{"v":1655000,"d":"$1,655,000"}]
["Total Liabilities and Stockholders' Equity…............................",{"$nulls":3}," ",{"v":2770000,"d":"$2,770,000"},null,{"v":3065000,"d":"$3,065,000"}]
[]
[]
[]
["Morgan Corporation"]
["Comparative Income Statements"]
[{"$nulls":5},"Year-End 2010",null,"Year-End 2011"]
["1. Sales….....................................................................................................",{"$nulls":4},{"v":3600000,"d":"$3,600,000"},null,{"v":3300000,"d":"$3,300,000"}]
["2. Cost of Goods sold….....................................................................................................",{"$nulls":4},{"v":2300000,"d":"2,300,000"},null,{"v":2250000,"d":"2,250,000"}]
["3.      Gross profit….....................................................................................................",{"$nulls":4},{"v":1300000,"d":"$1,300,000"},null,{"v":1050000,"d":"$1,050,000"}]
["4. Selling and administrative expense….....................................................................................................",{"$nulls":4},{"v":650000,"d":"650,000"},null,{"v":600000,"d":"600,000"}]
["5. Depreciating expense….....................................................................................................",{"$nulls":4},{"v":90000,"d":"90,000"},null,{"v":100000,"d":"100,000"}]
["6.      Operating profit (EBIT)….....................................................................................................",{"$nulls":4},{"v":560000,"d":"$560,000"},null,{"v":350000,"d":"$350,000"}]
["7. Interest expense….....................................................................................................",{"$nulls":4},{"v":54000,"d":"54,000"},null,{"v":52000,"d":"52,000"}]
["8.      Earnings before taxes (EBT)….....................................................................................................",{"$nulls":4},{"v":506000,"d":"$506,000"},null,{"v":298000,"d":"$298,000"}]
["9. Taxes….....................................................................................................",{"$nulls":4},{"v":91000,"d":"91,000"},null,{"v":58000,"d":"58,000"}]
["10. Earnings after taxes (EAT)….....................................................................................................",{"$nulls":4},{"v":415000,"d":"$415,000"},null,{"v":240000,"d":"$240,000"}]
[]
[]
[]
["Morgan Corporation"]
["Statement of Cash Flow"]
[]
["Cash flows from operating activities:"]
["     Net income (earnings after taxes)…................................",{"$nulls":6},{"v":240000,"d":"$240,000"}]
["     Adjustments to determine cash flow from operating activities:"]
["          Add back depreciation….............................................",{"$nulls":5},{"v":100000,"d":"$100,000"}]
["          Increase in accounts receivable…..............................",{"$nulls":5},{"v":75000,"d":"75,000"}]
["          Increase in inventory…..............................................",{"$nulls":5},{"v":100000,"d":"100,000"}]
["          Decrease in prepaid expenses…...............................",{"$nulls":5},{"v":-5000,"d":"(5,000)"}]
["          Increase in accounts payable….................................",{"$nulls":5},{"v":100000,"d":"100,000"}]
["          Decrease in accrued expenses…..............................",{"$nulls":5},{"v":20000,"d":"20,000"}]
["               Total adjustments…..............................................",{"$nulls":6},{"v":390000,"d":"390,000"}]
["               Net cash flows from operating activities…............",{"$nulls":6},{"v":-150000,"d":"$(150,000)"}]
["Cash flows from investing activities:"]
["     Increase in investments (long-term securities)….............",{"$nulls":5},{"v":-50000,"d":"$(50,000)"}]
["     Increase in plant and equipment…..................................",{"$nulls":5},{"v":-200000,"d":"(200,000)"}]
["          Net cash flows from investing activities…..................",{"$nulls":6},{"v":-250000,"d":"$(250,000)"}]
["Cash flows from financing activities:"]
["     Increase in bonds payable…...........................................",{"$nulls":5},0]
["     Preferred stock dividends paid….....................................",{"$nulls":5},0]
["     Common stock dividends paid…......................................",{"$nulls":5},0]
["          Net cash flows from financing activities…..................",{"$nulls":6},0]
["Net increase (decrease) in cash flows…..............................",{"$nulls":6},{"v":-160000,"d":"$(160,000)"}]

{"sheet":"Week 5","index":2,"n_rows":33,"n_cols":4,"rows_kept":33,"merged":[[0,0,0,8],[1,0,1,8],[2,0,2,8],[3,0,3,8],[4,0,4,8],[5,0,5,8],[27,0,31,0],[32,0,51,8]],"flags":["formulas_cached_only"]}
["BRYANT & STRATTON COLLEGE"]
["ACCT220 FINANCIAL ANALYSIS"]
["PORTFOLIO PROJECT"]
["Click here to get back to the Introduction Page"]
["WEEK 5"]
["Calculate the 5 Financial Ratios below for BOTH 2010 AND 2011. "]
[null,"1)","Profit Margin"]
[null,null,2010,{"v":0.1153,"d":"11.53%"}]
[null,null,2011,{"v":0.0727,"d":"7.27%"}]
[]
[null,"2)","Current Ratio"]
[null,null,2010,1.48]
[null,null,2011,1.57]
[]
[null,"3)","Receivables Turnover"]
[null,null,2010,10.29]
[null,null,2011,7.76]
[]
[null,"4)","Inventory Turnover"]
[null,null,2010,8]
[null,null,2011,6]
[]
[null,"5)","Return on Assets"]
[null,null,2010,{"v":0.1498,"d":"14.98%"}]
[null,null,2011,{"v":0.0783,"d":"7.83%"}]
[]
[]
["Word Report on Financial Statements and Ratios","Write a minimum of a 300 word report on your findings. Discuss the trends you see on the"]
[null,"Financial Statements. What do you see happening with Sales, Profits, Inventory, "]
[null,"Cash, and Receivables from 2010 to 2011. When you compare the 2010 vs. 2011"]
[null,"financial ratios above, which ones are trending up, and which ones are trending down?"]
[null,"You may be creative with this report. "]
["            Overall, I would say this company is in serious trouble.  Every single ratio can indicate something negative.  The profit margin has decreased, indicating that the company is making less money from the goods sold.  This may be due to higher cost of manufacturing.  The current ratio has increased slightly.  This is generally good, as it means the company is producing closer to the same amount as it is selling.  The receivables turnover is decreasing greatly.  This is very bad as it means we are seeing more and more clients not paying or paying late.  The company may need to overhaul its collections.  Inventory turnover has decreased.  This means we are holding onto products longer, taking more time to sell them.  This is generally bad and can mean our productions is not aligned with our sales forecast.  The return on assets has decreased to almost half of its previous standing.  This is generally bad, as it indicates our invested assets are falling flat and showing no revenue growth.\n\n\tThis would almost certainly spell doom for a company unless there are extenuating circumstances that could be leveraged to keep the faith of the financial markets long enough to recover.  With ratios trending this poorly, I would advise renovation across the board.  Look at cutting costs in manufacturing to increase the profit margin.  Researching new suppliers would be helpful too.  The current ratio on its own would be good, but we can definitely do better.  Trying to align the sales forecast to production rate would help this as well as improve the inventory turnover rate.  I’ve previously touched on what can be done to fix receivables turnover, but another option worth exploring could be restricting the credit policy.  Finally, while a return on assets over 5% is generally okay, the fact that it fell will alarm investors.  Resolving the previous issues should show this begin to go up as well."]

{"sheet":"Week 6","index":3,"n_rows":32,"n_cols":2,"rows_kept":32,"merged":[[0,0,0,9],[1,0,1,9],[2,0,2,9],[3,0,3,9],[4,0,4,9],[5,0,20,0],[23,0,27,0],[29,0,31,0]],"flags":["formulas_cached_only"]}
["BRYANT & STRATTON COLLEGE"]
["ACCT220 FINANCIAL ANALYSIS"]
["PORTFOLIO PROJECT"]
["Click here to get back to the Introduction Page"]
["WEEK 6"]
["Pro Forma Income Statement","You will need your final 2011 Income Statement from Week 4 for this assignment."]
[null,"Use your 2011 Income Statement as your base, and then make the adjustments listed below in "]
[null,"order to construct a 2012 Pro Forma Income Statement.  "]
[]
[null,"Pro Forma Income Statement for 2012 includes the following Management assumptions."]
[]
[null,"1) Sales for existing business will remain at $3,300,000, but add $500,000 for new clothing line."]
[]
[null,"2) Cost of Goods Sold for existing business will remain at $2,250,000. Add $300,000"]
[null,"to Cost of Goods Sold for new clothing line."]
[]
[null,"3) Selling and Administrative Expenses will increase by 15% with new line. "]
[]
[null,"4) Assume Interest will be $50,000 in 2012. "]
[]
[null,"5) Assume Taxes will be 30% of Earnings Before Taxes for 2012."]
[]
[]
["Word Report on Pro Forma","Write a minimum of a 300 word report on your findings. Discuss the trends you see on the 2011 vs. 2012"]
[null,"Income Statements. How much does profits increase as a result of adding the assumed clothing "]
[null,"line sales. Does it all seem worth it? What risks do you see with the company if sales that "]
[null,"management is assuming here are not achieved? "]
[null,"You may be creative with this report. "]
[]
["Word Reflection on Portfolio Project","Write a minimum of a 300 word Reflection telling me what you learned from this 3 week assignment. "]
[null,"Are financial statements easier to create and interpret as a result of this assignment? "]
[null,"Can you better see how financial ratios can illustrate the financial condition of the company?"]