Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Please use EXCEL. I need the EXCEL formula as well as the solution. Prepare Problem using Excel. Please note, you must show your work in

Please use EXCEL. I need the EXCEL formula as well as the solution. Prepare Problem using Excel. Please note, you must show your work in Excel, which includes providing the formulas in the cells, not just the summary value. I need to know the formula for when I put it in excel to get to the answer that is provided.

(3) Customer-Profitability Analysis; Activity-Based Costing 1. Operating income, Trace Telecom: $7,000

Fresno Fiber Optics, Inc. manufactures fiber optic cables for the computer and telecommunications industries. At the request of the company vice president of marketing, the cost management staff has recently completed a customer-profitability study. The following activity-based costing information was the basis for the analysis.

Customer-Related Activities Cost Driver Base Cost Driver Rate

Sales activity .................................................Sales visits .................................................$1,000

Order taking .................................................Purchase orders ........................................200

Special handling ...........................................Units handled ............................................50

Special shipping ...........................................Shipments ..................................................500

Cost-driver data for two of Fresnos customers for the most recent year are

Customer-Related Activities Trace Telecom Caltex Computer

Sales activity .................................................. 8 visits ...................................................... 6 visits

Order taking .................................................. 15 orders .................................................... 20 orders

Special handling ............................................800 units handled ........................................600 units handled

Special shipping ............................................ 18 shipments ............................................. 20 shipments

The following additional information has been compiled for Fresno Fiber Optics for two of its customers, Trace Telecom and Caltex Computer, for the most recent year:

Trace Telecom Caltex Computer

Sales revenue ...............................................$190,000 ...............................................$123,800

Cost of goods sold ....................................... 80,000 ...............................................62,000

General selling costs .................................... 24,000 ...............................................18,000

General administrative costs ....................... 19,000 ...............................................16,000

Required: 1. Prepare a customer profitability analysis for Trace Telecom and Caltex Computer.

2. Build a spreadsheet: Construct an Excel spreadsheet to solve requirement (1) above. Show how the solution will change if the following information changes: Trace Telecoms sales revenue was $185,000 and Caltex Computers cost of goods sold was $59,000.

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

The Process Auditing And Techniques Guide

Authors: J.P. Russell

2nd Edition

087389782X, 978-0873897822

More Books

Students also viewed these Accounting questions

Question

Is Apex code required to create, delete and update records?

Answered: 1 week ago

Question

At what level(s) was this OD intervention scoped?

Answered: 1 week ago