Excel Formula Adjustment for Variable and Fixed Costs, Slides of Marketing

Download Excel Formula Adjustment for Variable and Fixed Costs and more Slides Marketing in PDF only on Docsity!

Excel Proficiency

Exercises

E XCEL R EVIEW

1. Multiplication Table Problem

Relative, Absolute, and Mixed Addressing

The Exercise

Create a 10x10 multiplication table in a spreadsheet, as shown below. The cells inside the table (i.e., within the black border) should contain only formulas, not numbers. You should find it unnecessary to enter more than a single formula, which can be drag- copied to fill the rest of the table.

1 2 3 4 5 6 7 8 9 10 1 1 2 3 4 5 6 7 8 9 10 2 2 4 6 8 10 12 14 16 18 20 3 3 6 9 12 15 18 21 24 27 30 4 4 8 12 16 20 24 28 32 36 40 5 5 10 15 20 25 30 35 40 45 50 6 6 12 18 24 30 36 42 48 54 60 7 7 14 21 28 35 42 49 56 63 70 8 8 16 24 32 40 48 56 64 72 80 9 9 18 27 36 45 54 63 72 81 90 10^10 20 30 40 50 60 70 80 90

Notes

The principle behind completing this multiplication table is simple. You want a formula in each cell of the table matrix that multiplies the value in that cell’s column header by that cell’s row header. The trick is to write a single formula (a “master formula”) that can be copied into all the matrix cells and is valid for each one.

Solving this problem by writing a single formula requires that you understand Excel’s mixed addressing feature. Note that mixed addressing comes into play only when a formula is copied, as we’re doing here. So that’s the only time you need to concern yourself with it.

Before you tackle mixed addressing, you should first understand Excel’s related addressing options: relative and absolute. Excel’s default is relative addressing. That is, cell references contained within a formula that’s copied are adjusted in the copy relative to their position in the spreadsheet. Fixed addressing is the opposite. As its name implies, a fixed reference, when copied as part of a formula, does not change.

Excel uses a dollar sign ($) to indicate that a reference is fixed. For example, the cell reference A1 (without dollar signs) is relative, whereas $A$1 (with dollar signs) is fixed. Mixed addressing occurs when either the column reference or the row reference is fixed, but not both. For example, $A1 is a mixed reference where the column A is fixed but not the row and A$1 is a mixed reference where the row 1 is fixed but not the column.

For our multiplication table problem, it will satisfy the requirements of the upper-left- hand cell of the matrix if we write a formula that multiplies the value in the column

and in the second column, the formulas are: =C1B =C2B =C3B =C4B** and so on.

Even though the original formula gave the correct value for the upper-left-hand cell of the matrix, that formula was insufficient when we wanted to copy it to fill the rest of the matrix cells.

So how can we properly fill the matrix? One way is to write an individual formula for each cell in the matrix. But the much more efficient way called for in this exercise is to modify the relative cell references in the original formula before copying it so each copied formula references the correct values for its location in the matrix.

Using Absolute Addressing If the original formula is =B1A2* then which references shall we fix? Making both cell references absolute , the formula becomes =$B$1$A$2.* However, the result of copying that formula is incorrect, putting exactly the same formula in each cell of the matrix:

A B C D E F 1 1 2 3 4 5 2 1 1 1 1 1 1 (^3) 2 1 1 1 1 1 4 3 1 1 1 1 1

So using absolute addressing in this case doesn’t work either.

Using Mixed Addressing Instead of using relative or absolute addressing, mixed addressing will solve our problem. Imagine that we start by copying the original formula from the upper-left- hand cell down the first (left-hand) column. In the first cell reference of the original formula (B1) we need to fix the row 1, but not necessarily the column B. So that adjusted cell reference reads B$1 and the formula reads =B$1A2*. Copying that formula down the first column yields correct answers in the first column. So far so good.

A B C D E F 1 1 2 3 4 5 2 1 1 3 2 2 4 3 3 5 4 4 6 5 5 7 6 6

The next step is to copy the formulas in the first column to the columns to their right. When we do so the values in the second column (column C) look OK, but the subsequent columns contain incorrect values.

A B C D E F 1 1 2 3 4 5 2 1 1 2 6 24 120 3 2 2 4 12 48 240 4 3 3 6 18 72 360 5 4 4 8 24 96 480 6 5 5 10 30 120 600 (^7) 6 6 12 36 144 720

Again, our downfall is relative addressing, this time in the second cell reference of the formula. Looking in column F, for example, the copied formulas read:

=F$1E =F$1E =F$1E* and so on.

Using Mixed Addressing Correctly To correct this error, we go back to the original formula. In that formula, the second cell reference must be fixed as to column. So the formula =B$1A2* must be adjusted to read =B$1$A2*. Copying this corrected formula from the upper-left-hand cell to the rest of the cells in the matrix results in correct values in all the columns:

1 2 3 4 5 6 7 8 9 10 1 1 2 3 4 5 6 7 8 9 10 2 2 4 6 8 10 12 14 16 18 20 3^3 6 9 12 15 18 21 24 27 4 4 8 12 16 20 24 28 32 36 40 5 5 10 15 20 25 30 35 40 45 50 6 6 12 18 24 30 36 42 48 54 60 7 7 14 21 28 35 42 49 56 63 70 8 8 16 24 32 40 48 56 64 72 80 9 9 18 27 36 45 54 63 72 81 90 10 10 20 30 40 50 60 70 80 90 100

For more information on relative, fixed, and mixed addressing see Excel’s online help on the topic Move or copy a formula.

2. Olive Oil Pricing Problem

IF Statements, SUMPRODUCT Function, MIN Function

The Exercise

Olive oil can be purchased according to this price schedule:

For the first 500 gallons $23 per gallon For any of the next 500 gallons $20 per gallon For any oil beyond 1,000 gallons $15 per gallon

Create a spreadsheet that will calculate the total price of buying x gallons of oil, where x is a number to be entered into a cell on the spreadsheet.

Notes

Creating this spreadsheet requires the use of Excel’s powerful IF statement. The syntax of the IF statement is a three-part “if-then-else” format. If the test condition (the first parameter) evaluates to true, the value-if-condition-true (the second parameter) is returned. But if the test condition evaluates to false, the value-if-condition-false (the third parameter) is returned. =IF(condition-to-test, value-if-condition-true, value-if-condition-false)

A simple example: =IF(Sky-is-blue, sunny-day, cloudy-day)

IF statements can be nested, although to retain clarity in your work it’s generally not a good idea to nest them to very many levels. A nested IF statement (where the nested IF takes the place of the value-if-condition-false in the original IF statement) might look like this: =IF(condition-to-test, value-if-condition-true, IF(condition-to-test, value-if- condition-true, value-if-condition-false))

Continuing our weather example, a nested IF statement might read like this: =IF(Sky-is-blue, sunny-day, IF(Temp<32, Maybe-snow, Maybe-rain))

The olive oil pricing exercise specifies three oil prices based on quantity ($23/gallon for up to 500 gallons, $20/gallon for 501-1,000 gallons, and $15/gallon for 1,001 or more gallons) and has as its variable x gallons of oil to buy.

An Initial, Simple Scenario The Solutions.xls spreadsheet begins by presenting a solution to a simpler version of the exercise that requires only a single IF statement. This version supposes 3 values for x that account for three quantity possibilities (1,600, 483, and 2001 gallons). But instead of three pricing levels, the simpler version has only two: first 500 gallons and any amount over 500. Because there are only two pricing levels in this version, a simple IF statement can be used to calculate the cost at these three levels.

If the price schedule were: first 500 gallons at $ 23. additional gallons at $ 20.

Cost of 1600 gallons is (^) $33,500. Cost of 483 gallons is $11,109. Cost of 2001 gallons is $41,520.

Assigning names to key variables makes it easier to read the IF formula. I’ll assign these names: Quantity1 to the cell for gallons that holds 1, Price_for_500 to the cell for price that holds $23. First500 to the cell for quantity that holds 500 Price_for_Over500 to the cell for price that holds $20.

Then we can write an IF statement that reads like this:

=IF(Quantity1<=First500,Price_for_500Quantity1, First500Price_for_500 + (Quantity1-First500)Price_for_Over500)*

Below is the same formula but formatted and with then and else added to better see the IF logic:

=IF(Quantity1<=First500, then Price_for_500Quantity1,* else First500Price_for_500 + (Quantity1- First500)Price_for_Over500)**

The result of the IF statement for 1,600 gallons is $33,500.00. If you insert absolute (or mixed) addressing in the first formula where needed, you can copy the first IF statement down the column to also solve for the given quantities of 483 and 2,001 gallons.

The Actual Problem Scenario The second scenario on the “Oil prices” tab of the Solutions.xls spreadsheet is more complex and reflects the actual problem. In this scenario, there are three price levels (not two) with any amount over 1,000 gallons priced at $15.00/gallon. Again, the challenge is

If the price schedule were: first 500 gallons at $ 23. additional gallons at $ 20.

Cost of 1600 gallons is (^) $33,500. Cost of 483 gallons is $11,109. Cost of 2001 gallons is $41,520.

A Closer Look at the SUMPRODUCT Part of the Formula Along with using a nested IF statement, our formula also makes use of the powerful Excel built-in function named SUMPRODUCT. The syntax for the SUMPRODUCT function is:

=SUMPRODUCT(array1,array2,array3,…)

The SUMPRODUCT part of our formula reads like this:

SUMPRODUCT(First500:Next500, Price_for_500:Price_for>_500)

and means:

• Multiply First500 by Price_for_500 (or 500*$23.00) to get value1.
• Multiply Next500 by Price_for>_500 (or 500*$20.00) to get value2.
• Add together value1 and value2.

As you can see, the SUMPRODUCT function takes two or more arrays as its parameters. It multiples the first value in the first array by the first value in the second array, the second value in the first array by the second value in the second array, and so on. When all the multiplication operations across arrays are complete, the results are added together.

An Alternative Calculation Method As is generally always the case with Excel, there’s more than one good way to model this problem. The Solutions.xls worksheet shows an alternative method that again makes use of nested IF statements and SUMPRODUCT as well as the MIN function.

Alternately 1600 483 2001 gallons gallons gallons For the price schedule in the problem: gals/price level gals/price level gals/price level first 500 gallons at $ 23.00 500 483 500

1600 483 2001 gallons gallons gallons gals/price level gals/price level gals/price level 500 483 500 500 0 500 600 0 1,

Alternately 1600 483 2001 gallons gallons gallons For the price schedule in the problem: gals/price level gals/price level gals/price level first 500 gallons at $ 23.00 500 483 500 next 500 gallons at $ 20.00 500 0 500 any additional gallons at $ 15.00 0 0 0 Cost $ 21,500.00 $ 11,109.00 $ 21,500.

How does this alternative scenario work?

Again, we have all the information in the problem about quantity and price levels entered into the spreadsheet. Again, we want to find the cost for three different quantities: 1,600 gallons, 483 gallons, and 2,001 gallons.

However, in this alternative scenario, the first thing we do is determine for each quantity (1,600, 483, 2,001) how many gallons are involved compared to the first quantity/price level (500 gallons at $23.00/gallon). That calculation uses the MIN function and is located in the layout as follows:

Using values for quantity instead of cell references (just for clarity here) our three formulas are: =MIN(1600,First500) which yields 500 =MIN(483,First500) which yields 483 =MIN(2001,First500) which yields 500

The next stage of processing is handled with IF statements that have the logic described below. The IF statement here handles the 2 nd^ row for the quantity of 1,600 gallons (Quantity1). That is, the case of quantities up to 1,000 gallons.

=IF(Quantity1<=(first500), expressed as SUM(first500:first500) 0, IF(Quantity1>=SUM(first500, next500), next500, Quantity1-first500) expressed as sum(first500:first500)

Examine the formula on the “Olive Oil” tab in the Solutions.xls spreadsheet to see exactly how the formula is written.

The “Olive Oil” tab from the

Solutions.xls

workbook.

3. Web Service Problem

Forecasting and Charting

The Exercise

You have an idea for a new web service that offers customized workouts for subscribers. Build a spreadsheet to calculate:

1. the number of new customers each month, and
2. the total customer base (cumulative number of customers signed up) each month. Model projections for 60 months using these two scenarios:
3. Total market potential is 1,000,000 customers. Each month you sign up 2% of customers in the market that have not yet signed up.
4. Total market potential is initially 1,000,000 customers but grows at 1% per month. Each month you sign up 2% of customers in the market that have not yet signed up. Use Excel’s Chart Wizard wizard to make a graph comparing the total customer base under each of the two scenarios. What type of graph is appropriate?

Notes

The First Scenario The first scenario can be laid out as follows:

Note that at the top of the scenario we enter the two key values ( total market potential and % remaining captured/period ), with labels. We’ll use these values in the formulas to calculate the number of new customers and it’s important to use cell references (and not the actual values) in those formulas. By using cell references in the formulas, we can easily change the two key values and have the change reflected automatically in all calculations that depend on them.

Scenario A Total market potential 1,000, % remaining captured/period 2%

Period New customers Total custom 0 - - 1 20,000 20, 2 19,600 39, 3 19,208 58, 4 18,824 77, 5 18,447 96, 6 18,078 114, 7 17,717 131, 8 17,363 149, 9 17,015 166,

Constant market

Customer Base Comparison Growing market

Period

cell references in the formulas, we can easily change the key values if necessary and have the change reflected automatically in all calculations that depend on them.

At the start (Period 0) we have, of course, no new customers and no total customers. In the first month (Period 1), the Total Market formula is a reference to the cell at the top of the scenario that holds the “initial total market”, or 1,000,000. Then, calculations are as follows.

The New Customers calculation: = (Total_Market_This_Period – Total_Customers_Previous_Period) * Percent_remaining_captured/period

The Total Customers calculation: =Total_Customers_Previous_Period + New_Customers_This_Period

The Total Market calculation (beginning with Period 2): =Total_Market_Previous_Period * (1+Market_size_growth/period)

Again, we can copy the initial “master” formulas down the columns to complete the calculations for all 60 periods, taking care to use absolute addressing in the master formulas where required.

The Graph

The final part of this problem is to create a graph to compare the total customer base for

the two scenarios. Because Excel has so many graph types, you might wonder which type is most appropriate for this data. The line graph shown below is especially effective for plotting data over time.

One way to easily create a graph like this is to select the column of values for “Total Customers” for the first scenario and then select Excel’s Chart Wizard to plot a line graph.

Then copy the values from the “Total Customers” column in the second scenario and paste those values into the graph to add the second line.

Select the graph and return to the Chart Wizard to modify the legend labels, add a title, change the orientation and/or scale of axis labels, and so on.

Excel’s online help provides descriptions and examples of the various chart types available to you.

To get started with charts see Excel’s online help on the topic Create a chart. More advanced online help chart topics that may be helpful include Select a different chart type , Troubleshoot charts , and About formatting charts.

A partial view of the “Web Service” tab from the Solutions.xls workbook.

Assumptions: Regular Unit sales, first year 1, Unit price, first year $1, Unit manufacturing cost $1, Yearly sales growth 100% Yearly price fall 15% Yearly decline in manufacturing costs 6% Marketing expense as % of revenue 14% Yearly fixed cost $1,000, Discount rate 15%

4. Pro Forma Problem

Forecasting, Data Table, Goal Seek

The Exercise

You have founded a company to sell thin client computers to the food processing industry for Internet transaction processing. Before investing in your new company, a venture capitalist has asked for a five year pro-forma income statement showing unit sales, revenue, total variable cost, marketing expense, fixed cost, and profit before tax.

You expect to sell 1,600 units of the thin client computers in the first year for $1,800 each. Swept along by Internet growth, you expect to double unit sales each year for the next five years. However, competition will force a 15% decline in price each year.

Fortunately, technical progress allows initial variable manufacturing costs of $1,000 for each unit to decline by 6% per year. Fixed costs are estimated to be $1,000,000 per year. Marketing expense is projected to be 14% of annual revenue. When it becomes profitable to do so, you will lease an automated assembly machine that reduces variable manufacturing costs by 20% but doubles the annual fixed cost; the new variable manufacturing cost will also decline by 6% per year. Net present value (NPV) will be used to aggregate the stream of annual profits, discounted at 15% per year. 1 a) Ignoring tax considerations, build a spreadsheet for the venture capitalist. b) How many units do you need to sell in the first year to break even in the first year? c) How many units do you need to sell in the first year to break even in the second year?

Notes

One of the biggest challenge in a problem of this sort is to organize the quantity of information, not to mention also establishing the correct relationships between values and presenting the information in a meaningful way. You’ll probably find that it’s most useful to start by entering into a spreadsheet everything you know about the problem. Don’t worry too much at first about the spreadsheet’s layout. First just get the data entered, since you can always change the layout later as needed.

Start by entering the problem’s initial assumptions into the spreadsheet:

(^1) Problem 2-5 from Introductory Management Science, 5 th (^) edition (1998), Eppen, Gould, et al.