Business: Jonathans Pest Eliminations

Jonathan has developed two products for pest infestation protection and elimination:

Product 1: Terminator, a product that destroys everything in its sight that sells for $25 per unit.

Product 2: Rambo, a product that infiltrates infestations to kill the pests at their source. It sells for $14 per unit.

Jonathan has two employees. The employees each earn a salary of $400 per month plus a 5% commission. The commission is 5% of sales revenue.

Jonathan purchases the products from a supplier in Montana. The cost is $10 for each Terminator and $5 for each Rambo

Shipping costs $2 for each Terminator and $2.28 each for each Rambo. Jonathan pays the shipping costs.

Jonathan plans to sell 700 Terminators and 300 Rambos next month. His goal is to earn at least $5,000 per month.

Excel Work

Step 1: Starting file

On this tab, you can enter the given data in the blue assumptions box (columns A & B). All other cells in this worksheet in columns F, I, J and K should be entered as formulas with cell references to the assumptions area. Black cells will not require any entry.

The starting file has each cell already formatted to include the correct label and format (such as dollars or percentages). In the real world, this is an important item to consider when you build an Excel spreadsheet.

In columns E and F, you are analyzing as if each product were sold individually and the other product did not exist.

Breakeven units, Target units answers should be rounded to the nearest whole unit. The formula in Excel to do this is =ROUND. Here are examples of how to use the formula

=ROUND(Q17/T14,0) or = ROUND((Q20/R12)/T4,0)

To find the Breakeven point in dollars (sales revenue), use the rounded answer in units times the sales price per unit. Same for target profit in sales revenue.

Here are your check figures to confirm you have the basic work done correctly on the starting file:

Terminator Breakeven Point in units = 68 units

Rambo Target Profit in sales revenue = $13,482

Operating income = $9,231

Multi Product analysis Overall breakeven point = 80 units

Multi Product analysis Target profit sales for Product 1 = 405 units

Step 2:

Right click on the tab name for Starting File. Create a copy of the tab and name the file Cost increase.

Right click on the tab name for Starting File. Create a copy of the tab and name the file Sales price decrease.

Right click on the tab name for Starting File. Create a copy of the tab and name the file change in employee compensation.

You should now have four tabs.

If you completed the formulas correctly in Step 1 on the starting file, the following calculations will AUTOMATICALLY recalculate when you make a change in the blue assumptions area of columns B and C.

Each Step 3,4,5 item is a unique question and uses the starting file assumptions to begin.

Step 3: Cost Increase

On the cost increase tab, assume that Jonathans supplier in Montana is increasing the cost to $12 per unit of Terminator and $8 per unit of Rambo.

Step 4: Sales price decrease

On the sales price decrease tab, assume that to compete with a new competitor in the market, Jonathan has to decrease the sales price of each unit. The Terminator sales price is now $22.50 and the Rambo sales price is now $12.

Step 5: Employee Compensation

Jonathans two employees want to negotiate their contracts. Rather than earn a commission, they are asking for instead a salary of $1,000 per month.