You will use Excel for this case. All non-input cells will either reference another cell or a calculated (formula) cell. Format all the cells as "accounting". Any variable cost elements that you discover should be displayed to four decimal places. The purpose of this case is to reinforce the concept of cost behavior, and to give you some Excel experience. Remember that readability and neatness does count when you create your solution.
Data regarding the Pochettino Company's operations and selling expenses for five months have been accumulated in order develop a budget estimate for an upcoming month. The Marketing Department has reviewed this data and could not reach a conclusion as to what is the most appropriate independent variable for predicting variable and mixed costs. These costs may be dependent on sales dollars, shipping weight, or distance shipped.
| Data Set for the Pochettino Company case |
| April |
May |
June |
July |
August |
| Distance Shipped (miles) |
425,000 |
420,000 |
450,000 |
375,000 |
550,000 |
| Weight Shipped (pounds) |
65,000 |
25,000 |
70,000 |
72,000 |
85,000 |
| Dollar Sales |
$ 4,200,000 |
$ 3,900,000 |
$ 4,500,000 |
$ 5,000,000 |
$ 8,400,000 |
| Cost of Goods |
2,520,000 |
2,340,000 |
2,700,000 |
3,000,000 |
5,040,000 |
| Gross Margin |
1,680,000 |
1,560,000 |
1,800,000 |
2,000,000 |
3,360,000 |
| Costs |
| Advertising |
204,000 |
198,000 |
210,000 |
220,000 |
288,000 |
| Commissions |
126,000 |
117,000 |
135,000 |
150,000 |
252,000 |
| Salaries |
80,000 |
80,000 |
80,000 |
80,000 |
80,000 |
| Supplies |
21,700 |
20,500 |
24,500 |
19,700 |
27,000 |
| Shipping costs |
87,500 |
60,000 |
93,000 |
95,200 |
109,500 |
| NOI |
$ 1,160,800 |
$ 1,084,500 |
$ 1,257,500 |
$ 1,435,100 |
$ 2,603,500
|
REQUIRED:
A. Use Excel to review the data in the chart above. Specifically, you need to determine which of the costs - Advertising, Commissions, Salaries, Supplies, and Shipping Costs - are purely variable, purely fixed, and which are mixed. You also need to determine what is the cost driver for those costs that are variable and/or mixed.
Show how you determined the nature of the costs.
B. Develop a cost formula for each cost/expense item.
C. Pochettino Company wants to develop a budget for selling expenses for October 2017 when the following estimates are to be applicable: dollar sales - $5,500,000; weight shipped- 75,000 pounds; distance shipped - 570,000 miles.
Prepare a traditional Income Statement for October 2017 which has the following:
1. Cost/Expense Item
2. Type of cost for each cost item--variable (V), fixed (F), mixed (M).
3. Variable cost component, if any, for each cost item.
4. Fixed cost component, if any, for each cost item.
5. October 2017 estimate for each cost item.