Instructions

This project is designed to be a hands-on practice with the concepts in Chapter 7.

Your goal is to use Excel in such a way that any changes to assumptions will correctly ripple through the entire profitability analysis.

If executed properly, you should be able to use this Excel analysis over and over (what if analysis) 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.

Your Excel File submission should include four tabs total and the file name should be Last name_Project 3.xls or Last Name_Project 3.xlxs

Email Assignment: You will be writing an email to Jonathan to address the three potential changes to the future of Jonathans Pest Eliminations. Your email should be written in Microsoft Word. Submit your assignment in the Western Online assignment area with the file name Project 3 Email.doc or Project 3 Email.docx or Project 3 Email.pdf.

TO: Jonathan

FROM: Your name

The email subject line should communicate what you have done in less than 6 words.

Organize your email with three paragraphs.

The first paragraph should tell Jonathan how the change in cost impacted the companys overall breakeven point (compared to the original analysis).

The second paragraph should tell Jonathan how the sales price decrease impacted the projected operating income for next month (compared to the original analysis).

The third paragraph should tell Jonathan how the change in employee compensation impacted the overall target profit point (compared to the original analysis).

Average CM per unit