Data Analytics to Predict Solutions Your company is concerned that too much working capital is tied up in inventory, but at the same time are concerned

Data Analytics to Predict Solutions

Your company is concerned that too much working capital is tied up in inventory, but at the same time are concerned stockouts. While many companies reorder inventory items based on the number of units they think they will need, a more cost-effective method to determine the optimal order quantity is accomplished by calculating the economic order quantity (EOQ). The costs to be considered are holding costs consisting of storage facility costs and related labor costs; and order costs which consist of shipping and handling costs. One-half of the inventory is on hand at any point in time, and demand is relative even across time. The company’s inventory data and assumed possible order quantities are presented here.

Inventory Cost and Unit Data

Unit Cost

Units

Rate

Annual demand (D)

  2,250 

Order cost per order (S)

$500 

Inventory cost per unit

$250 

Holding cost per unit (H)

$25 

Cost of borrowing rate

10%

Assumed quantities ordered per year

50100150200250300350400450500550600650700750800850

Use the following template:

  • Module 5 CTA Excel File TemplateDownload Module 5 CTA Excel File Template

Requirements

There are four parts to this Assignment. Use Excel to perform the following.

  1. Use the economic order quantity formula (EOQ = SQRT((2SD/H)) to determine the optimal number of units that the company should order based on each assumed level of order quantities provided in the data.
  2. Complete the table by calculating the number of orders per year, annual order cost, annual holding cost, and annual total cost. Highlight the minimum annual total cost using conditional formatting. Hint: The minimum cost should equal the cost at the EOQ you calculated in part 1.
  3. Create a line chart that graphs annual order cost, annual holding cost, and annual total cost. The x-axis should be the quantity ordered. Include a chart legend, appropriate chart title, axes labels, and properly formatted amounts on the axes.
  4. Examine the chart and your responses to parts 1 and 2. Indicate any relationships.

Submit the provided Excel spreadsheet containing your answers to each of the above requirements. Use Excel functions to make any required calculations described in the requirements. Post your completed Excel spreadsheet containing your answers for your instructor to grade. Submit a Word file detailing your answer to Requirement 4. above regarding indicating and explaining any relationships you identify from examining the chart you prepare and your responses to parts 1 and 2.

Share This Post

Email
WhatsApp
Facebook
Twitter
LinkedIn
Pinterest
Reddit

Order a Similar Paper and get 15% Discount on your First Order

Related Questions

Choose TWO of the following passages: Genesis 3:1-7; Exodus 14:1-12; Leviticus 16:29-34; Numbers 14:11-19; Deuteronomy 30:15-20, and provide a one-page

Choose TWO of the following passages: Genesis 3:1-7; Exodus 14:1-12; Leviticus 16:29-34; Numbers 14:11-19; Deuteronomy 30:15-20, and provide a one-page interpretation of the passage for each based on what you have learned/acquired from the previous assignments performed for this course. As part of this presentation, a clear and concise principle

First, create an outline or concept map that summarizes your communication plan for a significant organizational change or event. Consider the following

First, create an outline or concept map that summarizes your communication plan for a significant organizational change or event. Consider the following examples: a new company branding initiative; the restructuring of a team, department or business unit; a company-wide community service project; the transition of an executive leader; an emergency

Instructions First, create an outline or concept map that summarizes your communication plan for a significant organizational change or event. Consider the

Instructions First, create an outline or concept map that summarizes your communication plan for a significant organizational change or event. Consider the following examples: a new company branding initiative; the restructuring of a team, department or business unit; a company-wide community service project; the transition of an executive leader; an

Comp 3 = Fin Review Walmart’s Financials and Business Model. Revised the attached paper with the requirments. Describe the business, including the type of

Comp 3 = Fin Review Walmart’s Financials and Business Model. Revised the attached paper with the requirments. Describe the business, including the type of business. Create the business case: ·        Determine why funding is needed for the company. ·        Determine the sources of funding. Consider self-funding, borrowing, equity, venture capital, and so on.

Wells Fargo Case Study BACKGROUND: In fall of 2016 federal regulators accused Wells Fargo of falsely opening over 2 million customer credit card and

Wells Fargo Case Study BACKGROUND: In fall of 2016 federal regulators accused Wells Fargo of falsely opening over 2 million customer credit card and checking accounts to meet unrealistic sales quotas (http://money.cnn.com/2016/09/08/investing/wells-fargo-created-phony-accounts-bank-fees/?iid=EL, September 4, 2017).  Since then, Wells Fargo has also been accused of forcing over 500,000 customers into auto insurance policies they did notneed,

Accounting is the process of recording, summarizing, analyzing, and reporting financial transactions of a business or organization. It provides vital

Accounting is the process of recording, summarizing, analyzing, and reporting financial transactions of a business or organization. It provides vital information to various stakeholders, including investors, creditors, management, and government agencies, enabling them to make informed decisions. Here’s a breakdown of key aspects within accounting: Financial Statements: These are the

Comp 3 = Fin Review Walmart’s Financials and Business Model. Revised the attached paper with the requirments. Describe the business, including the type of

Comp 3 = Fin Review Walmart’s Financials and Business Model. Revised the attached paper with the requirments. Describe the business, including the type of business. Create the business case: ·        Determine why funding is needed for the company. ·        Determine the sources of funding. Consider self-funding, borrowing, equity, venture capital, and so on.