I can not figure out this spreadsheet without hardcoding the cells. No hardcoding is allowed. All if the information is in the excel file. All empty cells are needed to be filled in with appropriate formulas.
ES X V fx ??? B D E F G H Financial Statement Analysis FINANCIALS: ANDREWS BALDWIN CHESTER DIGBY Sales ??? ??? EBIT ??? ??? ??? Profits ??? ??? ??? SG&A % ??? ??? ??? Contribution Margins % ??? ??? 10 Emergency Loans ??? ??? ??? ??? 11 RATIOS: ANDREWS BALDWIN CHESTER DIGBY 12 Return on Assets ??? ??? 13 Return on Equity ??? ??? ??? Capsim Website 14 Return on Sales ??? ??? (Contains helpful 15 Asset Turnover ??? ??? ??? formulas) 16 Leverage ??? ??? ??? 17 Analysis Questions: (Briefly explain in 2-3 sentences) 18 1)Which Company is performing the best and 19 using the above key financials and ratios? -> 20 21 22 23 2)Which Company is performing the worst and 24 using the above key financials and ratios? -> 25 26 27 3)Explain the relationship between the Income 28 Statement, Balance Sheet, and Cash Flows-> 29 30 31 32 Cash Flow Summary: 34 Cash Flows from Operating Activities ANDREWS BALDWIN CHESTER DIGBY 35 Net Income (Loss) S 2,123 (2,356) $ (4,573) $ 5,109 CHECK FIGURES 36 Adjustment for Non Cash Items (Cells should be Green) 37 Depreciation 3,535 $ 2,704 $ 2,760 38 Extraordinary Gain/Losses/Writeoff (156) $ES X V fx ??? B C D E F G H 39 Changes in Current Assets and Liabilities 40 Accounts Payable 52 $ (1,334) $ (979) $ 1.256 41 Inventory 6,838 3,350 ?77 (5,884) 42 Accounts Receivable (806) 609 $ 766 (1,734) Net Cash From Operations: 2,973 S 17,744) $ 3,158 44 Cash Flows from Investing Activities 45 Plant Improvements (Net): $ 7,700) $ $ $ (11,240) 46 Cash Flows from Financing Activities Dividends Paid 48 Sales of Common Stock 3,000 4.000 49 Purchase of Common Stock 50 Cash from Long Term Debt 1,340 $ 3,000 51 Early Retirement of Long Term Debt 52 Retirement of Current Debt (5,168) (11,700) (6,877) (2,200) 53 Cash from Current Debt Borrowing 2,750 13,715 54 Cash From Emergency Loan 777 55 Net Cash From Financing Activities: (2,168) (8,950) $ 15,662 4,800 56 Effect of Exchange Rate (232) $ (66) 2591 5 (419) 57 Net Change in Cash Position: $ 1,641 $ 6,043) $ 777 58 59 Balance Sheet: ANDREWS BALDWIN CHESTER DIGBY 61 Cash $ 1,641 $ 9,779 $ 7,755 62 Accounts Receivable 5,352 3,360 4,707 9,196 63 Inventory 9.419 33,240 15,098 64 Total Current Assets 13,425 777 37,947 $ 32,049 65 Plant and Equipment 53,020 $ 40.560 41,400 $ 66,160 66 Accumulated Deprecation (21,259) (19,808) (19,044) $ (22,971) 67 Total Fixed Assets 31,761 777 22.356 $ 43,189 6.8 Total Assets: 277 277 277 69 Accounts Payable 3,080 2,233 $ 4,688 $ 70 Current Debt 2,750 LA 5,444 21,200 71 Long Term Debt 11,000 17,000 21,840 977 72 Total Liabilities 14,080 21,983 47,728 $ 29,444 73 Common Stock 277 LA 7,808 $ 6,808 LA 15,808 Retained Earnings 21.859 13,519 777 29.987E5 X V fx ??? A B C D E F G H 75 Total Equity 31,106 277 LA 12.575 277 76 Total Liabilities & Owners Equity: $ 45,186 $ 43,310 $ 60.303 277 77 78 Income Statement: 79 ANDREWS BALDWIN CHESTER DIGBY 80 Sales 277 40.879 $ 57,274 81 Variable Cost (Labor, Material, Carry, Ship) 45,081 31,653 $ 45.466 LA 74,330 82 Depreciation 3,535 $ 2,704 277 S 4.411 83 SG&A (R&D/Promo/Sales/Admin) 11,613 777 10,475 $ 19,255 84 Other )Fees/Writeoffs) 150 (89) $ 350 85 EBIT 4,742 (1,077) (1,339) $ 13,538 86 Interest (Short Term/Long Term) 1.474 S 2.469 S 5,681 $ 2,779 87 Taxes 1,102 (1,190) $ (2,447) $ 5,545 BB Profit Sharing 43 LA 105 89 Net Profit 2,123 777 277 777
