Project Instructions
Starting File: ExcelloScanners - Student.xlsx
Background: Your start-up company is about to launch a new line of high speed scanners, and you are hoping to attract a large amount of money from top-level investors. To do this, you need to prepare a projected income statement. You also need to calculate the break-even point for your new product and determine the optimal price point.
You wish to determine the sales price that will maximize the company's revenue. If the price is too high, scanner sales will not be strong. If too low, although you will sell more units, profit may drop. You wish to use price elasticity to determine the optimal price point.
Important: The images shown pdf below are the final results after two runs of Solver. Your intermediate results in earlier steps will NOT match all of the values shown in the image. Your final result should match, however. Follow these instructions to complete the project. Refer to the screen images shown at the end of the instructions to verify your results.
Remember: Project work must be your individual work - no group work allowed! Ask the instructor for help if needed.
Turn on the Solver feature:
1. Follow the instructions in the dark red box near the bottom of page EX 634 to activate the Solver feature. (Note: if you did not see Solver in the tool bar, and still do not see Solver in the toolbar after following those steps, restart Excel.)
Here is the Projecteddata for this activity:
|
Revenue section
|
|
|
Units Sold
|
103,000
|
|
Price per unit
|
$389.00
|
|
Variable Expenses section
|
|
|
Material cost per unit
|
$187.75
|
|
Manufacturing cost per unit
|
$134.35
|
|
Fixed Expenses section
|
|
|
Salaries and Benefits
|
$3,825,000
|
|
Advertising
|
$625,000
|
|
Administrative
|
$483,000
|
|
Miscellaneous
|
$300,000
|
Table 1
Steps:
Income Statement Worksheet:
1. Complete the Income Statement section in columns B on the Income Statement worksheet, by entering the data given in the Table 1 above in the appropriate cells. Assume that your company will produce enough scanners to have a 1% surplus over the units that will be sold.Use formulas with cell references to complete the following steps:
a. Calculate Total Revenue in cell B9.
b. Calculate Units Produced in cell B12 as being 1% above the Units Sold amount.
c. Calculated Total Material Cost in cell B14.
d. Calculate Total Manufacturing Cost in cell B16.
e. Calculate Total Variable Expenses in cell B17.
f. Calculate Total Fixed Expenses in cell B24.
g. Calculate Total Revenue in cell B27.
h. Calculate Total Expenses in cell B28.
i. Calculate Net Income in cell B29.
2. Create a one-variable data table to complete the Cost-Volume Profit Analysis section in columns D to G of the Income Statement worksheet. Assume that your company will sell between 50,000 to 200,000 scanners, in increments of 25,000.
3. Create a two-variable data table to complete the Net Income Analysis section on the Income Statement worksheet. Assume that units sold will range from 50,000 to 200,000 units in increments of 25,000. Assume that the price will range from $300 to $500 in increments of $50.
4. All currency amounts should be formatted with currency (2 decimal places).
Note that your results should now match Figure 1 below at this point in time.
CVP Chart Worksheet:
5. Create a CVP chart based on the Cost-Volume Profit Analysis completed in the prior step. Place this chart on a separate worksheet named CVP Chart. Format as shown in the images below. This will provide information about the break-even point.
Note that your chart should now match Figure 2 below at this point in time.
Maximize Net Income Worksheet:
6. Make a copy of the Income Statement worksheet, and name the new worksheet Maximize Net Income Worksheet. (Note: You must successfully complete Steps 1-4 above in order to be successful in this worksheet.)
7. Insert a new column at the location of col. C. Add headings of New Price in cells C6, C11, C19, and C26.
8. Add a heading of Price Elasticity of Demand in cell A3.
9. Enter the Price Elasticity value of 1.8 in B3, which indicates that for every 10% increase in the price, sales will decline by 18 percent.
10. Enter 389.00 in the Price Per Unit under New Price (cell C8).
11. You will now construct a formula for Units Sold using elasticity in cell C7 as follows, using the appropriate cell references:
=projected units sold times (1+price elasticity value times (1-new price per unit/projected price per unit))
Note that this will result in a value matching the value in cell B7 at this point in time.
12. Use the data provided in Table 1 above for Variable Expenses and Fixed expenses. Use formulas to calculate cells C9, C12, C14, C16, C17, C24, C27, C28, and C29 as you did in column B.
Note that this will result in column C values matching the values in column B at this point in time.
13. You will now use Solver to determine the company's possible maximum net income that could be achieved by changing the price of the scanner. Set Solver to find the maximum net income in cell C29 by changing the value of the Price PerUnit under the New Price (cell C8). Constrain Units Produced under the New Price (cell C12) to be between 25,000 and 250,000 inclusive (you should have two Solver constraints). Make sure that the checkbox for Make Unconstrained Variables Non-Negative is cleared. Tell Solver to Solve. Keep the Solver solution, which will insert the new value in C29.
Note that your results should now match Figure 3 below at this point in time. If they do not match, remember that you must always set cell C8 back to 389.00 before you re-run Solver.
Attachment:- ExcelloScanners-Student.xlsx