Aroma Coffee, Inc., operates a small coffee shop in the downtown area. Its profits have been declining, and Problem 1.39 management is planning to expand and add ice cream to the menu. The annual ice cream sales are expected _ Benefit-Cost Analysis:

to increase revenue by $30,000. The cost to purchase ice cream and cones from the manufacturer is $15,000. | Add a Product Line The present manager will supervise the coffee shop and ice cream shop. Due to expansion, however, the (LO 4)

labor costs and utilities would increase by 30 percent. Rent and other costs will increase by 25 percent.

AROMA COFFEE, INC. eXcel Annual Income Statement Before Expansion mhhe.corhilton4e SEES VEU Aaa Oh Se ee Oe $ 55,000 OSS era Satna hee! ek, sh etigl Memes eines act OO Glen baka we cee teate ett saat Sah em: 16,000 NEDO e sage ey sins outed tut hh We das ur ore 12,000 LATTES. Sots Ee eae eee ere 3,000 Ciera: Ah Aen SUNY Seo pate 6,000

@thewcostsyass shee: = TN. Meee hens 3,000 Managekssalanyaiic ai iia skin air Baki 20,000 Wotalicosiss (alWis eeceesse) .LEM E. $ 60,000

@perating profii(lOScSe) heassea tee e $ (5,000)

Required

a. Should management open the ice cream shop? Show an analysis of the costs and benefits of adding ice cream.

b. BUILD YOI yi EET. Build an Excel spreadsheet to solve requirement (a).
Analyze the decision to expand if ai learn that Baskin-Robbins will open an ice cream store down the block. Aroma’s ice cream sales could drop by one-third. Use the following hints.
Hint 1: Follow these steps to build a flexible spreadsheet (also see Chapter 12): |
1. Place all facts, assumptions, and estimates in a “data input” section of the spreadsheet. For example, place each number used in the spreadsheet in a separate, labeled cell. This is the only place you should enter any numbers.
2. Write the relations among the spreadsheet’s numbers (or “parameters” as algebraic formulas using the numbers’ cell addresses (not the numbers themselves) in a separate section of the spreadsheet.
For example, expected total revenues could be calculated with a formula containing the cell addresses of current revenue + expected revenue from the expansion (e.g., = B3 + C3).
3. Steps | and 2 allow the spreadsheet to easily and flexibly reflect any changes you make in the parameters, such as the expected sales level, which appear only in the data input section.
Hint 2: You must make assumptions about how the possible drop in ice cream sales will affect revenues and costs. Make these assumptions explicit in the data input section and in the formulas. For example, enter the possible drop in sales, — 1/3, in the data input section and use that number in any formulas related to sales