Pony Espresso is a small business that sells specialty coffee drinks at office buildings. Each morning and afternoon, trucks arrive at offices’ front entrances, and the office employees purchase various beverages such as Java du Jour and Café de Colombia. The business is profitable. Pony Espresso offices, however, are located north of town, where lease rates are less expensive, and the principal sales area is south of town. This means the trucks must drive across town four times each day.

The cost of transportation to and from the sales area plus the power demands of the trucks’ coffee brewing equipment are a significant portion of variable costs. Pony Espresso could reduce the amount of driving and, therefore, the variable costs, if it moved the offices closer to the sales area.

Pony Espresso presently has fixed costs of $10,000 per month. The lease of a new office, closer to the sales area, would cost an additional $2,200 per month. This would increase the fixed costs to $12,200 per month.

Although the lease of new offices would increase the fixed costs, a careful estimate of the potential savings in gasoline and vehicle maintenance indicates that Pony Espresso could reduce the variable costs from $0.60 per unit to $0.35 per unit. Total sales are unlikely to increase as a result of the move, but the savings in variable costs should increase the annual profit.

Project Focus

Consider the information provided to you from the owner in the data file AYK19_Data.xlsx. Especially look at the change in the variability of the profit from month to month. From November through January, when it is much more difficult to lure office workers out into the cold to purchase coffee, Pony Espresso barely breaks even. In fact, in December, the business lost money.

1. Develop the cost analysis on the existing lease information using the monthly sales figures provided to you in the data file.

2. Develop the cost analysis from the new lease information provided above.

3. Calculate the variability that is reflected in the month-to-month standard deviation of earnings for the current cost structure and the projected cost structure.

4. Do not consider any association with downsizing such as overhead—simply focus on the information provided to you.

5. You will need to calculate the EBIT (earnings before interest and taxes).

  

Project Number Project Name Project Type Page Number Plug-In Focus Area Project Lovel Skill Set Financial Excel T2 Personal Introductory Formulas AYK4 Destiny Budget Cash Flow Introductory Formulas AYKA Cash Flow Excel T2 TI, T2 Hardware and Software 3 Technology Budget Excel Introductory Formulas AYKA Excel Formulas Tracking Donations T2 Employee Introductory AYKA Relationships Global Commerce 5 Convert xcel T2 Introductory Formulas AYK.5 Currency Cost Excel T2 Total Cost of Introductory Formulas AYK.5 Comparison Ownership Time Excel or Project T12 Project Management Introductory Gantt Charts AYK.6 Management Maximize Excel T2, T4 Strategic Analysis Intermediate Formulas or AYK.6 Profit Solver Security Analysis Excel T3 Filtering Data Intermediate Conditional AYK.7 Formatting, Autofilter, Subtotal 10 Gathering Data Excel T3 Data Intermediate Conditional AYK8 Analysis Formatting 11 Intermediate Scanner System Excel T2 Strategic Formulas AYK.8 Analysis 12 Profit Intermediate Formulas Competitive Pricing Excel T2 AYK.9 Maximization 13 Adequate Acquisitions Excel T2 Break-Even Intermediate Formulas AYK.9 Analysis 14 CRM Customer Relations xcel T3 Intermediate PivotTable AYK.9 15 Assessing Excel T3 Data Intermediate PivotTable AYK.10 the Value of Information Analysis 16 Growth, Trends, and Forecasts Excel T2, 13 Data Forecasting Advanced Average, Trend, Growth AYK.11 17 Shipping Costs Excel T4 SCM Advanced Solver AYK.12 18 Formatting Grades Advanced If, LookUp Excel T3 Data AYK.12 Analysis