Question:Need some help on this assignment. Not quite sure how to get a cash flow statement derived from what I have here. Additionally, not sure what I need to do for the fixed assets portion.

Thanks in advance for any assistance!

• Download Attachments:

Acct 515 Financial Statement Project Spring 2017 Create an income statement, a balance sheet, and a cash flow statement for years X1 through X5. You may use any accounting principals that seem appropriate, providing that they are GAAP. Your goal is to maximize the firm's common stock price at the end of year 5, by making savvy accounting and financial decisions. You should think of yourself as the CFO (chief financial officer) of this firm. REQUIRED Part One (85% of grade): In this assignment, assume that you are creating actual statements for years X1-X5. You will be graded on how well you do the accounting, the reasonableness of your assumptions and the appearance and \"presentation\" of your financial statements. Part Two (15%) As of the end of year five, do a five-year financial analysis of your company. At a minimum level you should calculate 8 or so basic financial ratios. Write a one-page financial analysis that provides a prospective investor with a good overview of the most recent financial results of the company. You will be graded on how well your analysis fits the information from your company. Hint: You will probably have to do financial analyses of other companies in courses like Finance and Strategy. This part of the project is an opportunity to create a financial forecasting/analysis framework that you can transport to other classes. Strange as it might seem, this framework might even be useful in \"real life\". Bonus To make the project more realistic I have am asking you to think like a CFO. You will not be graded on the quality of your financial decisions. Your grade will be based on how well you do the accounting. To make the project more interesting I am adding a $20 prize for the individual who maximizes stock price in year X5. The primary criterion is the market value of the stock at the end of year 5, which in this case is directly related to GAAP EPS and pertains to your GAAP statements. (HINT: the price per share depends on how many shares are outstanding. You need to think hard about how much stock you sell in the first year of your analysis.) Acct 515 Spring 2017 Balance Sheet for the year ending 12/31/16 Year 0 Assets Cash Marketable Securities Accounts Receivable Inventory Other Assets Current Assets Equipment Accumulated Depreciation Fixed Assets Total Assets 200,000 2,100,000 600,000 500,000 300,000 3,700,000 3,000,000 1,000,000 2,000,000 5,700,000 Liabilities and Equity Accounts Payable Other Current Liabilities Current Liabilities Mortgages Total Liabilities Common Stock Paid in Capital Retained Earnings Total Equity Total Liabilities and Equity 1,000,000 100,000 1,100,000 1,100,000 200,000 3,400,000 1,000,000 4,600,000 5,700,000 1 2 3 4 5 200,000 4,844,671 1,050,000 546,000 525,000 7,165,671 12,000,000 1,250,000 10,750,000 17,915,671 200,000 200,000 200,000 200,000 1,837,500 3,215,625 5,627,344 9,847,852 918,750 2,956,250 1,607,813 5,023,438 2,813,672 8,641,016 4,923,926 14,971,777 1,500,000 (1,500,000) 1,750,000 (1,750,000) 2,000,000 (2,000,000) 2,250,000 (2,250,000) 1,500,000 150,000 1,650,000 8,686,589 10,336,589 2,250,000 225,000 2,475,000 8,361,742 10,836,742 3,375,000 337,500 3,712,500 8,025,042 11,737,542 5,062,500 506,250 5,568,750 7,676,056 13,244,806 7,593,750 759,375 8,353,125 7,314,336 15,667,461 3,400,000 4,179,082 7,579,082 17,915,671 3,400,000 17,077,915 20,477,915 31,314,657 3,400,000 44,871,538 48,271,538 60,009,081 3,400,000 106,422,473 109,822,473 123,067,279 3,400,000 245,417,067 248,817,067 264,484,529 75% 50% Amortize a Loan Annual Borrow Interest Life Qtrly 9,000,000 3.60% 20 $158,302.83 Pmt 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 0.9% 80 interest $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 Reduce Loan 81,000.0 80,304.3 79,602.3 78,894.0 78,179.3 77,458.2 76,730.6 75,996.4 75,255.7 74,508.3 73,754.1 72,993.2 72,225.4 71,450.7 70,669.0 69,880.3 69,084.5 68,281.5 67,471.4 66,653.9 65,829.0 64,996.8 64,157.0 63,309.7 62,454.8 61,592.1 60,721.7 59,843.5 58,957.4 58,063.3 57,161.1 56,250.8 55,332.4 54,405.6 53,470.5 52,527.1 51,575.1 50,614.5 49,645.3 $77,302.83 $77,998.55 $78,700.54 $79,408.84 $80,123.52 $80,844.63 $81,572.24 $82,306.39 $83,047.14 $83,794.57 $84,548.72 $85,309.66 $86,077.44 $86,852.14 $87,633.81 $88,422.51 $89,218.32 $90,021.28 $90,831.47 $91,648.96 $92,473.80 $93,306.06 $94,145.82 $94,993.13 $95,848.07 $96,710.70 $97,581.10 $98,459.33 $99,345.46 $100,239.57 $101,141.72 $102,052.00 $102,970.47 $103,897.20 $104,832.28 $105,775.77 $106,727.75 $107,688.30 $108,657.49 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 $158,302.83 48,667.4 47,680.7 46,685.1 45,680.5 44,666.9 43,644.2 42,612.3 41,571.1 40,520.5 39,460.4 38,390.9 37,311.7 36,222.7 35,124.0 34,015.4 32,896.8 31,768.2 30,629.4 29,480.3 28,320.9 27,151.0 25,970.7 24,779.7 23,578.0 22,365.5 21,142.0 19,907.6 18,662.0 17,405.3 16,137.2 14,857.7 13,566.7 12,264.1 10,949.7 9,623.5 8,285.4 6,935.3 5,572.9 4,198.4 2,811.4 1,412.0 $109,635.41 $110,622.13 $111,617.73 $112,622.29 $113,635.89 $114,658.61 $115,690.54 $116,731.75 $117,782.34 $118,842.38 $119,911.96 $120,991.17 $122,080.09 $123,178.81 $124,287.42 $125,406.01 $126,534.66 $127,673.47 $128,822.54 $129,981.94 $131,151.78 $132,332.14 $133,523.13 $134,724.84 $135,937.36 $137,160.80 $138,395.25 $139,640.80 $140,897.57 $142,165.65 $143,445.14 $144,736.15 $146,038.77 $147,353.12 $148,679.30 $150,017.41 $151,367.57 $152,729.88 $154,104.45 $155,491.39 $156,890.81 Loan 9,000,000 8,922,697.17 8,844,698.62 8,765,998.09 8,686,589.24 8,606,465.72 8,525,621.09 8,444,048.85 8,361,742.47 8,278,695.32 8,194,900.76 8,110,352.04 8,025,042.38 7,938,964.94 7,852,112.80 7,764,478.99 7,676,056.47 7,586,838.15 7,496,816.87 7,405,985.40 7,314,336.44 7,221,862.64 7,128,556.58 7,034,410.77 6,939,417.64 6,843,569.57 6,746,858.87 6,649,277.78 6,550,818.45 6,451,472.99 6,351,233.42 6,250,091.70 6,148,039.70 6,045,069.23 5,941,172.03 5,836,339.75 5,730,563.99 5,623,836.24 5,516,147.94 5,407,490.44 319,800.5 Interst 316,979.8 314,133.8 311,262.1 308,364.5 305,440.9 302,491.0 299,514.5 296,511.2 293,480.9 290,423.3 287,338.2 284,225.4 281,084.5 277,915.4 274,717.7 271,491.3 268,235.8 264,951.0 261,636.7 258,292.5 254,918.2 251,513.6 5,297,855.03 5,187,232.90 5,075,615.17 4,962,992.88 4,849,356.99 4,734,698.38 4,619,007.84 4,502,276.09 4,384,493.75 4,265,651.37 4,145,739.40 4,024,748.23 3,902,668.14 3,779,489.33 3,655,201.91 3,529,795.90 3,403,261.24 3,275,587.76 3,146,765.23 3,016,783.29 2,885,631.51 2,753,299.37 2,619,776.24 2,485,051.40 2,349,114.04 2,211,953.24 2,073,557.99 1,933,917.19 1,793,019.62 1,650,853.97 1,507,408.83 1,362,672.69 1,216,633.91 1,069,280.79 920,601.50 770,584.08 619,216.52 466,486.64 312,382.19 156,890.81 (0.00) Acct 515 Spring 2017 Abridged Income Statement for the year ended 12/31/16 Year 0 Sales Cost of Goods Sold Gross Profit Operating expenses Wages Interest Expense Depreciation Exp Misc Total Op Exp Int Rev Net income before taxes Taxes Net Income 5,000,000 2,500,000 2,500,000 1,000,000 1,500,000 500,000 1,000,000 1 2 3 10,030,000 20,711,950 43,992,182 5,630,000 20,711,950 43,992,182 400,000 480,000 576,000 319,801 308,365 296,511 4,400,000 250,000 250,000 250,000 100,000 105,000 110,250 1,069,801 1,143,365 1,232,761 77,700 179,253 4,560,199 19,568,585 42,759,421 1,458,818 3,179,082 6,849,005 12,898,833 14,965,797 27,793,623 4 5 96,034,933 215,310,319 96,034,933 215,310,319 691,200 829,440 284,225 271,491 250,000 250,000 115,763 121,551 1,341,188 1,472,482 94,693,745 213,837,838 33,142,811 61,550,934 74,843,243 138,994,594 20% wage growth Cash Flow From Operating Activities Net Income Items not affecting cash Gain on Sale of Investment Depreciation Expense Increase in AP Increase in Tax Increase in AR Year 2017 4560199 ? Amount Rate Plus $ 50,000.00 0.15 0 $ 75,000.00 0.25 7500 $ 100,000.00 0.34 13750 $ 335,000.00 0.39 22250 $ 10,000,000.00 0.34 113900 $ 15,000,000.00 0.35 3400000 $ 18,333,333.00 0.38 5150000 $ 999,999,999.00 0.35 NI Tax Y1 Y2 $ 4,560,199.46 $ 19,568,585.48 $ 1,458,817.81 $ 6,849,004.92 Y3 Y4 $ 42,759,420.58 $ 94,693,744.98 $ 213,837,837.60 $ 14,965,797.20 $ 33,142,810.74 $ 74,843,243.16 Acct 515 Spring 2017 ASSUMPTIONS: 1 On 1/1/X1 the company will purchase additional land, plant, and equipment totaling $9,000,000. You must decide how much is allocated to each category and how you will depreciate each category. Remember that each category has different depreciation rules. Be sure to show the \"Historical Cost\" for each category (property, plant and equipment) and the associated amount of accumulated depreciation on the balance sheet. You must also decide how the original equipment was depreciated and its asset life. 2 On 1/1/X1 the company will take out a mortgage to cover part of the cost of the purchases. The interest rate is 3.6%, the payments are quarterly and the maturity is 20 years. You must decide how much of a loan you think you will need, given your current financial structure. You also have the option of selling common stock to raise some of the money to pay for the asset expansion. You must take out a loan for at least $3,000,000. The maximum amount you can borrow is $12,000,000. You must decide the optimum amount of debt and equity. Please include a loan amortization schedule of your particular loan that shows the interest expense for each year. 3 Sales (in units) increases by 70% in the first year and will grow by 5% additional each year. So, there is a 75% (70% + 5%) growth in sales in year 2, 80% (70% + 5% + 5%) in year 3 ... These are the annual compounded rates of growth for the sales units. The sales price is $50 in year X0, and the price increases by 18% each year. To make the analysis less complicated, assume all units in ending inventory at 12/31/X0, units in CGS, have a unit cost of $25. In successive years, the number of units in the ending balance of inventory will increase by 5% each year. The unit cost of inventory increases by 4% each year. Hint: you will have to decide which inventory method to use for your analysis. I suggest either LIFO or FIFO. 4 5 As the balance sheet shows, the firm needs $200,000 in the cash account for transactions purposes. The firm takes any extra cash and invests the cash in marketable securities. You have an option of investing in corporate securities or municipal securities. The corporate securities have a return of 4.2% and the municipal securities have a return of 3.7%, but the municipal securities are federal income tax-free. Interest revenue is calculated by taking the previous year's ending balance in marketable securities and multiplying by the rate of return from the type of securities selected. For instance, if you decide to invest the in municipal securities during year one, (X1), and had $1,000,000 in marketable securities at the end of year zero, and an interest rate of 4%, interest revenue for year X1 would be $40,000 ($1,000,000 * 0.04 = $40,000). This calculation of interest revenue is simplistic and obviously unrealistic, but will keep you from having a circular logic problem in your Excel modeling. 6 The abridged income statement in year zero (X0) needs some explanation. \"Operating expenses\" includes many accounts, such as wage expense, lease expense, etc. You will need to add a few line items to make the (your) income statement more realistic. For instance, at a minimum you will need to add lines for miscellaneous expense, wage expense, depreciation expense, and interest expense. In the base year, all expenses are lumped together as \"operating expenses\