Please answer in excel TotalKuantan ATV, Inc. assembles five different models of all-terrain vehicles (ATVs) from various ready-madecomponents to serve the Las Vegas, Nevada, market. The company uses the same engine for all its ATVs. Thepurchasing manager, Ms. Jane Kim, needs to choose a supplier for engines for the coming year. Due to the sizeof the warehouse and other administrative restrictions, she must order the engines in lot sizes of 1,000 each. Theunique characteristics of the standardized engine require special tooling to be used during the manufacturingprocess. Kuantan ATV agrees to reimburse the supplier for the tooling. This is a critical purchase since latedelivery of engines would disrupt production and cause 50 percent lost sales and 50 percent back orders of theATVS. Jane has obtained quotes from two reliable suppliers but needs to know which supplier is more cost-effective. The terms of sale are 4/10 net 30 for Supplier 1 and 2/10 net 30 for Supplier 2. The data related to thecosts of ownership associated with two reliable suppliers has been collected in the Microsoft Excel Online filebelow. Open the spreadsheet and perform the required analysis to answer the questions below.Que onsssibility: Good to go1. What is the total cost of ownership for each of the suppliers? Assume the buyer will take advantage of thelargest discount. Do not round intermediate calculations. Round your answers to the nearest cent.Supplier 1S fill in the blank 22. Which supplier is more cost-effective?SearchDLSupplier 2S fill in the blank 3GFocusBE A1 Total Cost of Ownership Analysis23 Requirements (annual forecast units)4Lot size (Q)SWeight per engine (lbs)Order processing cost (per order)Inventory carrying rate (per year)Cost of working capital (per year)Profit marginPrice of finished ATVBack-order cost (per unit)Back-order lost salesLate delivery lost sales5son800 S.16೯ ರoglerome20212324fx25Tosoft 6dgeOther InformationTruckload (TL=40,000 lbs)Less-than-truckload (LTL)Per ton-mileDays per yearInvoice payment period (days)Discount period (days)B14,0001,00024$165.0024%15%16%$5,000$18.0050%50%Sheet1 +Catculation Mode: Automatic Workbook Statistics$0.70 per ton-mile$1.40 per ton-mile2,000 lbs per mile3653010DItems1 to 999 units per unit1,000 to 2,999 units per unit3,000+ units per unitTooling costTerms (net 30)Distance (miles)Supplier Quality Rating (defects)Supplier Delivery Rating (lateness)Total engine costCash discount (net 30)Cash discount (early payment)Tooling costTransportation costOrdering costCarrying costQuality costBackorder costLost sales costTotal costLowest costESupplier 1$520.00$511.00$501.00$21,0004%1402%2%Supplier 1$7.280,000.00SAMSUNGFSupplier 2$530.00$521.00$516.00$25,000Supplier 22%1103%3%Formu=B3 E3

Answered: Image /qna-images/answer/72535e9a-bf1d-4689-976c-9d87901a5335.jpg

Kuantan ATV, Inc. assembles five different models of all-terrain vehicles (ATVs) from various ready-made components to serve the Las Vegas, Nevada, market. The company uses the same engine for all its ATVs. Th purchasing manager, Ms. Jane Kim, needs to choose a supplier for engines for the coming year. Due to the siz of the warehouse and other administrative restrictions, she must order the engines in lot sizes of 1,000 each. TEH unique characteristics of the standardized engine require special tooling to be used during the manufacturing process. Kuantan ATV agrees to reimburse the supplier for the tooling. This is a critical purchase since late delivery of engines would disrupt production and cause 50 percent lost sales and 50 percent book:

Kuantan ATV, Inc. assembles five different models of all-terrain vehicles (ATVs) from various ready-made components to serve the Las Vegas, Nevada, market. The company uses the same engine for all its ATVs. Th purchasing manager, Ms. Jane Kim, needs to choose a supplier for engines for the coming year. Due to the siz of the warehouse and other administrative restrictions, she must order the engines in lot sizes of 1,000 each. TEH unique characteristics of the standardized engine require special tooling to be used during the manufacturing process. Kuantan ATV agrees to reimburse the supplier for the tooling. This is a critical purchase since late delivery of engines would disrupt production and cause 50 percent lost sales and 50 percent book:

Practical Management Science

6th Edition

ISBN:9781337406659

Author:WINSTON, Wayne L.

Publisher:WINSTON, Wayne L.

Chapter2: Introduction To Spreadsheet Modeling

Section: Chapter Questions

Problem 20P: Julie James is opening a lemonade stand. She believes the fixed cost per week of running the stand...

See similar textbooks

Similar questions

The following information pertains to Emy Manufacturing Corporation's Product X: Annual demand 33,750 units Annual cost to hold one unit of inventory P15 Setup cost (or the cost to initiate a production run) P500 Beginning inventory of product X 0 At present, the Company produces 2,250 units of Product X per production runt for a total of 15 production runs per year. The company is considering to use the EOQ model to determine the economic lot size and the number of production runs that will minimize the total inventory carrying cost and setup cost for Product X. If the EOQ model is used, the economic lot size is a.2,250 units. c. 2,250,000 units. b.1,500 units. d. P1,500.
Parker Hi-Fi Systems, located in Wellesley, Massachusetts, a Boston suburb, assembles and sells the very finest home theater systems. The systems are assembled with components from the best manufacturers worldwide. Although most of the components are procured from wholesalers on the East Coast, some critical items, such as LCD screens, come directly from their manufacturer. For instance, the LCD screens are shipped via air from Foxy, Ltd., in Taiwan, to Boston’s Logan airport, and the top-of-the-line speakers are purchased from the world-renowned U.S. manufacturer Boss. Parker’s purchasing agent, Raktim Pal, submits an order release for LCD screens once every 4 weeks. The company’s annual requirements total 500 units (2 per working day), and Parker’s per unit cost is $1,500. (Because of Parker’s relatively low volume and the quality focus—rather than volume focus— of many of Parker’s suppliers, Parker is seldom able to obtain quantity discounts.) Because Foxy promises delivery within 1…
Catlea Merchandising is engaged in selling school shoesfor both boys and girls in their teenage years. Catlea needs 32,000 pairs of shoes in a year in order to satisfy the market demand. It costs ₱ 48 to place an order while ₱ 8 is needed to hold each quantity of shoe in Catlea's inventory. Upon checking on Catlea's supplier, it takes 8 days in between placing an order and eventually receiving it. a. Determine the Economic Order Quantityb. Determine the number of order per monthc. Determine the reorder point
ITEMS 18 to 20 ARE BASED ON THE FOLLOWING INFORMATION: The following information pertains to Emy Manufacturing Corporation's Product X: Annual demand 33,750 units Annual cost to hold one unit of inventory P15 Setup cost (or the cost to initiate a production run) P500 Beginning inventory of product X 0 At present, the Company produces 2,250 units of Product X per production runt for a total of 15 production runs per year. The company is considering to use the EOQ model to determine the economic lot size and the number of production runs that will minimize the total inventory carrying cost and setup cost for Product X. At present, the company's total annual inventory costs is P 7,500. c. P24,375. P16,875. d. P22,500.
Basic EOQ Model Item X is a standard item stocked in a company's inventory of spare parts. Each year, the firm uses about 2,000 units of Item X, which costs Php 1,000 per unit. Storage costs, which include insurance and cost of capital, amount to 18 percent of item unit cost. Placing an order for more of Item X costs Php 400. The company operates 360 days per year and Item X is received 9 days after placement of order. How much would be Item X's total annual procurement cost? * Php 2,000,000 O Php 16,971 Php 8,485 None of the above dba Itam V'n total annual ctockouit cost? *
Develop a EOQ solution and calculate total relevant costs for the gross requirements in the following table*. Period 1 2 3 4 5 6 7 8 9 10 11 12 Gross requirements 20 30 30 30 20 10 40 60 *Holding cost =$ 2.50/unit/week; setup cost =$ 150; lead time =1 week; beginning inventory =40. What is the average demand per week? with 2 decimal places): Calculate Economic Order Quantity (EOQ) Develop a EOQ solution (enter your responses as whole numbers). Period 1 2 3 4 5 6 7 8 9 10 11 12 Gross requirements 20 30 30 30 20 10 40 60 Scheduled receipt…
Basic EOQ Model Item X is a standard item stocked in a company's inventory of spare parts. Each year, the firm uses about 2,000 units of Item X, which costs Php 1,000 per unit. Storage costs, which include insurance and cost of capital, amount to 18 percent of item unit cost. Placing an order for more of Item X costs Php 400. The company operates 360 days per year and Item X is received 9 days after placement of order. How much would be Item X's total annual purchasing cost? * O Php 8,485 O Php 2,000,000 None of the above Php 16,971
Item X is a standard item stocked in a company’s inventory of spare parts. Each year, the firm uses about2,000 units of Item X, which costs Php 1,000 per unit. Storage costs, which include insurance and cost ofcapital, amount to 18 percent of item unit cost. Placing an order for more of Item X costs Php 400. Thecompany operates 360 days per year and Item X is received 9 days after placement of order. How many units of Item X should be ordered each time? A. 94 unitsB. 95 unitsC. 100 unitsD. 2,000 units
Item X is a standard item stocked in a company’s inventory of spare parts. Each year, the firm uses about 2,000 units of Item X, which costs Php 1,000 per unit. Storage costs, which include insurance and cost of capital, amount to 18 percent of item unit cost. Placing an order for more of Item X costs Php 400. The company operates 360 days per year and Item X is received 9 days after placement of order. How much would be Item X's total annual stockout cost? A. None of the above B. Zero C. Php 8,485 D. Php 16,971
Item X is a standard item stocked in a company’s inventory of spare parts. Each year, the firm uses about 2,000 units of Item X, which costs Php 1,000 per unit. Storage costs, which include insurance and cost of capital, amount to 18 percent of item unit cost. Placing an order for more of Item X costs Php 400. The company operates 360 days per year and Item X is received 9 days after placement of order. How much would be Item X's total annual procurement cost? * Php 16,971 Php 2,000,000 None of the above Php 8,485 How much would be Item X's total annual storage cost? * Php 8,485 Php 16,971 Php 2,000,000 None of the above How much would be Item X's total annual stockout cost? * Php 16,971 Zero None of the above Php 8,485
Item X is a standard item stocked in a company’s inventory of spare parts. Each year, the firm uses about 2,000 units of Item X, which costs Php 1,000 per unit. Storage costs, which include insurance and cost of capital, amount to 18 percent of item unit cost. Placing an order for more of Item X costs Php 400. The company operates 360 days per year and Item X is received 9 days after placement of order. How many units of Item X should be ordered each time? When should Item X be ordered? How much would be Item X's total annual purchasing cost? How much would be Item X's total annual procurement cost? Including its total annual purchasing cost, how much would be Item X's total annual inventory cost? What is Item X's average inventory level? How much would be Item X's total annual storage cost? How much would be Item X's total annual stockout cost? How many times per year would Item X be ordered?
Aunt Molly's Old Fashioned Cookies bakes cookies for retail stores. The company's best-selling cookie is the chocolate nut supreme, which is marketed as a gourmet cookie and regularly sells for $8.00 per pound. The standard cost per pound of chocolate nut supreme, based on Aunt Molly's normal monthly production of 400,000 pounds is as follows: Cost Item Quantity Standard Unit Cost Total Cost Direct material: Cookie mix 10 oz. $0.02 per oz. $0.20 Milk Chocolate 5 oz. $0.15 per oz. $0.75 Almonds 1 oz. $0.50 per oz. $0.50 Total $1.45 Direct Labor Mixing 1 min $14.40 per hr. $0.24 Baking 2 min $18.00 per hr. $0.60 Total $0.84 Cost Item Quantity Standard Unit Cost Total Cost Variable overhead 3 min $32.40 per direct-labor hr. $1.62 Total Standard cost per pound $3.91 Aunt Molly's management accountant, Karen Blair, prepares monthly budget reports based on these standard costs. The month of April's…