Business Modeling Simulation HW1

Hints for problem 2-15

To DISCOUNT or NOT to DISCOUNT… that is the question.

Syntax of Excel IF Statement:

The syntax of If Function in Excel is as follows:

=IF (Logic_Test, Value_if_True, Value_if_False)

Here, ‘Logic_Test’ refers to the expression that is to be evaluated.

‘Value_if_True’ is the output of IF Statement if the ‘Logic_Test’ is TRUE.

‘Value_if_False’ is the output of IF Statement if the ‘Logic_Test’ is FALSE.

Use this test to determine if a customer does or does not get the 4% or the 7% discount.

Think of an equation like this: Discounted Purchase = Purchase – discount amount

Discount amount = Purchase amount * discount percentage

But what if only 63% of the customers get this particular discount? How do you “turn it off” or zero it out?

Multiply by a 1 or a 0! Create a “TEST CELL” and multiply the test result (0 or 1) by the discount amount.

Remember Excel’s random number generator returns a UNIFORM distribution by default. That means it will return a number UNIFORMLY DISTRIBUTED between 0 and 1. So… IF(RAND() < .63, 1,0) will give you a 0 if the number is .63 or above and 1 if the number is .63 or below!

HISTOGRAMS!

Many students have more problems with cut and paste then just using the histogram data function in Excel. All you need are 2 columns of numbers; the numbers you want sorted into buckets or bins and the bin spacing. After identifying these 2 columns in your spreadsheet click the DATA tab and Click Analysis. Scroll down the list and select the Histogram function near the bottom. This will open up a dialog box for running the wizard. Be sure to create the histogram within the current sheet and chart the sort results.