Assume that you are working as a marketing manager at NewGen Robotics of Greenville, Oregon. The company is introducing a new line of household robots capable of performing basic vacuuming and cleanup tasks. You are examining the projected income statement for the company$$s operations and calculating the break$-$even point for the product as well as exploring the optimal price point.

The table shows the projected income statement for the company:

Revenue

Account Amount

Units Sold $102,391$

Price per Unit

$ $399\mathrm{.}95$

Variable Expenses

Material Cost per Unit

$ $175\mathrm{.}50$

Manufacturing Cost per Unit $ $143\mathrm{.}25$

Fixed Expenses

Salaries and Benefits

$$3,450,000$

Advertising $ $775,000$

Administrative $ $520,000$

Miscellaneous $ $500,000$

Tasks

Complete an income statement based on the values from the Revenue table. Assume that the company produces enough cleaning robots to have a $1\%$ surplus over units sold.

Based on your income statement, calculate the number of units the company would have to sell at its current price to break even.

Use a one$-$way data table to calculate the total revenue, expenses, and net income if the company sells from $50,000$ to $200,000$ units in increments of $25,000.$ Create a CVP chart based on your results.

Use a two$-$way data table to calculate the net income for units sold from $50,000$ to $200,00$ units in increments of $25,000.$ Calculate sales prices of $$300$ up to $$500$ in increments of $$50.$

Requirements

Using Excel create a spreadsheet with the following information based on the tasks listed:

create an income statement based on the values provided in the table

calculate the number of units the company would have to sell to break even

create a one$-$way data table and a CVP chart

create a two$-$way data table