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,