KEMBAR78
"What If" Analysis and Financial Modelling: Theory/Practical | PDF | Prices | Mathematical And Quantitative Methods (Economics)
Scribd
Upload
64 views10 pages

"What If" Analysis and Financial Modelling: Theory/Practical

This document discusses using MS-Excel to model purchasing decisions when vendors offer discounted prices for large quantities. It provides an example where the annual usage is 100 units, regular price is $50, ordering cost is $25, and holding cost is $0.35. It calculates the economic order quantity and compares the total costs of purchasing at the regular price versus a discounted price of $48. The assignment is to use goal seeking to find the discounted price that results in a total cost of $48,000 and costs for discounts of $47 and $46.

Uploaded by

Ver Dnad Jacobe
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
64 views10 pages

"What If" Analysis and Financial Modelling: Theory/Practical

This document discusses using MS-Excel to model purchasing decisions when vendors offer discounted prices for large quantities. It provides an example where the annual usage is 100 units, regular price is $50, ordering cost is $25, and holding cost is $0.35. It calculates the economic order quantity and compares the total costs of purchasing at the regular price versus a discounted price of $48. The assignment is to use goal seeking to find the discounted price that results in a total cost of $48,000 and costs for discounts of $47 and $46.

Uploaded by

Ver Dnad Jacobe
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
You are on page 1/ 10

“WHAT IF” ANALYSIS AND

FINANCIAL MODELLING:
THEORY/PRACTICAL
CHAPTER 4
Test the Arrive to
Model Simulate
model conlusion

This lesson focus on the practical use of MS-Excel


(Spreadsheet) as a tool for decision maker to use:
The Problem

 Many vendors offer discount prices for products if


purchased in large quantities. The buyers can save
on the product price, but they have to pay for
carrying large inventories.
 Theformulas (or mathematical model) for
determining whether to accept or reject discount
offer are not too complicated. However, they may
not be found in may commercial application
software packages.
 Writing these formulas with MS-Excel takes only a few
minutes. Furthermore, a “what-if” analysis can easily be
performed. The decision is determine by the following
variables:
 Annual usage (units per year)
 Unit price
 Cost per order (fixed)
 Holding cost (for each dollar value of inventory, in dollars per year)
 The minimum quantity that must be purchased in order to receive a
discount
 The discounted price.
Example
 In this example, the data for our case are:
 Annual usage = 100 units
 Unit price = $50.00
 Ordering cost = $25.00 per order
 Holding cost = $0.35 per order value in inventory,
per year
 The discount price offer = $48.00
To solve this problem, Economic Order
Quantity (EOQ) model/formula is used.
The formula:

2 (annual usage) (ordering cost)


EOQ = (unit price) (holding cost)

Developing the model involved the following steps:


Cell Formula Explanation
E12 EOQ =SQRT(2*E5*E7/(E6*E8)) Calculates the EOQ value
E13 Actual Ordering Cost =ROUND(E12,0) Round the result of EOQ
E14 Number of Orders =E5/E13 The number of orders placed
annually by dividing the
annual usage by EOQ
E15 Average Inventory =E13/2 The average inventory is
equal to half of EOQ
E17 Order Cost =E7*E14 Multiply the order cost by the
number of orders per year
E18 Holding Cost =E15*E6*E8 Multiplies average inventory
times the unit cost times
holding cost per unit
E19 Purchase Cost =E6*E5 Multiplies the unit cost by the
annual usage
E20 Total Cost =SUM(E17..E19) Total all the Annual Costs
Other Information:
Unit price in cell G6, must be entered
directly or with a formula (E6*.96).
Minimum amount for purchased to be
ordered is also entered in cell G9
(500).
In G13: enter this formula:
IF(G12>=G9,ROUND(G12,0),G9)
ASSIGNMENT: Goal Seeking

Find the discounted price for minimum number of


purchases that contribute to the total cost to be
$48,000. Do a Trial and Error or through goal
seeking also for discount price of $47.00 (750 units
of actual order quantity) and $46.00 (1000 units of
actual order quantity).

You might also like