Complete in Excel

1. Determine the cash in and the cash out each year.

2. What is the net cash flow for each year

You are starting your own business with an equity investment of $500,000. You have prepared prof- forma income statements for the first five years as shown below. 1 2 4 5 1,250,000 Revenue Beg. Inv. Purchases GAS Ending Inv. CGS GP SGA 750,000 750,000 136,986 613,014 636,986 437,500 199,486 49,872 149,615 1,600,000 136,986 960,000 1,096,986 175,342 921,644 678,356 459,375 218,981 54,745 164,236 3 1,700,000 175,342 1,020,000 1,195,342 186,301 1,009,041 690,959 482,344 208,615 52,154 156,461 1,400,000 186,301 840,000 1,026,301 153,425 872,877 527,123 506,461 20,662 5,166 15,497 1,550,000 153,425 930,000 1,083,425 169,863 913,562 636,438 521,655 114,784 28,696 86,088 Taxes Net Profit You plan to distribute 100% of firms profits to yourself each year. Based upon the assumptions listed below will you be able to distribute all the firm's profits? Average Collection Period of 75 days based upon revenue. Average Inventory days of 67 days based upon purchases Average Payable Period of 27 days based upon purchases. 1. Determine the cash in and the cash out each year. 2. What is the net cash flow for each year