Exercise 2: A First Excel Assignment

Suppose a firm has the following situation:

Product 1 2 Price/unit $125 $345 Unit volume/year 8,400 4,200 Cost of goods sold/unit $62 $188

Furthermore, Sales commissions are 3% of sales. Other operating expenses are $245,000. General and administrative expenses are $387,000. Interest expenses are $112,000. Income taxes are 46% of taxable income.

Using these assumptions, a pro forma income statement in Excel in the following format:

Gross sales xxxx xxxx less: Commissions xxxx xxxx Net sales xxxx xxxx less: Cost of goods sold xxxx xxxx Gross profit xxxx xxxx Total gross profit xxxx less: Other operating expenses xxxx General & administrative xxxx Interest expenses xxxx Income before taxes xxxx less: Income taxes xxxx Net income xxxx

Each of the following questions represents its own deviation from the base case,1 (i.e., the questions are not cumulative):

1. What happens to net income if for product 1 the price is increased to $150 and the volume is decreased to 6,500 units? 2. What happens to net income if General & administrative becomes 20% of gross sales? 3. What happens to net income if Cost of goods sold on both products increases by 10%? 4. If the unit volume for product 2 is always half of the unit volume for product 1, what product 1 volume yields a zero net income?