excel

Exam 1 Part B (Case Problem) Project Description and Instructions:

You have been hired as a financial consultant for an iPhone app developing company. You have been asked to compare two scenarios (Scenario 1 or Scenario 2) with different assumptions of sales price and annual demand for the app based on similar apps in the market. You are required to make a recommendation to the company to choose the scenario that will result in greater Net profit for the firm for the first year. You will use the IF function in cell G2 to compare Net Profit under Scenario 1 and Scenario 2 to make the recommendation of Scenario 1 or Scenario 2. The firm’s fixed costs for year 1 include development costs, advertising costs and maintenance costs. The only variable cost is the 30% commission on the Sales Price that is paid to apple for using their platform for each app sold. The fixed and variable costs remain unchanged for all scenarios. Following is the data for costs:

Fixed Costs

Developer Costs $30,000

Advertising Costs $24,000

Maintenance Costs $12,000

Variable Cost per unit Apple’s Commission 30% of Sales price

As part of this project you will also perform What-IF Analysis using Goal seek and determine the quantity needed to be sold to make a profit for $500,000 at the end of the first year if the sales price is $2.99. The Goal Seek solution for Quantity Sold should be referenced in cell G3. Following is the Sales data including the Sales Price and the Quantity Sold under each scenario.

Scenario 1 Scenario 2 Goal Seek

Sales Price $1.99 $3.99 $2.99

Quantity Sold 300,000 100,000 To be determined using goal seek

To begin working in this case you will need to download the data file

iPhone_app_sales.xlsx provided to you and save it as iPhone_app_sales

profit.xlsx .

The formulae needed to complete the case are provided below:

Total Fixed Costs= Developer +Advertising +Maintenance costs

Variable Cost per unit = Sales Price* Apple’s commission

Total Variable Costs= Variable Cost per unit * Quantity Sold

Contribution Margin = Sales Price – Variable Cost per unit

Quantity Sold for Breakeven = Round((Total Fixed Costs/ Contribution Margin),0)

Sales Revenue = Sales Price * Quantity Sold

Net Profit = Sales Revenue – Total Fixed Costs – Total Variable Costs