View the step-by-step solution to:

Assets Cash Marketable Securities Accounts Receivable Inventory Other Assets Total Current Assets Property Plant Equipment Accumulated Depreciation

I need help with analyzing my finance project.  I need help with assumption I've made on my project. I have attached the information and instruction along with the work I've completed already.  I know how to input the data, but I don't understand how to interpret. Can you please help!

Assets X0 X1 X2 X3 X4 X5 Cash $ 100,000 $ 100,000 ### $ 100,000 ### $ 100,000 Marketable SecuriTes $ 1,262,000 $ 4,975,583 $ 8,210,112 $ 59,244,465 $ 144,791,005 $ 308,737,899 Accounts Receivable $ 192,000 $ 228,480 $ 271,891 $ 323,551 $ 385,025 $ 458,180 Inventory $ 420,000 $ 917,280 $ 1,078,721 $ 1,268,576 $ 1,491,846 $ 1,754,410 Other Assets $ 150,000 $ 174,000 $ 201,840 $ 234,134 $ 271,596 $ 315,051 ±otal Current Assets $ 2,124,000 $ 6,395,343 $ 9,862,564 $ 61,170,726 $ 147,039,472 $ 311,365,541 Property $ - $ 1,000,000 Plant $ 2,000,000 Equipment $ 2,000,000 $ 2,000,000 Accumulated DepreciaTon $ (200,000) $ (250,000) $ (500,000) $ (750,000) $ (1,000,000) $ (1,250,000) ±otal Fixed Assets $ 1,800,000 $ 4,750,000 $ (500,000) $ (750,000) $ (1,000,000) $ (1,250,000) ±otal Assets $ 3,924,000 $ 11,145,343 $ 9,362,564 $ 60,420,726 $ 146,039,472 $ 310,115,541 LiabiliTes Accounts Payable $ 154,000 $ 184,800 $ 221,760 $ 266,112 $ 319,334 $ 383,201 OtherCurrent LiabiliTes $ 50,000 $ 55,000 $ 60,500 $ 66,550 $ 73,205 $ 80,526 $ 204,000 $ 239,800 $ 282,260 $ 332,662 $ 392,539 $ 463,727 L± Debt $ 5,910,656 $ 5,816,789 $ 5,718,171 $ 5,614,559 $ 5,505,703 ±otal Non Current LiabliTes $ - $ 5,910,656 $ 5,816,789 $ 5,718,171 $ 5,614,559 $ 5,505,703 ±otal LiabiliTes $ 204,000 $ 6,150,456 $ 6,099,049 $ 6,050,833 $ 6,007,099 $ 5,969,429 Equity Common Stock $ 100,000 $ 1,132,801 $ (3,445,019) $ 49,616,169 $ 73,104,401 $ 196,956,856 Paid in Capital $ 3,400,000 $ 4,432,801 $ (145,019) $ 52,916,169 $ 76,404,401 $ 200,256,856 ±reasury Stock $ - $ (1,032,801) $ 4,577,821 $ (53,061,188) $ (23,488,232) $ (123,852,455) Retained Earnings $ 220,000 $ 462,085 $ 2,275,733 $ 4,898,744 $ 14,011,804 $ 30,784,855 ±otal Equity $ 3,720,000 $ 4,994,886 $ 3,263,515 $ 54,369,894 $ 140,032,373 $ 304,146,111 ±otal LiabiliTes and Equity $ 3,924,000 $ 11,145,343 $ 9,362,564 $ 60,420,726 $ 146,039,472 $ 310,115,541
Background image of page 01
X0 X1 X2 X3 X4 X5 Sales $ 1,690,000 $ 3,210,662 $ 6,099,616 $ 11,588,050 $ 22,014,977 $ 41,824,053 COGS $ 780,000 $ 1,318,590 $ 2,229,076 $ 3,768,254 $ 6,370,233 $ 10,768,879 Interest Revenue $ 68,148 $ 268,681 $ 443,346 $ 3,199,201 $ 7,818,714 Gross Proft $ 910,000 $ 1,960,220 $ 4,139,221 $ 8,263,142 $ 18,843,945 $ 38,873,889 Interest Exp $ 298,897 $ 294,374 $ 289,622 $ 284,629 $ 279,384 DepriciaTon Exp $ 250,000 $ 250,000 $ 250,000 $ 250,000 $ 250,000 Salaries $ 290,000 $ 345,100 $ 410,669 $ 488,696 $ 581,548 $ 692,043 UTliTes $ 300,000 $ 375,000 $ 468,750 $ 585,938 $ 732,422 $ 915,527 Misc $ 50,000 $ 57,500 $ 66,125 $ 76,044 $ 87,450 $ 100,568 ±otal Op EXP $ 640,000 $ 1,326,497 $ 945,544 $ 1,150,677 $ 1,401,421 $ 1,708,138 Net Pre ±ax $ 270,000 $ 633,723 $ 3,193,677 $ 7,112,465 $ 17,442,525 $ 37,165,751 ±ax $ 86,850 $ 215,466 $ 1,066,850 $ 2,399,238 $ 5,892,458 $ 12,579,356 Net Income $ 183,150 $ 486,405 $ 2,395,508 $ 5,156,573 $ 14,749,268 $ 32,405,110 1690000 3210662 6099615.6676 11588049.8453065 22014977.0961132 41824053.4871959 X0 X1 X2 X3 X4 X5 $- $10,000,000 $20,000,000 $30,000,000 $40,000,000 $50,000,000 $60,000,000 $70,000,000 Chart Title COGS Interest Revenue Gross Proft Interest Exp Depricia±on Exp Salaries U±li±es Misc Total Op EXP Net Pre Tax Tax Net Income
Background image of page 02
Show entire document
BUSN 5200 Summer 2015 MS Excel Project Tannous Create an income statement and a balance sheet for years X1 through X5. You may use any accounting principles that seem appropriate, providing that they are GAAP/IFRS. Your goal is to maximize the firm’s common stock price at the end of year 5, by making well-informed accounting and financial decisions. You should think of yourself as the CFO (chief financial officer) of this firm. Balance Sheet for the year ending 12/31/X0 Assets Cash 100,000 Marketable Securities 1,262,000 Accounts Receivable 192,000 Inventory 420,000 Other Assets 150,000 Current Assets 2,124,000 Equipment 2,000,000 Accumulated Depreciation (200,000) Fixed Assets 1,800,000 Total Assets 3,924,000 Liabilities and Equity Accounts Payable 154,000 Other Current Liabilities 50,000 Current Liabilities 204,000 Mortgages 0 Total Liabilities 204,000 Common Stock 100,000 Paid in Capital 3,400,000 Retained Earnings 220,000 Total Equity 3,720,000 Total Liabilities and Equity 3,924,000 Tannous BUSN 5200 – Summer 2015 page 1 of 4
Background image of page 1
A SSUMPTIONS: 1 On 1/1/X1 the company purchased additional land, plant, and equipment totaling $5,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 took out a mortgage to cover part of the cost of the purchases. The interest rate is 5.0%, the payments are semi-annual, and the maturity is 30 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 $2,000,000. The maximum amount you can borrow is $7,500,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) increase by 61% each year. The sales price is $26 in year X0, and the price increases by 18% each year. 4 To make the analysis less complicated, assume the units in ending inventory at 12/31/X0 have a unit cost of $12. In successive years, the number of units in the ending balance of inventory will increase by 12% each year. The unit cost of inventory increases by 5% each year. Hint #1 : you will have to decide which inventory method to use for your analysis. I suggest either LIFO or FIFO. Hint #2 : The unit cost of $12.00 is a simplifying assumption. 5 As the balance sheet shows, the firm needs $100,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 5.4% and the municipal securities have a return of 4.6%, 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 marketable securities in municipal securities during year one, (X1), interest revenue for year X1 would be $58,052 ($1,262,000 * 0.046 = $58,052). 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 income statement more realistic. For instance, at a minimum you will need to add lines for depreciation and interest expense. In the Tannous BUSN 5200 – Summer 2015 page 2 of 4
Background image of page 2
Show entire document
Sign up to view the entire interaction

Top Answer

Hi, Enclosed is the analysis. I have made some... View the full answer

Answer 03052016 (1) Analysis.docx

Analysis/Interpretations
Liquidity
The Current ratio of the company is high from the beginning. It stood at 42.48 during the
year X0. It increased to 3866.67 during the year X5.It shows that the...

Sign up to view the full answer

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
Ask a homework question - tutors are online