Excel Assignment Review

CASE PROBLEM 2

Case Problem 2

Data File needed for this Case Problem: Popcorn.xlsx

Seattle Popcorn Seattle Popcorn is a small company located in Tacoma, Washington, that

produces gourmet popcorn distributed in the Northwest. Steve Wilkes has developed a

workbook that will allow him to perform a profit analysis for the company. Using this work-

book, he wants to create formulas to determine the break-even point for the company—the

sales volume needed so that revenues will match the anticipated monthly expenses. Three

factors determine the break-even point: the sales price of each unit of Seattle Popcorn, the

variable manufacturing cost to the company for each unit, and the fixed expenses (salaries,

rent, insurance, and so on) that the company must pay each month. Steve wants to be able

to explore a range of possible values for each of these factors, as follows:

• The sales price of each unit of Seattle Popcorn can vary from $5 to $1 5 (in whole

numbers).

• The variable manufacturing cost of each unit can vary from $5 to $1 5 (in whole

numbers).

• The fixed monthly expense for the company can vary from $1 5,000 to $30,000 (in

whole numbers).

Complete the following:

1 . Open the Popcorn workbook located in the Excel8\Case2 folder included with your

Data Files, and then save the workbook as Seattle Popcorn. In the Documentation

sheet, enter your name and the date.

2. Switch to the Profit Analysis worksheet, and then define names for cells, as follows:

cell C1 5: PricePerUnit

cell C1 6: CostPerUnit

cell C1 7: MonthlyExpenses

3. In the range H3:H43, enter a formula using defined names to calculate the revenue,

which is determined by the units sold multiplied by the price per unit.

4. In the range I3:I43, enter a formula using defined names to calculate the expenses,

which are determined by the units sold multiplied by the cost per unit plus the fixed

monthly expense.

5. In cell C1 8, enter a formula to calculate the break-even point, which is determined

by the fixed monthly expense divided by the difference between the price per unit

and the cost per unit. Use the IFERROR function to display a blank cell instead of an

error value.

6. In cell C1 9, enter a formula to calculate the revenue at the break-even point, which

is determined by the break-even point multiplied by the sales price per unit. Use the

IFERROR function to display a blank cell instead of an error value. 7. Create the validation rules for cells C1 5, C1 6, and C1 7, as shown in Figure 8-38.Figure 8-38 Validation rules for cells C15, C16, and C17

8. Protect the worksheet so the user can enter data only in cells C1 5, C1 6, and C1 7.

Everything else in the worksheet should remain locked.

9. Enter the following values in the worksheet to determine how many units Seattle

Popcorn must sell each month in order to break even:

• Sales Price per Unit = $13

• Manufacturing Cost per Unit = $10

• Fixed Monthly Expense = $30,000

1 0. In cell C1 6, add the following comment: Call Joe Heller in Cost Accounting for unit

cost data.

Note: In the following steps, you’ll create a macro. Save your workbook before

recording the macro. That way, if you make a mistake while recording the macro,

you can close the workbook without saving the changes, and then reopen the work-

book and try again. Also, read the list of tasks before you begin recording them.

1 1 . Save the workbook.

1 2. Create a macro named PrintChart with the shortcut key Ctrl+a and an appropriate

macro description that performs the following tasks:

a. Set the print area to include the chart and input/output area (range A1 :E20).

b. Set the page layout to landscape orientation, centered horizontally on the page,

with the text Break-even Analysis in the center section of the header, and your

name in the right section of the footer.

c. Print the chart and input area.

d. Make cell A1 the active cell.

e. Stop recording the macro.

1 3. Test the PrintChart macro by pressing the Ctrl+a keys. If the macro doesn’t work,

close the workbook without saving your changes, reopen the workbook, and record

the macro again.

1 4. Edit the PrintChart macro, replacing the line ActiveWindow. SelectedSheets.

PrintOut Copies: =1, Collate: =True, IgnorePrintAreas: =False with

ActiveSheet. PrintPreview. (Hint: The line to edit is four lines above the end of

the macro.)

1 5. Create a button in the range A22:B23, assign the PrintChart macro to the button, and

change the default label to a more descriptive one.

1 6. Run the PrintChart macro to test the button and the revised macro.

1 7. Save the workbook as a macro-enabled workbook using the name SP with Macros,

and then close it.

1 8. Submit the finished workbook to your instructor, either in printed or electronic form,

as requested.