50.1K

Verified Solution

Link Copied!

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.

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

Period 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,069,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.

D. Using the same estimated sales and other information from requirement C, construct a Contribution Format Income Statement.

Answer & Explanation Solved by verified expert

See Answer