Can someone please help me with the attached problem set. I would really appreciate it if you show all your calculations in excel so i can follow the logic. Thank you

EXHIBIT 1 BUCKEYE COMPUTER, INC. ($ millions) Income Statement Sales Cost of Goods Sold R&D Expense SG&A Expense Operating Income (EBIT) Interest Expense Earnings before Tax Income Taxes Earnings (NI) Dividends to Retained Earnings 2002 1,604 842 158 367 237 8 229 85 144 0 144 2003 1,950 1,020 203 446 281 6 275 108 167 18 149 2004 2,240 1,226 240 479 295 5 290 107 183 28 155 2005 2,456 1,517 278 522 139 13 126 40 86 45 41 2006 3,017 1,739 327 549 402 14 388 141 247 49 198 Balance Sheet Cash Accounts Receivable Inventory Other Current Assets Current Assets Plant, Property & Equipment (net) Other Fixed Assets Total Assets 2002 103 219 282 18 622 410 32 1,064 2003 58 283 348 22 711 482 39 1,232 2004 45 328 420 24 817 536 45 1,398 2005 17 349 500 28 894 610 49 1,553 2006 30 431 584 32 1,077 686 61 1,824 145 30 41 216 55 793 1,064 177 32 31 240 50 942 1,232 203 34 19 256 45 1,097 1,398 233 18 124 375 40 1,138 1,553 285 31 35 351 137 1,336 1,824 97 97 106 120 25 43 32 44 Accounts Payable Accrued Expenses Short Term Debt Current Liabilities Long Term Debt Net Worth Liabilities & Net Worth Notes: CAPEX (PP&E only) Depreciation (PP&E only) Corporate Finance Problem Set 1 Financial Projections Buckeye Computer, Inc. produces high performance image generators for simulation (including PC-based visual system products) and visual workstations for high-end graphics applications. Its financial statements are shown in Exhibit 1. Answer the following questions assuming we are at the beginning of year 2007. 1. Consider the investment and financing decisions implemented between 2002 and 2006. a. How much more (total dollar amount) was tied up in Current Assets at the end of 2006 than at the end of 2002? What were the two principal uses? b. How much of this investment (total dollar amount) was financed by Automatic Sources? c. What was the net increase in working capital over the last four years? d. What was the net amount invested in Fixed Assets since 2002? e. What was the total investment in fixed assets and working capital? 2. Compare the Total Capital (Debt + Equity) in 2002 to 2006. a. By how much did Capital increase? b. How much capital was generated internally? c. How much capital was raised externally? d. How does this compare to the total investment in fixed assets and working capital? 3. Prepare a Summary Statement of Cash Flow for each year 2003-2006 in the following form: Summary Statement of Cash Flow Net Income Adjustments to Reconcile NI and Cash Flow = CF from Operating Activities CF used by Investing Activities = CF before Financing + CF from Financing Activities = Net Increase (Decrease) in Cash Note: You do not have all the details a firm would normally provide in a Statement of Cash Flow, but this summary form gives a clear picture of the firm's activities. Also, it is easier to use PP&E (net) in the \"investing\" section rather than adding back depreciation in the operating section and using CAPEX in the investing section but do whichever you prefer. 4. Suppose Buckeye management sets an annual revenue growth target of 20%. Estimate the amount of new funds needed each year and the total amount needed over the next five years. Project cash needs at 1% of sales, assume the firm pays 20% of earnings as dividends, and project other items based on the 5-year historical, average financial ratios. (Note: Don't be overly concerned about a precise estimate of interest expenses, particularly since you do not know if Buckeye will close the gap by borrowing or by issuing equity. If you do want to assume all debt financing and incorporate interest expense in a circular spreadsheet, use a borrowing cost of 8%.) EXHIBIT 1 BUCKEYE COMPUTER, INC. ($ millions) Income Statement Sales Cost of Goods Sold R&D Expense SG&A Expense Operating Income (EBIT) Interest Expense Earnings before Tax Income Taxes Earnings (NI) Dividends to Retained Earnings 2002 1,604 842 158 367 237 8 229 85 144 0 144 2003 1,950 1,020 203 446 281 6 275 108 167 18 149 2004 2,240 1,226 240 479 295 5 290 107 183 28 155 2005 2,456 1,517 278 522 139 13 126 40 86 45 41 2006 3,017 1,739 327 549 402 14 388 141 247 49 198 Balance Sheet Cash Accounts Receivable Inventory Other Current Assets Current Assets Plant, Property & Equipment (net) Other Fixed Assets Total Assets 2002 103 219 282 18 622 410 32 1,064 2003 58 283 348 22 711 482 39 1,232 2004 45 328 420 24 817 536 45 1,398 2005 17 349 500 28 894 610 49 1,553 2006 30 431 584 32 1,077 686 61 1,824 145 30 41 216 55 793 1,064 177 32 31 240 50 942 1,232 203 34 19 256 45 1,097 1,398 233 18 124 375 40 1,138 1,553 285 31 35 351 137 1,336 1,824 97 25 97 43 106 32 120 44 Accounts Payable Accrued Expenses Short Term Debt Current Liabilities Long Term Debt Net Worth Liabilities & Net Worth Notes: CAPEX (PP&E only) Depreciation (PP&E only) EXHIBIT 1 BUCKEYE COMPUTER, INC. ($ millions) Income Statement Sales Cost of Goods Sold R&D Expense SG&A Expense Operating Income (EBIT) Interest Expense Earnings before Tax Income Taxes Earnings (NI) Dividends to Retained Earnings 2002 1,604 842 158 367 237 8 229 85 144 0 144 2003 1,950 1,020 203 446 281 6 275 108 167 18 149 2004 2,240 1,226 240 479 295 5 290 107 183 28 155 2005 2,456 1,517 278 522 139 13 126 40 86 45 41 2006 3,017 1,739 327 549 402 14 388 141 247 49 198 Balance Sheet Cash Accounts Receivable Inventory Other Current Assets Current Assets Plant, Property & Equipment (net) Other Fixed Assets Total Assets 2002 103 219 282 18 622 410 32 1,064 2003 58 283 348 22 711 482 39 1,232 2004 45 328 420 24 817 536 45 1,398 2005 17 349 500 28 894 610 49 1,553 2006 30 431 584 32 1,077 686 61 1,824 145 30 41 216 55 793 1,064 177 32 31 240 50 942 1,232 203 34 19 256 45 1,097 1,398 233 18 124 375 40 1,138 1,553 285 31 35 351 137 1,336 1,824 97 97 106 120 25 43 32 44 Accounts Payable Accrued Expenses Short Term Debt Current Liabilities Long Term Debt Net Worth Liabilities & Net Worth Notes: CAPEX (PP&E only) Depreciation (PP&E only) A) Increase in CA 1077-622 455 Principal uses: Current Assets are used in the day to day operations and help in the smooth function of the company. It helps in managing the curent liability and hence the company is able to understand the requirement B) Increase in current liability = 351 - 216 Hence 135 out of 455 is financed by automatic sources 135 C) Working capital (2002) Working capital (2006) Increase 406 Current Assets - Current Lia 726 320 D) Fixed Asset in 2002 = PPE + other fixed assets Fixed Asset in 2006 = PPE + other fixed assets Increase 442 410 + 32 747 686 + 61 305 E) Net fixed assets and net working capital = 320+305 625 oth function of the company. understand the requirement in the future. Current Assets - Current Liabiliies Debt Equity Total Capital = Debt + Equity 2002 271 793 1,064 2006 488 1,336 1,824 A) B) C) Increase in capital = 1824 - 1064 Capital Generated internally = Increase in Equity Capital externally = Increase in Liability 760 543 217 D) Increase in fixed assets = 305 which is much less than the total capital raised Fixed assets and working capital increased by 625. This means out of total capital raised most of it ha al capital raised most of it has gone to the fixed assets and working capital EXHIBIT 1 BUCKEYE COMPUTER, INC. ($ millions) Income Statement Sales Cost of Goods Sold R&D Expense SG&A Expense Operating Income (EBIT) Interest Expense Earnings before Tax Income Taxes Earnings (NI) Dividends to Retained Earnings 2002 1,604 842 158 367 237 8 229 85 144 0 144 2003 1,950 1,020 203 446 281 6 275 108 167 18 149 2004 2,240 1,226 240 479 295 5 290 107 183 28 155 2005 2,456 1,517 278 522 139 13 126 40 86 45 41 2006 3,017 1,739 327 549 402 14 388 141 247 49 198 Balance Sheet Cash Accounts Receivable Inventory Other Current Assets Current Assets Plant, Property & Equipment (net) Other Fixed Assets Total Assets 2002 103 219 282 18 622 410 32 1,064 2003 58 283 348 22 711 482 39 1,232 2004 45 328 420 24 817 536 45 1,398 2005 17 349 500 28 894 610 49 1,553 2006 30 431 584 32 1,077 686 61 1,824 145 30 41 216 55 793 1,064 177 32 31 240 50 942 1,232 203 34 19 256 45 1,097 1,398 233 18 124 375 40 1,138 1,553 285 31 35 351 137 1,336 1,824 97 97 106 120 25 43 32 44 Accounts Payable Accrued Expenses Short Term Debt Current Liabilities Long Term Debt Net Worth Liabilities & Net Worth Notes: CAPEX (PP&E only) Depreciation (PP&E only) 2010 167 -18 -64 -66 -4 -72 -7 32 2 -10 -5 2011 183 -28 -45 -72 -2 -54 -6 26 2 -12 -5 2012 86 -45 -21 -80 -4 -74 -4 30 -16 105 -5 2013 247 -49 -82 -84 -4 -76 -12 52 13 -89 97 0 0 0 0 CF -45 -13 -28 13 Increase/Decrease in cash -45 -13 -28 13 Net Income Dividends paid Change in Accounts Receivable Change in inventory Change in Other CA Change in PPE Change in other fixed assets Change in AP Change in Accrues Expenses Change in Short term debt Change in long term debt CF financing Revenue Growth needed Cash needed 20% 1% Revenue 2006 3,017 2007 3620.4 2008 2009 2010 4344.48 5213.376 6256.051 Cash needed(1% of sales) Actual Increase in total assets 30.17 301.7 36.204 362.04 43.4448 52.13376 62.56051 434.448 521.3376 625.6051 Profit(8%) Dividends To retained earnings 247 290 348 417 500 49 57.9264 69.51168 83.41402 100.0968 198 231.7056 278.0467 333.6561 400.3873 Funds needed Total 130.3344 156.4013 187.6815 225.2178 969.8965 2011 7507.261 75.07261 750.7261 This is the total funds needed by the compamy 601 120.1162 480.4647 These are the funds that the company have 270.2614 Total funds needed - Funds with the company EXHIBIT 1 BUCKEYE COMPUTER, INC. ($ millions) Income Statement Sales Cost of Goods Sold R&D Expense SG&A Expense Operating Income (EBIT) Interest Expense Earnings before Tax Income Taxes Earnings (NI) Dividends to Retained Earnings 2002 1,604 842 158 367 237 8 229 85 144 0 144 2003 1,950 1,020 203 446 281 6 275 108 167 18 149 2004 2,240 1,226 240 479 295 5 290 107 183 28 155 2005 2,456 1,517 278 522 139 13 126 40 86 45 41 2006 3,017 1,739 327 549 402 14 388 141 247 49 198 Balance Sheet Cash Accounts Receivable Inventory Other Current Assets Current Assets Plant, Property & Equipment (net) Other Fixed Assets Total Assets 2002 103 219 282 18 622 410 32 1,064 2003 58 283 348 22 711 482 39 1,232 2004 45 328 420 24 817 536 45 1,398 2005 17 349 500 28 894 610 49 1,553 2006 30 431 584 32 1,077 686 61 1,824 145 30 41 216 55 793 1,064 177 32 31 240 50 942 1,232 203 34 19 256 45 1,097 1,398 233 18 124 375 40 1,138 1,553 285 31 35 351 137 1,336 1,824 97 97 106 120 25 43 32 44 Accounts Payable Accrued Expenses Short Term Debt Current Liabilities Long Term Debt Net Worth Liabilities & Net Worth Notes: CAPEX (PP&E only) Depreciation (PP&E only) A) Increase in CA 1077-622 455 Principal uses: Current Assets are used in the day to day operations and help in the smooth function of the company. It helps in managing the curent liability and hence the company is able to understand the requirement B) Increase in current liability = 351 - 216 Hence 135 out of 455 is financed by automatic sources 135 C) Working capital (2002) Working capital (2006) Increase 406 Current Assets - Current Lia 726 320 D) Fixed Asset in 2002 = PPE + other fixed assets Fixed Asset in 2006 = PPE + other fixed assets Increase 442 410 + 32 747 686 + 61 305 E) Net fixed assets and net working capital = 320+305 625 oth function of the company. understand the requirement in the future. Current Assets - Current Liabiliies Debt Equity Total Capital = Debt + Equity 2002 271 793 1,064 2006 488 1,336 1,824 A) B) C) Increase in capital = 1824 - 1064 Capital Generated internally = Increase in Equity Capital externally = Increase in Liability 760 543 217 D) Increase in fixed assets = 305 which is much less than the total capital raised Fixed assets and working capital increased by 625. This means out of total capital raised most of it ha al capital raised most of it has gone to the fixed assets and working capital EXHIBIT 1 BUCKEYE COMPUTER, INC. ($ millions) Income Statement Sales Cost of Goods Sold R&D Expense SG&A Expense Operating Income (EBIT) Interest Expense Earnings before Tax Income Taxes Earnings (NI) Dividends to Retained Earnings 2002 1,604 842 158 367 237 8 229 85 144 0 144 2003 1,950 1,020 203 446 281 6 275 108 167 18 149 2004 2,240 1,226 240 479 295 5 290 107 183 28 155 2005 2,456 1,517 278 522 139 13 126 40 86 45 41 2006 3,017 1,739 327 549 402 14 388 141 247 49 198 Balance Sheet Cash Accounts Receivable Inventory Other Current Assets Current Assets Plant, Property & Equipment (net) Other Fixed Assets Total Assets 2002 103 219 282 18 622 410 32 1,064 2003 58 283 348 22 711 482 39 1,232 2004 45 328 420 24 817 536 45 1,398 2005 17 349 500 28 894 610 49 1,553 2006 30 431 584 32 1,077 686 61 1,824 145 30 41 216 55 793 1,064 177 32 31 240 50 942 1,232 203 34 19 256 45 1,097 1,398 233 18 124 375 40 1,138 1,553 285 31 35 351 137 1,336 1,824 97 97 106 120 25 43 32 44 Accounts Payable Accrued Expenses Short Term Debt Current Liabilities Long Term Debt Net Worth Liabilities & Net Worth Notes: CAPEX (PP&E only) Depreciation (PP&E only) 2010 167 -18 -64 -66 -4 -72 -7 32 2 -10 -5 2011 183 -28 -45 -72 -2 -54 -6 26 2 -12 -5 2012 86 -45 -21 -80 -4 -74 -4 30 -16 105 -5 2013 247 -49 -82 -84 -4 -76 -12 52 13 -89 97 0 0 0 0 CF -45 -13 -28 13 Increase/Decrease in cash -45 -13 -28 13 Net Income Dividends paid Change in Accounts Receivable Change in inventory Change in Other CA Change in PPE Change in other fixed assets Change in AP Change in Accrues Expenses Change in Short term debt Change in long term debt CF financing Revenue Growth needed Cash needed 20% 1% Revenue 2006 3,017 2007 3620.4 2008 2009 2010 4344.48 5213.376 6256.051 Cash needed(1% of sales) Actual Increase in total assets 30.17 301.7 36.204 362.04 43.4448 52.13376 62.56051 434.448 521.3376 625.6051 Profit(8%) Dividends To retained earnings 247 290 348 417 500 49 57.9264 69.51168 83.41402 100.0968 198 231.7056 278.0467 333.6561 400.3873 Funds needed Total 130.3344 156.4013 187.6815 225.2178 969.8965 2011 7507.261 75.07261 750.7261 This is the total funds needed by the compamy 601 120.1162 480.4647 These are the funds that the company have 270.2614 Total funds needed - Funds with the company