Please check the questions in the document then get back to me.

profileharsh.vijan
FinancialModelingExcel.xlsx
Home>Business & Finance homework help>Accounting homework help>Please check the questions in the document then get back to me.

Final Exam Instructions

FIN 380 Take-Home Final Examination- Fall 2020 The following pages contain the take home final exam for Finance 380, sections A and B. The exam is due no later than Monday, November 23rd at 9:00 PM. Late exams will lose 1 point for every minute late. By electronically submitting the final exam, you are attesting to the following. Please READ carefully. 1. I understand that this is an examination and attest that the work presented is my own; that it is done without outside assistance of any kind (except that provided by the instructor). I understand that the penalty for cheating or collusion of any kind is a grade of F for the course. 2. Spreadsheet solutions will be appropriately formatted and developed in a logical, readable, coherent fashion. Confusing or unformatted spreadsheets may result in a reduction of points. 3. I understand that the file tabs are in their original order and understand that out-of-order problems will not be graded (Note: it is YOUR responsibility to insure that what you pass in ordered correctly) 4. I understand that the due date and time for the exam is Monday, November 23rd at 9:00 PM. I understand that the exam must be submitted electronically by email to me at [email protected]. I understand that exams passed in after that time will be considered late and will incur a minimum penalty of 1 point lost per minute late. 5. The file name should contain both your last name, first name, section and the word “Final” in that order. For example, if you’re in the 10:20 AM section (Section A), your file name should be LastName.FirstName.A.Final.xlsx 6. Make sure you pass in the FINAL exam, not a homework assignment or your midterm. I don’t have time to hunt you down. 7. If you submit your exam early, I’ll try to open the file and make sure I have the right exam and that I can view it. I’ll let you know if I have problems. It is unlikely that I will be able to do so for any exam submitted after 5:00 P.M. on the day (Sunday) before the due date. 8. The exam is being made available to you on or before November 16 and is due November 23rd. That means you have ample time to ask questions if you are unclear about the instructions or what you are expected to provide as an answer. If you don’t understand a question, ask BEFORE the exam is due for clarification. Misunderstood questions leading to wrong answers will count against you. 9. Good luck! It’s almost over.

Problems

DECIDE

EQUATION

NEWELL

Date Price Dividend S&P500 Risk-Free
11/1/18 $ 22.86 2,740.37 0.149%
10/1/18 $ 15.51 2,924.59 0.148%
9/1/18 $ 19.83 $ 0.2300 2,901.13 0.145%
8/1/18 $ 21.00 2,813.36 0.142%
7/1/18 $ 25.32 2,718.37 0.140%
6/1/18 $ 24.94 $ 0.2300 2,734.62 0.147%
5/1/18 $ 22.59 2,654.80 0.150%
4/1/18 $ 26.47 2,648.05 0.147%
3/1/18 $ 24.41 $ 0.2300 2,640.87 0.139%
2/1/18 $ 24.41 2,713.83 0.135%
1/1/18 $ 25.12 2,823.81 0.119%
12/1/17 $ 29.36 $ 0.2300 2,673.61 0.113%
11/1/17 $ 29.20 2,584.84 0.103%
10/1/17 $ 38.45 2,575.26 0.094%
9/1/17 $ 40.24 $ 0.2300 2,519.36 0.086%
8/1/17 $ 45.31 2,471.65 0.082%
7/1/17 $ 49.48 2,470.30 0.088%
6/1/17 $ 50.32 $ 0.2300 2,423.41 0.083%
5/1/17 $ 49.48 2,411.80 0.079%
4/1/17 $ 44.61 2,384.20 0.065%
3/1/17 $ 44.08 $ 0.1900 2,362.72 0.062%
2/1/17 $ 45.63 2,363.64 0.044%
1/1/17 $ 44.05 2,278.87 0.042%
12/1/16 $ 41.55 $ 0.1900 2,238.83 0.040%
11/1/16 $ 43.58 2,198.81 0.039%
10/1/16 $ 44.51 2,126.15 0.025%
9/1/16 $ 48.82 $ 0.1900 2,168.27 0.022%
8/1/16 $ 49.03 2,170.95 0.027%
7/1/16 $ 48.46 2,173.60 0.020%
6/1/16 $ 44.87 $ 0.1900 2,098.86 0.021%
5/1/16 $ 43.88 2,096.95 0.024%
4/1/16 $ 41.90 2,065.30 0.016%
3/1/16 $ 40.75 $ 0.1900 2,059.74 0.016%
2/1/16 $ 34.80 1,932.23 0.026%
1/1/16 $ 35.50 1,940.24 0.025%
12/1/15 $ 40.35 $ 0.1900 2,043.94 0.012%
11/1/15 $ 40.71 2,080.41 0.014%
10/1/15 $ 38.68 2,079.36 0.006%
9/1/15 $ 36.20 $ 0.1900 1,920.03 0.000%
8/1/15 $ 38.23 1,972.18 0.004%
7/1/15 $ 39.27 2,103.84 0.005%
6/1/15 $ 37.30 $ 0.1900 2,063.11 0.001%
5/1/15 $ 35.69 2,107.39 0.000%
4/1/15 $ 34.43 2,085.51 0.000%
3/1/15 $ 35.28 $ 0.1900 2,067.89 0.002%
2/1/15 $ 35.31 2,104.50 0.001%
1/1/15 $ 33.13 1,994.99 0.000%
12/1/14 $ 34.23 $ 0.1700 2,058.90 0.003%
11/1/14 $ 32.48 2,067.56 0.000%
10/1/14 $ 29.81 2,018.05 0.000%
9/1/14 $ 30.78 $ 0.1700 1,972.29 0.001%
8/1/14 $ 29.83 2,003.37 0.002%
7/1/14 $ 28.90 1,930.67 0.002%
6/1/14 $ 27.58 $ 0.1700 1,960.23 0.002%
5/1/14 $ 25.90 1,923.57 0.003%
4/1/14 $ 26.64 1,883.95 0.002%
3/1/14 $ 26.45 $ 0.1500 1,872.34 0.002%
2/1/14 $ 28.27 1,859.45 0.004%
1/1/14 $ 27.21 1,782.59 0.001%
12/1/13 $ 28.54 $ 0.1500 1,848.36 0.005%
11/1/13 $ 26.59 1,805.81 0.005%
10/1/13 $ 25.96 1,756.54 0.003%
9/1/13 $ 24.09 $ 0.1500 1,681.55 0.000%
8/1/13 $ 22.03 1,632.97 0.002%
7/1/13 $ 23.53 1,685.73 0.003%
6/1/13 $ 22.86 $ 0.1500 1,606.28 0.003%
5/1/13 $ 23.42 1,630.74 0.003%
4/1/13 $ 22.82 1,597.57 0.004%
3/1/13 $ 22.61 $ 0.1500 1,569.19 0.005%
2/1/13 $ 20.08 1,514.68 0.008%

PORT

Ibar Inky Izap Ivision Istand Ipray Ialone Iweep
Ibar 0.1489 0.0042 -0.0291 0.0775 -0.0218 0.0273 -0.0635 0.0147
Inky 0.0042 0.2911 -0.0076 0.0711 0.0782 0.0211 0.0323 0.0090
Izap -0.0291 -0.0076 0.2381 0.0150 0.0483 -0.0002 0.0207 0.0819
Ivision 0.0775 0.0711 0.0150 0.6240 -0.0983 0.0469 0.0145 0.0253
Istand -0.0218 0.0782 0.0483 -0.0983 0.3754 -0.0158 0.0395 -0.0711
Ipray 0.0273 0.0211 -0.0002 0.0469 -0.0158 0.0634 -0.0105 0.0178
Ialone -0.0635 0.0323 0.0207 0.0145 0.0395 -0.0105 0.2803 -0.0408
Iweep 0.0147 0.0090 0.0819 0.0253 -0.0711 0.0178 -0.0408 0.2611
Mean 10.35% 19.95% 20.88% 25.88% 36.01% 8.14% 19.63% 18.25%

SPORT-T

Sales Growth 15.00% COGS/Sales
Cash/Sales SG&A/Sales
A/R Sales Interest/LTDebt 6.00%
Inventory/Sales Interest/ST Debt 4.00%
Net Fixed Assets/Sales Int Earned 1.50%
A/P.Sales Taxes/EBT
Other CL/Sales DPS/EPS
Shares Outstanding (in thousands) 475,000 Depr/NFA
Required Return (WACC) 9.00%
Long-Term CF growth rate 3.25%
Risk free rate 2.50%
BALANCE SHEET 12/31/17 12/31/18 12/31/19 12/31/20 12/31/21 12/31/22
Year 1 2 3 4 5
Assets
Current Assets
Cash And Cash Equivalents $ 681,000
Marketable Securities $ 305,100
Net Receivables $ 506,000
Inventory $ 3,338,000
Total Current Assets $ 4,830,100
Property Plant and Equipment $ 2,751,800
Total Assets $ 7,581,900
Liabilities
Current Liabilities
Accounts Payable $ 516,900
Notes Payable
Other Current Liabilities $ 469,000
Total Current Liabilities $ 985,900
Long Term Debt $ 5,100,000
Total Liabilities $ 6,085,900
Stockholders' Equity
Common Stock $ 49,000
Retained Earnings $ 1,304,000
Capital Surplus $ 143,000
Total Stockholder Equity $ 1,496,000
Total Liabilities + Net Worth $ 7,581,900
Balance Sheet Check $ - 0
INCOME STATEMENT 2017
Total Revenue $ 30,848,000
Cost of Revenue $ (23,122,000)
Gross Profit $ 7,726,000
Operating Expenses
Selling General and Administrative $ (5,626,000)
Depreciation $ (456,000)
Total Operating Expenses $ (6,082,000)
Income from Continuing Operations $ 1,644,000
Total Other Income/Expenses Net $ - 0
Earnings Before Interest And Taxes $ 1,644,000
Interest Expense $ (293,000)
Income Before Tax $ 1,351,000
Income Tax Expense $ (371,525)
Net Income From Continuing Ops $ 979,475
Net Income $ 979,475
Dividends Paid $ (151,000)
Change in Retained Earnings $ 828,475
STATEMENT OF CASH FLOWS 2017 2018 2019 2020 2021
Cash Flows from Operations
Net Income
Depreciation Expense
Decrease (Increase) in Receivables
Decrease (Increase) in Inventories
Decrease (Increase) in Other Current Assets
Increase (Decrease) in Accounts Payable
Increase (Decrease) in Other Current Liabilities
Increase (Decrease) in Other Non-Current Liabilities
Total Cash Flow From Operations
Cash Flows from Investing
(Additions to) Gross Property Plant and Equipment
Investment in Other Non-Current Assets
Total Cash Flows from Investing
Cash Flows from Financing
From Issuance (Repayment) of Short-Term Debt
From Issuance (Repayment) of Long-Term Debt
From Sale (Repurchase) of Equity
Dividends Paid to Shareholders
Cash Flows From Financing
Net Change in Cash and Marketable Securities
Beginning Cash and Marketable Securities
Ending Cash and Marketable Securities
Net Change in Cash and Marketable Securities

CAPITAL

MACRS Factors
Equipment Cost Salvage (year 9) MACRS life Year 3 5
Pipe Cutter $85,000 $0 3 yrs 1 0.3333 0.2000
Pipe Bender $165,000 $10,000 5 yrs 2 0.4444 0.3200
Robot Welder $825,000 $40,000 5 yrs 3 0.1482 0.1920
4 0.0741 0.1152
5 0.1152
6 0.0576

OPTION

POLLING

SUNNY

Asset Life
Year 3
1 0.3333
Variable Distribution Mean Standard Deviation 2 0.4444
Initial Outlay Normal $1,000,000 $10,000 3 0.1482
Initial Sales Normal $1,600,000 $200,000 4 0.0741
Growth in Sales Normal 4% 2% 5
COGS/Sales Normal 70% 4% 6
Initial Fixed Cost Normal $100,000 $10,000
Growth in Fixed Cost Normal 3% 2%
Working Capital/Sales Normal 5% 1%
Salvage Normal $100,000 $5,000
Discount Rate Normal 12% 2%
Minimum Maximum
Tax Rate Uniform 22% 30%