Assembling and packaging small electronic devices for U.S. households in two contracted manufacturing plants: Olympia, Washington and Houston, Texas, TBG Hometronics Company distributes finished products to three TBG-owned regional distribution centers (DCs) - the West region DC located in Sacramento, California, the Central region DC located in Topeka, Kansas, and the East region DC in Richmond, Virginia. The regional DCs are supporting two major segments - eCity and Shop4Less simultaneously in each of the five regional markets in the U.S.: Northwest, Southwest, Central, Northeast and Southeast.

TBG has long partnered with eCity, which has five retail stores in each regional market. To diversify its customer base, TBG developed a second distribution channel Shop4Less, which has two mega-stores in each regional market. TBG does not own warehouses in regional markets and hence outsources local stocking and delivery-to-store services to a national 3PL firm Aplus Logistics Co. According to the agreement, TBG ships products from its own regional DCs to one centralized Aplus warehouse located in each of those five markets and Aplus is responsible for local stocking and direct-to-store deliveries to eCitys stores and Shop4Lesss stores with a flat charge of $800 per trip including stocking and delivery.

James Smith, vice president of distribution for TBG, was preparing for the annual strategy review meeting conducted by the executive team. Smith needed to assess the companys logistics costs and profitability of its two distribution channels: eCity, a retail chain, and Shop4Less, a mass merchandiser. A quick review of the companys prior year financials and distribution information made Smith believe that transportation costs may be too high.

Furthermore, Smith wondered whether the current inventory commitment with eCity and with Shop4Less may be re-negotiated given that inventory carrying cost is approximately 20% of inventory value. Smith was also concerned about the financing costs associated with payment terms for TBGs customers.

From last years shipment data, Smith was able to put together sales break-downs by market and by distribution channel (Table 1). Smith noted that each package contains 500 units of TBG products. 4,000 packages were shipped to eCity stores with 2,000 orders placed and 6,000 packages were shipped to Shop4Less with 500 orders placed. Average selling prices per unit were $25 when sold to eCity stores and $20 when sold to Shop4Less. Costs of goods sold per unit were $15 for all products.

Table 1 Sales by Market and Distribution Channel (in Number of Packages)

Channel	NW Market	SW Market	Central Market	NE Market	SE Market	Total
eCity	900	500	900	800	900	4000
Shop4Less	1100	800	1400	1600	1100	6000

Smith broke down logistics costs by major activities (Table 2).

Table 2 Logistics Costs

Order Processing	$ 1,000,000
Packaging	$ 2,500,000
Transportation (from Plants to Regional DCs)	$ 5,500,000
Local Stocking and Delivery Charges (3PL)	$ 1,800,000
Total Logistics Costs	$ 10, 800,000

Per agreements, TBG maintained a 60-day inventory supply for eCity stores and provided weekly direct deliveries to each retail store. TBG maintained a 30-day inventory supply for Shop4Less and shipped directly to each of its stores twice a week. All sales were made on credit with net 60 days for eCity and net 45 days for Shop4Less in terms of accounts receivables. Smith understood that TBGs average financing cost was approximately 9%. For convenience of calculation, in this exercise please assume 360 business days and 50 delivery weeks in a year.

Q1: Based on Activity-based Costing (ABC), what are the appropriate allocations of total logistics costs for each segment excluding inventory and sales financing costs (type numerical values only without the $ sign, use thousands separator, rounded to whole dollars. For example, 4,000,599)?

Q2. Based on inventory policy, what are the inventory carrying costs for each segment (type numerical values only without the $ sign, use thousands separator, rounded to whole dollars. For example, 4,000,599)?

Inventory carrying costs for eCity:

Inventory carrying costs for Shop4Less:

Q3. Based on accounts receivable payment terms, what are sales financing costs for each segment (type numerical values only without the $ sign, use thousands separator, rounded to whole dollars. For example, 4,000,599)?

e City

Shop4Less

Q4. Based on results from Q1-Q3, what is the net profitability for Shop4Less (rounded to four decimals; for example, type 0.1234)?

Q5. Based on results from Q1-Q3, what is return on assets (ROA) for Shop4Less (rounded to four decimals; for example, type 0.1234)? Note that we only consider current assets in this analysis.

Tutor:- kindly help with the step by step answer with the excel sheet.