to gain proficiency in conceptualizing and constructing decision mechanisms for business and personal financial investment scenarios,
to gain experience using the financial functions that exist in Excel,
to determine key financial metrics for a company based on income statement and balance sheet information,
to determine time value of money solutions for various personal and business scenarios, and
to enhance team collaboration skills.
Description: You must collaborate with a partner in your course section and turn in one copy of the answers for each team; failure to complete the assignment with a partner will result in zero points earned. The assignment must be completed in Excel using the three worksheets described below. All cells containing final responses must contain formulas or functions to receive credit; simply typing the correct answer in the appropriate cell will result in no credit. Your completed Excel file must be uploaded to Canvas by Thursday, April 25, 2024, at 11:59 p.m. The filename must consist of your last names followed by the text Hwk2 (e.g., LoneLevi_Hwk2.xlsx). Record all dollar values as whole numbers (e.g., $1,250, not $1,250.00) and all percentages with two decimal places (e.g., 1.14%, not 0.01).
Assessment: Point values for each part of the assignment appear after the prompt. Professional appearance (5 points) relates to numerical formatting in accordance with course protocols, inclusion of an appropriate spreadsheet title, student names, and date of creation, inclusion of descriptions for each part of the homework, and the general layout of contents.
Worksheet #1: You are contemplating investing $ 200,000 in a new value-added food product. Key assumptions include:
A six-year time horizon,
$ 3.59 sales price per unit,
$ 1.99 cost of production per unit,
first year sales of 26,350 units, with sales increasing 5.50% per year thereafter, and
the current required rate of return is 11.00%.
Deliverables:
Create Table 1 that is shown below and add the appropriate assumption values in the yellow highlighted cells.
Create Table 2 that is shown below and add the appropriate cell references, mathematical equations, or Excel functions in the yellow highlighted cells. Simply typing the correct answer in the appropriate cell will result in no credit. (3 points for updating when the values in Table 1 are changed.)
Based on the capital budgeting values (NPV, B/C ratio, IRR and MIRR) you calculated in Table 2, explain why you should or should not invest in this manufactured product. Simply stating “you will, or will not, make money” is not a sufficient answer.
Table 1 – Key Assumptions (3 points- ½ point per value)
Initial investment
Sales price per unit
Cost per unit
First year sales units
Percentage increase in units
Required rate of return
Table 2 – Capital Budgeting Analysis (12 points)
Year
Units Sold
Inflows
Outflows
NCF
0
1
2
3
4
5
6
SRR
NPV
B/C ratio
IRR
MIRR
Worksheet #2 – Part 1: Your company had $203 million in sales in 2023 and $51 million in operating expenses, along with total assets of $107.590 million and total debt of $64.554 million. The operating profit margin was 10.89 percent for the year. Construct the multi-step income statement table below to answer these two questions. (Table 3: 5 points – ½ point per value)
If the company’s tax rate is 32.00 percent and the interest rate on company debt is 7.00 percent, what are the 2023 values of operating profit and net profit? Assume that this year’s interest expense must be paid in full on all outstanding debt. (2 points)
What are the gross and net profit margins? (2 points)
Table 3 – Income Statement, December 2023
Item
Dollars
Percentage
Sales
COGS
Gross profit
Operating expenses
Operating profit (EBIT)
Interest expense
Earnings before taxes (EBT)
Taxes paid
Net profit
Worksheet #2 – Part 2: Retro Style Company produces four lines of accessories for a major U.S. clothing manufacturer. The lines are known by the code letters G, H, I, and J. The current sales mix for the Retro Style Company and the contribution margin ratio for these product lines are as follows:
Product Line
Percent of Total Sales
Contribution Margin Ratio
G
38.65%
32.00%
H
24.50%
22.00%
I
16.15%
38.00%
J
20.70%
52.00%
Forecasted total sales for next year are $330,000 and total fixed costs $67,500.
Construct the table below showing the dollar values for: 1) sales, 2) variable costs, and 3) the contribution associated with each product line and the company. (8 points total – ½ point per table value)
Calculate the overall contribution margin ratio (a percentage) and the break-even sales dollars for the Retro Style Company. (2 points)
Table 4 – Contribution Breakdown by Product & Company
Line Item/Product & Company
G
H
I
J
Company
Sales
Variable costs
Contribution
Contribution margin ratio
32.00%
22.00%
38.00%
52.00%
Worksheet #3 – Part 1: Compute the requested value(s) for each scenario in a table like the one shown below. Show all component numbers that factor into determination of the final answer(s) in separate cells.
Table 5 – Time Value of Money Solutions
Scenario
N
m
I
PV
PMT
FV
College
?
?
?
?
?
?
You have determined that $150,000 will be required 18 years from now to help pay for your daughter’s higher education. If your bank is paying 5.75% annual rate of return on its ‘College Opportunity’ account, how much must you deposit each month to reach your goal? (2 points)
Your brother in-law is offering to pay you $50,000 seven years from now in exchange for a $20,000 investment today. If you accept his offer, what annual rate of interest would you earn on your investment? (2 points)
You plan to retire in 12 years and move to Cabo San Lucas where you have your eye on a sunny beach house. The current market value of the house is $92,000 and the annual appreciation rate is 6.00%. If you can earn an 8.50% annual rate of return on your Z-Trade investment account, how much must you invest at the end of each quarter for the next 12 years to be able to buy your dream home upon retirement? (2 points)
Starting eleven years from now you would like to withdraw $36,000 a year for a period of 5 years (years 11 through 15) plus the additional amount of $24,000 in year 15. Assuming an interest rate of 7.50%, how much must you deposit today to make these future withdrawals a reality? (4 points)
On January 1, 1963, a kid from Sioux Falls purchased a limited-edition Superman comic at the original market price of 12 cents. On January 1, 2000 (37 years later), the comic was sold at auction for $8,700. What annual rate of return did this kid earn on the comic? (2 points)
Worksheet #3 – Part 2: The net cash flows associated with three investments are provided below. Assume that all blank cells represent zero net cash flows. Create the table below and show the maximum amount you are willing to pay for each investment. Assume a 12.50% discount rate. (6 points)
Table 6 – Maximum Willingness to Pay
End of Year
Z
Y
X
1
$ 40,000
$ 40,000
2
40,000
3
40,000
4
40,000
5
40,000
$ 40,000
200,000
6
40,000
7
40,000
8
40,000
9
40,000
10
40,000
40,000
I provided pictures that provide the tables given on the prompt. Copying and pasting messed up the formatting.
