Financial Management Financial Management Financial Management Financial Management midterm
sp19_midterm_takehome__ibrahim_almusalit___1_.xlsx
sp19_midterm_.xlsx
Unformatted Attachment Preview
This is your personal Mid-Term Exam File. You are to only use the file that was sent t
else’s file as this will be considered a ACADEMIC DISHONESTY AND WILL RESULT IN A
COURSE.
Caution: This is an EXAMINATION that you are to complete outside of class. Do not be mistaken
homework assignment. Because this is an examination you are expected to complete this on yo
not to work on or discuss the examination with anyone else other than with your instructor. You
assistance with any part of this exam from anybody other then your instructor. Any assistance o
will be considered ACADEMIC DISHONESTY AND WILL RESULT IN A FAILING (F) GRADE FOR THIS C
from the tutorials, homework assignments, practice problems, and your textbook to help you de
questions found in this examination. DO NOT COPY AND PASTE COMPLETE MODELS OR ANY POR
TUTORIAL, HOMEWORK ASSIGNMENTS, OR PRACTICE PROBLEMS. YOU MUST CREATE YOUR MO
INFORMATION GIVEN IN THE EXAM.
IMPORTANT: SAVE THIS SPREADSHEET TO THE DESKTOP OF THE COMPUTER YOU ARE USING WIT
RESAVE IT OFTEN WHILE YOU ARE WORKING ON IT.
SAVE THIS FILE BACK TO YOUR COMPUTER WITH YOUR NAME IN THE FILENAME AND RESAVE IT
COMPLETING THE EXAM. IT IS YOUR RESPONSIBILiTY TO PROTECT YOUR WORK AGAINST TECHN
BE EXTENDED DUE TO TECHNICAL FAILURES INCLUDING LOSS OF FILE OR CORRUPTION. IT IS ADV
MULTIPLE LOCATIONS.
When you have completed this exam spreadsheet: Save it one last time to your computer. Subm
MARCH 15th by 11:59 pm.
There are 100 points available from the take home portion of this exam. Points are shown on ea
where possible.
We will have the in class portion of the Mid-Term exam on March 12th at 9:30 in our classroom.
be worth 25 points. There will not be any make ups given!!
This Exam is worth a total of 125 points.
Please let me know if you have any questions.
This test belongs to Ibrahim Almusalit
the file that was sent to you. Do not use somebody
AND WILL RESULT IN A FAILING (F) GRADE FOR THIS
class. Do not be mistaken thinking that this is a simple
ted to complete this on your own. This means that you are
n with your instructor. You are not to receive help and
structor. Any assistance other then from an approved source
LING (F) GRADE FOR THIS COURSE. You may use material
ur textbook to help you develop your models and answer the
LETE MODELS OR ANY PORTION OF THE MODELS FROM THE
U MUST CREATE YOUR MODELS FROM SCRATCH USING
PUTER YOU ARE USING WITH YOUR NAME IN THE FILENAME.
ILENAME AND RESAVE IT OFTEN WHILE YOU ARE
UR WORK AGAINST TECHNICAL DIFFICULTIES. TIME WILL NOT
OR CORRUPTION. IT IS ADVISABLE TO SAVE YOUR WORK IN
me to your computer. Submit it via the Canvas dropbox by
m. Points are shown on each tab. Partial credit will be given
h at 9:30 in our classroom. This portion of the Mid-Term will
Problem 1:
Format the area below to look as much like the
picture to the right as possible. Do not include the
arrows.
Elvis Products International
Income Statement
For the Year Ended Dec. 31, 2018
Sales
Cost of Goods Sold
Gross Profit
Selling and G&A Expenses
Fixed Expenses
Depreciation Expense
EBIT
Interest Expense
Earnings Before Taxes
Taxes
Net Income
2018
6950000
6275000
675000
345000
120000
21250
188750
85000
103750
41500
62250
2017
6725000
6150000
575000
225000
120000
17900
212100
67500
144600
57840
86760
Rows 11-13 are
36 pixels high.
Text is centered
horizontally and
vertically
Col
C
Column
B
Col
D
Elvis Products International
ows 11-13 are
6 pixels high.
ext is centered
orizontally and
ertically
Income Statement
For the Year Ended Dec. 31, 2018
2018
Sales
Cost of Goods Sold
2017
$
6,950,000
6,275,000
$
6,725,000
6,150,000
Gross Profit
Selling and G&A Expenses
Fixed Expenses
Depreciation Expense
$
675,000
345,000
120,000
21,250
$
575,000
225,000
120,000
17,900
EBIT
Interest Expense
$
188,750
85,000
$
212,100
67,500
Earnings Before Taxes
Taxes
$
103,750
41,500
$
144,600
57,840
Net Income
$
62,250
$
86,760
Column B is
35 characters
wide
Columns C and
D are 14
characters wide
00
00
00
00
00
00
00
00
00
40
60
Ibrahim Almusalit
Ibrahim Almusalit
Problem 2:
You are given the data for a company for 12 months. The company uses short-term loans and marketabl
the desired level at the end of each month. Short-term loans will be paid down or paid off with any exce
only when the loan balance is zero. Marketable securities will be sold first to supply any needed cash inf
Note that the
marketable securities and no short-term loans at the beginning of January.
inputs are in
dollars
but the
The company expects to make an outlay for new capital equipment in
May, June,
July, August, Septemb
Cell L24 for the user to select the month of the outlay. Title the list as “Capital
Outlay” and create an inst
statements
Capital Outlay from the list below.” Use this and other input information
to complete
below
are in the Cash Budget.
short-term loans at the end of each month in Row 52 and the balancethousands
of marketable
of securities at the en
positive balances when marketable securities are being used, and it should show zero when marketable
Format the range F45:N45 so that all values less than zero display as red numbers instead of black numb
Name the input cells for collections Collect0, Collect1, and Collect 2. Then create and save three scenarios using th
cash flow for April – December while changing the collection rates. The BEST scenario will Collect0 = 30%, Collect1
base case collection rates given here, and the WORST scenario will have Collect0 = 0%, Collect1 = 50%, and Collect
titled “Scenario Summary.
INPUTS
Collections on sales in the month of the sale
Collections on sales in the month following the sale
Collections on sales in the second month following the sale
20%
55%
25%
Cash Operating Expenses as a Percentage of Current Month Sales
40%
Jan
Expected Sales
Feb
Mar
Apr
May
$ 7.875 $ 9.188 $ 9.975 $ 11.025 $ 7.350
Collections on Sales
Cash Operating Expenses
Fixed Expenses
Lease Payments
Capital Outlay
Tax Payments
$
$
$
$
550 $
675 $
130 $
550
–
Total Cash Outflows
$ 1.355 $
550
–
CASH BUDGET
Mar
Beginning Cash Balance
Apr
May
Net Cash Flow
Unadjusted Ending Cash Balance
Adjustment Needed
Ending Cash Balance
10.000
Short-Term Loans and Marketable Securi
Mar
Short-Term Loans Outstanding
Marketable Securities
Apr
–
May
rm loans and marketable securities investments to bring the cash balance to
or paid off with any excess balances and marketable securities will be used
pply any needed cash infusions before loans are used. The company had no
, July, August, September, October or November. Create a drop-down list in
utlay” and create an instruction box saying “Select the desired month of the
mplete the Cash Budget. Then complete the section that gives the balance of
able securities at the end of each month in Row 53. The formulas should show
w zero when marketable securities are not being used.
s instead of black numbers for any values of the inputs.
ve three scenarios using the Scenario Manager that show the company’s monthly net
ll Collect0 = 30%, Collect1 = 60%, and Collect2 = 10%, the BASE scenario will use the
Collect1 = 50%, and Collect2 = 50% . Save a summary of the scenarios on the next tab
NPUTS
Capital Outlay for New Equipment
$ 80.000
Month of the Capital Outlay
Desired End-Of-Month cash balance
Jun
Jul
Aug
$ 15.000
Sep
Oct
Nov
Dec
$ 9.450 $ 11.130 $ 12.075 $ 9.450 $ 7.875 $ 6.825 $ 5.775
$
$
550 $
$
550 $
675 $
550 $
$
550 $
$
550 $
675 $
$
–
130 $
–
–
$
550 $ 1.355 $
$
$
550 $
130 $
550 $
$
$
$
550
–
550 $ 1.355 $
550 $
550
$
BUDGET
Jun
Jul
Aug
Sep
Oct
Nov
Dec
nd Marketable Securities
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Ibrahim A
Ibrahim Almusalit
This is a continuation from Problem 2. Using the Cash Budget in problem 2. Name the input cells for collection
scenarios using the Scenario Manager that show the company’s monthly net cash flow for April
Collect0 = 30%, Collect1 = 60%, and Collect2 = 10%, the BASE scenario will use the base case collection rates giv
Collect1 = 50%, and Collect2 = 50% . Save a summary of the scenarios on a separate tabbed page.
Name the input cells for collections Collect0, Collect1, and Collect 2. Then create and save three
ash flow for April – December while changing the collection rates. The BEST scenario will
the base case collection rates given here, and the WORST scenario will have Collect0 = 0%,
arate tabbed page.
Ibrahim Almusalit
Ibrahim Almusalit
Problem 3:
You need forecast the 2019 and 2020 pro forma income statement and balance sheet for the firm whose 2017 and 201
income statements and balance sheets are given here. Inputs are provided for most items in the Inputs section below
The cost of goods sold in 2019 and 2020 are expected to change with sales by 103% and 105% respectively
arithmetic average of the proportion of this item in relation to sales for 2017 and 2018. Selling and G&A Expenses,
Accounts receivable, Inventory, and Accounts Payable are expected to change with sales at 100% of the two
arithmetic average of their percentage of sales for 2017 and 2018. The firm has planned an investment of $225,000 in
equipment in 2019. This equipment will be depreciated at $45,000 per year. Depreciation on existing Plant/Equipmen
be the same as it was in 2017. Interest expense for 2019 is computed on the 2018 ending balances in Short Term Note
Payable and Long Term Debt. Inputs for those interest rates are provided in the Inputs section.
Complete the pro-forma income statement and balance sheet for 2019 and 2020 using the information above, the inp
below, and the values that are given in the statements. The 2019 and 2020 projected statements should accurately ad
for any changes in the inputs.
Compute the excess or deficit of financing for 2019 in the yellow box at the bottom of the Balance Sheet. This numbe
should be positive if the firm will have more financing than is needed, and it should be negative if the firm has less
financing than is needed. If you have excess financing for 2019 reduce your reduce the 2019 Long Term debt financing
Calculate your 2020 interest expense using the 2019 ending balances in Short Term Notes Payable and Long Term Deb
Inputs for those interest rates are provided in the input section
Compute the excess or deficit of financing for 2020 in the yellow box at the bottom of the Balance Sheet. This numbe
should be positive if the firm will have more financing than is needed, and it should be negative if the firm has less
financing than is needed.
INPUTS
2019 Percent Change in Sales from 2018
2020 Percent Change in Sales from 2019
Interest Rate on Short Term Notes Payable
Interest Rate on Long Term Debt
Tax Rate for 2019 and 2020
Common Stock Dividend for 2019 and 2020
Expected addition to Plant and Equipment in 2019
Additional depreciation on new Plant/Equip in 2019 and 2020
5,000%
5,000%
5,250%
6,750%
39,0%
$125.000
$225.000
$45.000
Income Statement
2017
Sales
Cost of Goods Sold
Gross Profit
2018
3.514.000
2.284.100
3.795.120
2.656.584
1.229.900
1.138.536
2019
350.000
120.000
30.000
375.000
125.000
32.500
EBIT
Interest Expense
729.900
56.000
606.036
62.900
Earnings Before Taxes
Taxes
673.900
235.800
543.136
207.600
Net Income
438.100
335.536
Selling and G&A Expenses
Fixed Expenses
Depreciation Expense
130.000
Balance Sheet
Assets
Cash and Equivalents
Accounts Receivable
Inventory
52.000
406.000
854.000
2018
118.036
540.000
740.000
1.312.000
429.000
126.000
1.398.036
580.000
158.500
303.000
421.500
1.615.000
1.819.536
130.000
179.000
118.000
150.000
210.000
85.000
190.000
95.000
Total Current Liabilities
Long-term Debt
427.000
614.000
445.000
500.000
525.000
Total Liabilities
Common Stock
Retained Earnings
1.041.000
395.000
179.000
945.000
395.000
479.536
574.000
874.536
1.615.000
1.819.536
Total Current Assets
Plant & Equipment
Accumulated Depreciation
Net Fixed Assets
Total Assets
Liabilities and Owner’s Equity
Accounts Payable
Short-term Notes Payable
Other Current Liabilities
Total Shareholder’s Equity
Total Liabilities and Owner’s Equity
Excess/(Deficit) Financing for 2019 and 2020
2017
2019
118.036
395.000
$
–
he firm whose 2017 and 2018
in the Inputs section below.
respectively of the two-year
lling and G&A Expenses,
t 100% of the two-year
n investment of $225,000 in new
on existing Plant/Equipment will
alances in Short Term Notes
information above, the inputs
ments should accurately adjust
Balance Sheet. This number
ative if the firm has less
19 Long Term debt financing.
Payable and Long Term Debt.
Balance Sheet. This number
ative if the firm has less
2020
135.000
2020
118.036
185.000
100.000
525.000
395.000
$
–
f
Ibrahim Almusa
Ibrahim Almusalit
Problem 4:
INSTRUCTIONS:
Use the space beginning in Row 30 to create an amoritzation table model that will work for ANY ALLOWABLE values of
changeable inputs are in red. Create restrictions on the input cells that prevent users from entering values that are not
The amount of the loan must be a positive number.
The balloon payment must be a positive number or zero and must be less than the amount of the loan.
The term of the loan can be 1, 2, 3, 4, 5, 6, 7, 8, 9, or 10 years.
The interest rate can be between 3% and 15%.
The payment frequency can be annual, quarterly, or monthly. Use a drop-down list in Cell F26 with “Annual”, “Semi
“Quarterly” and “Monthly” as the choices. Use the results from that cell to set the payment frequency for computation
Each row in your table should show the monthly payment, the interest portion of that payment, the principal portion o
and the balance immediately following that payment for all payments within the term of the loan. Rows in the table th
the term of the loan should show nothing (be blank) except for the payment number. All values in the table should be
or zero.
In cell H23, create a formula that computes the total dollar amount of interest that will be paid over the life of the loan
inputs.
In cell H26, create a formula that computes the effective annual interest rate for the loan given the inputs.
INPUTS:
Amount of Loan:
Term of Loan in Years
Annual Interest Rate on Loan:
Balloon Payment
Payment Frequency
Payment
Number
0
1
2
3
4
5
6
7
8
9
10
11
12
Payment
Interest
$250.000
10
3,00%
$75.000
Semi-Annual
Total Interest Paid
over life of loan
Principal
Balance
Effective Annual
Interest Rate
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
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
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
work for ANY ALLOWABLE values of the inputs. Userrs from entering values that are not allowed.
amount of the loan.
in Cell F26 with “Annual”, “Semi-Annual”,
payment frequency for computation in the table.
hat payment, the principal portion of that payment,
rm of the loan. Rows in the table that are beyond
er. All values in the table should be positive numbers
will be paid over the life of the loan. given the
e loan given the inputs.
1
Ibrahim A
Ibrahim Almusalit
Problem 5:
The MACRS depreciation rates are given in the table
to the right. Using the inputs provided below, create
whatever formulas are needed to compute the book value
of the asset at the end of the year shown in the input.
Your method must work for any allowed values of the inputs.
The inputs for depreciation class and years owned are
drop-down lists. Do not change those cells.
Depreciation Class
Purchase Price
Years owned
Book Value of the asset
7-Year
$50.000
2
MACRS Pe
Year
1
2
3
4
5
6
7
8
9
10
11
MACRS Percentages
3-year
33,33%
44,45%
14,81%
7,41%
5-year
20,00%
32,00%
19,20%
11,52%
11,52%
5,76%
7-year
14,29%
24,49%
17,49%
12,49%
8,93%
8,92%
8,93%
4,46%
10-year
10,00%
18,00%
14,40%
11,52%
9,22%
7,37%
6,55%
6,55%
6,56%
6,55%
3,28%
3-Year
5-Year
7-Year
10-Year
1
2
3
4
5
6
7
8
9
10
Ibrahim
Ibrahim Almusalit
Problem 6:
The Supreme Shoe Company is considering the purchase of a new, fully-automated machine to replace a manually ope
being replaced, now 1 year old, originally had an expected life of 4 years, is being depreciated as a 5-year MACRS asset
of $103,500. It can be sold now for $49,500. If the old machine is used for 4 more years instead of being replaced now,
$11,250 before-tax residual value at that time. The annual cash expenses for the old machine are $22,500.
The replacement machine being considered has a purchase price of $135,000 and an expected salvage value of $56,250
life. There will also be shipping and installation expenses of $7,875. The constant investment in raw materials would inc
$13,500 from the current level with the old machine if the new machine is purchased and the old one is salvaged. The c
annual cash expenses for the new machine will be $19,125. The new machine will also result in additional productive ca
increase by $33,750 per year due to increased output. The new machine will be depreciated as a 5-year MACRS
Before considering this project, the company undertook an engineering analysis of current facilities to determine if oth
necessitated by the purchase of the machine. The study cost the company $12,500 and determined that existing faciliti
machine with no other changes. In order to purchase the new machine, the company would have to take on new debt
interest, resulting in increased interest expense of $12,000 per year. The required rate of return for this project is 9% a
marginal tax rate is 34%.
Use the space below to compute the initial outlay, the annual cash flows, and the terminal cash flows for this project.
to be linked to your computations are the ones shown. All other numbers can be entered straight into your formula
need to done in the formulas. The MACRS depreciation percentages are in the table to the right. You do not need to d
and the model only needs to work for this time period and for this depreciation type. Show all computations. Label al
THE NUMBER RESULTS ARE WHAT IS IMPORTANT, NOT THE FORMATTING. YOU CAN SET THIS UP ANY WAY THAT YO
THE 4 TOTAL CASH FLOWS GET COMPUTED CORRECTLY AND THEY ADJUST CORRECTLY TO THE INPUTS SHOWN.
Calculate the NPV and IRR of this project. Explain whether you would replace the old machine with the new machin
INPUTS
Old Machine:
Original Price
Current Value
Expected value in 4 years
Annual Cash Expenses
103.500
49.500
11.250
22.500
New Machine:
Original Price
Installation Expenses
Expected value in 3 years
Annual Cash Expenses
Increased Sales
135.000
7.875
56.250
19.125
33.750
CASH FLOWS
CASH FLOWS AT t=0
CASH FLOWS FOR YEARS 1 to 4
Year 1
Project NPV
Projects IRR
Would you invest in the new machine? Why or why not?
e to replace a manually operated one. The machine
-year MACRS asset from its purchase price
tead of being replaced now, it is expected to have a
ne are $22,500.
cted salvage value of $56,250 at the end of its 3-year
nt in raw materials would increase by a total of
he old one is salvaged. The company expects that
ult in additional productive capacity so sales will
year MACRS-class asset.
MACRS Percentages
facilities to determine if other changes would be
ermined that existing facilities could support this new
d have to take on new debt of $30,000 at 10%
eturn for this project is 9% and the company’s
cash flows for this project. The only inputs that need
d straight into your formulas but all calculations
You do not need to do any lookup functions,
ow all computations. Label all entries appropriately.
THIS UP ANY WAY THAT YOU WANT AS LONG AS
O THE INPUTS SHOWN.
Year
1
2
3
4
5
6
7
8
9
10
11
3-year
33,33%
44,45%
14,81%
7,41%
5-year
20,00%
32,00%
19,20%
11,52%
11,52%
5,76%
7-year
10-year
14,29% 10,00%
24,49% 18,00%
17,49% 14,40%
12,49% 11,5 …
Purchase answer to see full
attachment
