Posted: September 16th, 2017

Finance Midterm Exam Assignment

Question 1 – 10 points

In Tab “Question 1” name the ranges containing the Sales for Year 1; as well as the COGS for Year 1 and calculate the Gross Margin for that year using the formula: “Sales_Yr1-COGS_Year1”

Question 2 – 15 points

In Tab “Question 2” use the VLookup function to determine the Opening Date of each store.

Question 3 – 20 points

In tab “Question 3” use the IF Statement to calculate the revenue according to the following:

If a customer orders more than 300 units of a product, they pay $3.00 per unit. If they order more than 500 units, they pay $2.70 per unit. If the customer orders less than 300 units, they pay $4 per unit.

In column E write the formula that calculates the Revenue for each customer. Make sure to use the IF statement to express the sales price as a function of the number of units sold.

Question 4 – 10 points

Answer question 4 in tab “Question 4”.

You are considering the following two mutually exclusive projects. The required rate of return is 11.25 percent for project A and 10.75 percent for project B. Which project should you accept and why?

Clearly demonstrate your knowledge of the NPV function in Excel in your answer.

Year
Project A
Project B

0
-$48,000
-$126,900

1
$18,400
$ 69,700

2
$31,300
$ 80,900

3
$11,700
$ 0

Question 5 – 10 points

Answer question 5 in the appropriate tab.

You are buying a car. Would you rather pay $30,000 today or $3,000 a year for 10 years? Assume 8% as the Interest Rate. Clearly show the use of the appropriate Excel function in your answer.
If at the end of each of the next 20 years you invest $5,000 a year toward your retirement and earn 7.5% a year on your investment, how much will you have when you retire?
If you are borrowing $15,000 for 5 years with an annual interest rate of 6%. What are your annual payments?

Question 6 – 10 points

Answer question 6 in the appropriate tab.

Using the information in worksheet “DATA-Mexico”, demonstrate your knowledge of the IF statement by calculating the Gross Margin in year 1 for all stores Type Signature. (Hint: Gross Margin = Sales – COGS)

Question 7 – 25 points

In worksheet “Question 7”, create a PivotTable report named “Mexico Sales” from the data in the “Data-Mexico” worksheet.
Add the “Region” and then add the “Store Name” fields to the Row label area of the PivotTable report.
Add the “Store Type” field to the Column Labels area of the report.
Add the “Total Sales” field to the Values area report and format its values as currency.
What are the total sales for Core stores in the Centro Sur region?

Click here to have a similar A+ quality paper done for you by one of our writers within the set deadline at a discounted