{"id":17415,"date":"2023-04-26T20:01:27","date_gmt":"2023-04-26T20:01:27","guid":{"rendered":"https:\/\/www.goodacademic.com\/blog\/questions\/busi-2012-business-analytics-excel-project-1-economic-order-quantity-analysis\/"},"modified":"2023-04-26T20:01:27","modified_gmt":"2023-04-26T20:01:27","slug":"busi-2012-business-analytics-excel-project-1-economic-order-quantity-analysis","status":"publish","type":"questions","link":"https:\/\/www.goodacademic.com\/blog\/questions\/busi-2012-business-analytics-excel-project-1-economic-order-quantity-analysis\/","title":{"rendered":"BUSI 2012 \u2013 Business Analytics Excel Project #1 Economic Order Quantity Analysis"},"content":{"rendered":"

BUSI 2012 \u2013 Business Analytics<\/p>\n

Excel Project #1<\/div>\n
Economic Order Quantity Analysis<\/div>\n
Purpose: The purpose of this assignment is to apply your learning from Modules 1 through 5 to solving<\/div>\n
an economic order quantity analysis. You will write formulas and create a line chart.<\/div>\n
Caution: This IS an individual project. You can get help from the instructor if you don\u2019t understand<\/div>\n
something. Do not turn in a copy of someone else\u2019s work as your own. It is very easy to identify a copy<\/div>\n
of someone else\u2019s work, especially when it is an electronic file.<\/div>\n
Deliverables (120 points): An Excel spreadsheet that solves for economic order quanitty in a general<\/div>\n
way. Post your assignment solution to the D2L dropbox.<\/div>\n
\u2022 File Name for Deliverables: Excel Project 1.xls<\/div>\n
Scenario: This assignment is based upon an analysis concept found in economics, accounting, finance,<\/div>\n
and management \u2013 economic order quantity, also known as EOQ analysis. This analysis determines the<\/div>\n
number of units to order (and the number of times to order) to minimize the sum of inventory<\/div>\n
management costs (ordering costs and carrying costs).<\/div>\n
In order to apply EOQ analysis, four basic inputs are necessary: (a) projected annual demand for a<\/div>\n
product, (b) the purchase cost per unit of the product, (c) the carrying cost as a percentage of total cost,<\/div>\n
and (d) the administrative costs of placing an order.<\/div>\n
Economic Order Quantity (units). The formula for economic order quantity computed in units is:<\/div>\n
EOQ (units) =<\/div>\n
\u221a 2 x Annual Demand x Order Cost<\/div>\n
Cost Per Unit x Carrying Cost Percentage<\/div>\n
Order Frequency. The order frequency is based upon the EOQ in units and the projected annual demand:<\/div>\n
N = Annual Demand<\/div>\n
EOQ (units)<\/div>\n
Total Inventory Costs. The formulas for total inventory management costs, excluding purchase costs of<\/div>\n
the product, are:<\/div>\n
\u2022 Total Ordering Costs = Order Frequency x Order Cost Per Order.<\/div>\n
\u2022 Total Carrying Costs = Quantity Ordered x Cost Per Unit x Carrying Cost Percentage<\/div>\n
2<\/div>\n
\u2022 Total Inventory Costs = Total Ordering Costs + Total Carrying Costs<\/div>\n
Page 2<\/div>\n
EOQ Chart. An EOQ chart plots total carrying costs, total order costs, and total inventory management<\/div>\n
costs for various order quantities. The resulting chart will show the minimum total inventory<\/div>\n
management cost where the total carrying costs and total order costs intersect. This is lowest total<\/div>\n
inventory management cost.<\/div>\n
Instructions: Create an Excel worksheet to solve for economic order quantity in a general way.<\/div>\n
1. Diagram 1 shows the \u201cdesign\u201d layout of the spreadsheet. Input elements are shown in cells with<\/div>\n
a green, pink, or yellow background. These are to be typed by you. The cells with white<\/div>\n
backgrounds are formulas. Each formula element is identified with the word formula, meaning<\/div>\n
you must type the appropriate formula in that cell.<\/div>\n
2. Diagram 2 shows sample results of computations for 20 different order quantities from the<\/div>\n
sample inputs. The sample results demonstrate the type of formatting desired.<\/div>\n
3. Diagram 3 shows the economic order quantity chart.<\/div>\n
Diagram 1: Layout for CVP spreadsheet Inputs and Formulas<\/div>\n
Page 3<\/div>\n
Formula Elements:<\/div>\n
\u2022 Economic Order Quantity (Formula1) = computation using EOQ formula on page 1. Use the<\/div>\n
SQRT() function under the Formula tab, Math and Trig option. Write your formula in the box<\/div>\n
labeled \u201cNumber\u201d in the dialog.<\/div>\n
\u2022 Order Frequency (Formula2) = computation shown on page 1<\/div>\n
\u2022 Total Inventory Costs (Formula3) = computation shown on page 1<\/div>\n
\u2022 Text for Chart (Formula4) = Use concatenation to create the following text string:<\/div>\n
Economic Order Quantity = Approximately 17 Orders of 583 units<\/div>\n
where the numbers in the text string are cell references to the analysis results (computations).<\/div>\n
The concatenation function can be found under the Formulas tab, Text item on the ribbon in the<\/div>\n
Function Library group. Use\/nest the round function as appropriate.<\/div>\n
Note: When different inputs are entered, the text for chart should automatically update with new<\/div>\n
results.<\/div>\n
Formula Columns:<\/div>\n
\u2022 Analysis (Formula5) \u2013 Rows 2 through 19. Add 1 to the element in the row above.<\/div>\n
\u2022 Quantity (Formula6) \u2013 In Row 1 start with the input \u201cBeginning Quantity\u201d. In rows 2 through<\/div>\n
19, add the input \u201cQuantity Increment\u201d to the element in the row above.<\/div>\n
\u2022 Order Frequency (Formula7) \u2013 In rows 1 through 20, compute using the formula Annual<\/div>\n
Demand divided by Quantity for each Quantity.<\/div>\n
\u2022 Total Ordering Costs (Formula8) \u2013 In rows 1 through 20, compute using the formula: Order<\/div>\n
Frequency x Order Cost Per Order.<\/div>\n
\u2022 Total Carrying Costs (Formula9) \u2013 In rows 1 through 20, compute using the formula: Quantity<\/div>\n
divided by 2 x Cost Per Unit x Carrying Cost Percentage<\/div>\n
\u2022 Total Inventory Costs (Formula10) \u2013 Sum \u201cTotal Ordering Costs\u201d and \u201cTotal Carrying Costs\u201d<\/div>\n
in each row.<\/div>\n
Page 4<\/div>\n
Diagram 2: Sample Results<\/div>\n
Use the inputs (values in the yellow cells) and the results in this diagram to test your formulas.<\/div>\n
The chart (see next page) will have the quantity column as the x-axis and Total Ordering Costs, Total<\/div>\n
Carrying Costs, and Total Inventory Costs as separate series (lines) on the y-axis. To start:<\/div>\n
1) Select the Total Ordering Costs, Total Carrying Costs, and Total Inventory Costs headings and data.<\/div>\n
2) Insert a line chart. The chart will have 1, 2, 3, 4, etc. as the x-axis.<\/div>\n
3) Change the x-axis to refer to the quantity column as follows:<\/div>\n
a) Click on the chart<\/div>\n
b) Click on \u201cSelect Data\u201d button under the \u201cChart Tools | Design\u201d tab.<\/div>\n
i) In the dialog, click \u201cEdit\u201d above the panel labeled \u201cHorizontal Axis Labels\u201d<\/div>\n
(1) For the axis label range, select the data rows in the Quantity column.<\/div>\n
(2) Click OK to close the axis label range dialog<\/div>\n
c) Click OK to close the Select Data dialog.<\/div>\n
4) Edit your chart further as appropriate<\/div>\n
Page 5<\/div>\n
Diagram 3. Sample EOQ Chart<\/div>\n
CHART ELEMENTS:<\/div>\n
The basic chart elements include the title, legend, x-axis title, x-axis labels, y-axis labels; the plot of total<\/div>\n
ordering costs, total carrying costs, and total inventory costs. In addition:<\/div>\n
For the next item, search Google for \u201chow to associate a cell with text box in a chart\u201d or use the<\/div>\n
procedure demonstrated in class.<\/div>\n
\u2022 Create a text box in the chart for EOQ, then associate the contents of the \u201cText for chart\u201d formula<\/div>\n
(identified on page 3) with the text box. Format with bold font, purple color. Position this text<\/div>\n
box above the plotted graph.<\/div>\n
ANALYSIS QUESTIONS:<\/div>\n
To answer each of the analysis questions, select a row of cells below the chart to create a wide cell into<\/div>\n
which to type your answer. On the Home tab, select merge and center, then left-justify. Then type your<\/div>\n
answer.<\/div>\n
Situation #1. Suppose improved information technology will allow more automated ordering, reducing<\/div>\n
the order cost per order to $5. How will this change the recommended inventory policy and total<\/div>\n
inventory costs?<\/div>\n
Situation #2. Suppose your manager decides to place orders weekly rather than follow the recommended<\/div>\n
inventory policy. What order cost per order would make this an optimum decision? (Hint: Try different<\/div>\n
order costs per order in your spreadsheet until the order frequency is 52, which is once a week.)<\/div>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"

BUSI 2012 \u2013 Business Analytics Excel Project #1 Economic Order Quantity Analysis Purpose: The purpose of this assignment is to apply your learning from Modules 1 through 5 to solving an economic order quantity analysis. You will write formulas and create a line chart. Caution: This IS an individual project. You can get help from […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","template":"","meta":[],"disciplines":[644],"paper_types":[],"tagged":[],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/questions\/17415"}],"collection":[{"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/questions"}],"about":[{"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/types\/questions"}],"author":[{"embeddable":true,"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/comments?post=17415"}],"version-history":[{"count":0,"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/questions\/17415\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/media?parent=17415"}],"wp:term":[{"taxonomy":"disciplines","embeddable":true,"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/disciplines?post=17415"},{"taxonomy":"paper_types","embeddable":true,"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/paper_types?post=17415"},{"taxonomy":"tagged","embeddable":true,"href":"https:\/\/www.goodacademic.com\/blog\/wp-json\/wp\/v2\/tagged?post=17415"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}