To Trend in Microsoft Excel or Forecast in Microsoft Excel

Download the PDF

THE MTS JOURNAL

Auction Theory, Game Theory, and Appraisal
W alter W . O’Connell, M.E., ASA, SCSP

From time to time there arises a situation in all appraisers’ lives where they find holes in a data set that they would like to use for appraisal assignments. That is to say, when working with tables with a period/time column (x-value) and data column (y-value) there is missing data. This missing
data needs to be trended or forecasted to complete the data set for proper use. Or, we just need to forecast possible future outcomes when data is not yet available, for a prospective study.
Holes in data sets between two periods of published data usually take place for one of three possible reasons. First, because a data set was not yet been received or analyzed by the data collection source. Second, because the sampling size of the data was found to be too small to allow for a high enough degree of confidence to publish a data set conclusion for that period. Lastly, because the data set was found to contain errors or was collected/reported improperly. Regardless of whether the gaps are pricing holes, cost index holes, holes in Bureau of Labor Statistics data, or holes in location
indexes there always seems to be occasional gaps in a data sets, data with delayed release, or future data not yet collected by the publishers of a data source.
Speaking with fellow appraisers as well as looking back at my own work, we seem to bounce between using the Microsoft Excel™ Forecast Function (=FORECAST) and Trend Function (=TREND). Reviewing the Microsoft Office™ support webpage (http://support.office.com), functions are categorized into fourteen (14) functional categories. Within those categories are ‘statistical functions’ which are home to the forecasting and trending tool functions offered by Microsoft Excel™.
With over one hundred functions, the statistical function category offers a variety of basic and advanced statistical tools such as: ‘testing for independence’; ‘correlation coefficients’; ‘F probability distribution’; ‘returns on gamma distribution’; and ‘the k-th percentage of values in a range’. From this, it was clear that my degrees and accreditations had not prepared me for the advanced statistical functions only a PhD in Advanced Statistics and Mathematics could understand.
To solve the issue concerning my lack of a PhD in Advanced Mathematics, I focused my search to linear trending and linear regression functions. My search was now narrowed to six functions! Of the six, I focused on functions that predicted (calculated) a future/past values using functions that used data values (y-values) with corresponding time values (x-values)
(e.g. 2006, 2007, 2008 etc.) as source data. Only two functions met that criteria: the =FORECAST function and the =TREND function.
Now that we have the two functions that use traditional x- and y- values to derive forecasted/trended results it is time to perform a side-by-side comparison of both function

Volume 32, Issue 2, 2nd Qtr 2016 14

THE M&TS JOURNAL

Forecasting/Trending Comparison
=Forecast =Trend =Trend =Trend
constant not allowed constant b = ‘Blank’ constant b = “False” constant b = “True” Data
2006 125 125 125 125

Given
2007 150 150 150 150
2008 175 175 175 175
2009 200 200 200 200
2010 250 250 250 250
2011 260 260 260 260
2012 300 300 300 300
2013 =FORECAST(A12,B5:B11, A5:A11) =TREND(C5:C11, A5:A11,A12) =TREND(D5:D11,A5:A11,A12, FALSE) =TREND(E5:E11,A5:A11,A12,TRUE)
=FORECAST(A13,B6:B12, A6:A12) =TREND(C6:C12,A6:A12,A13) =TREND(D6:D12,A6:A12,A13, FALSE) =TREND(E6:E12,A6:A12,A13,TRUE) Calculated
2014
2015 =FORECAST(A14,B7:B13, A7:A13) =TREND(C7:C13,A7:A13,A14) =TREND(D7:D13,A7:A13,A14, FALSE) =TREND(E7:E13,A7:A13,A14,TRUE)
Pass Pass Fail Pass
The =FORECAST function is straight forward, asking the user to define the given y-values (given data), x-values (periods 2006- 2012), and x-value for the period to be forecasted (2013, 2014 or 2015). The forecasted results for 2013, 2014 and 2015, using the =FORECAST function are 326, 357 and 388, respectively (below).

Forecasting/Trending Comparison
=Forecast =Trend =Trend =Trend
constant not allowed constant b = ‘Blank’ constant b = “False” constant b = “True” Data
2006 125 125 125 125

Given
2007 150 150 150 150
2008 175 175 175 175
2009 200 200 200 200
2010 250 250 250 250
2011 260 260 260 260
2012 300 300 300 300
2013 326 326 209 326
357 357 221 357 Calculated
2014
2015 388 388 231 388
Pass Pass Fail Pass
The =TREND function asks the user for the same x- and y- value data as the =FORECAST function, but also asks for one additional piece of information at the end of the statement. That final piece of information requested is the definition of the ‘Const’ or ‘Constant b’. This ‘Constant b’ is optional information that was tested by telling the function to force the ‘Constant b’ equal to zero (‘FALSE’) or telling the function to omit ‘Constant b’ (‘TRUE’) from the equation allowing the straight line to be calculated normally. In short, the ‘Constant b’ places restrictions on the equation of a straight line y = mx + b and rests the
y-intercept (the point where the slope of the line crosses the y-axis) to a point equal to zero (0).

Volume 32, Issue 2, 2nd Qtr 2016 15

THE M&TS JOURNAL

We can see when using the =TREND function with ‘Constant b’ set equal to ‘FALSE’ the y-intercept (Constant b) is reset to zero, resulting in an under stating of the calculated forecasted data (209, 221 and 231). If ‘Constant b’ is set equal to ‘TRUE’ (or omitted), the y-intercept is calculated normally. This results in the identical conclusions that are reached when using the
=FORECAST function (326, 357, and 388).
After running different periods of time (x-value) and varying data (y-value) through the above Microsoft Excel™ template which returned identical results between the =FORECAST function and =TREND function, it is evident that the MS Excel™ Forecast function (=FORECAST) and the Trend function (=TREND) report the same forecasted/trended results as long as the ‘Constant b’ is not set to zero (‘false’). Both functions, when handled properly, conclude the same results.
Happy Forecasting/Trending.
About the Author
Walter O’Connell, M.E., ASA, SCSP, Senior Consultant with Porto Leone Consulting, LLC (“PLC”) and is responsible for managing cost segregation studies and tangible asset valuations. He has provided these services to clients in a variety of industries for over ten years.
Prior to joining PLC, Walter worked in the manufacturing and distribution sectors as an Inventory Control Manager for Newell Rubbermaid (NYSE:CHX) and Marcolin S.p.A.. While working as an Inventory Control Manager, Walter specialized in Material Requirements Planning (“MRP”) and Manufacturing Resource Planning (“MRP II”), in matters of national and international purchasing, the procurement of production equipment, plant and production design, cost allocation studies, and inventory accounting.

Volume 32, Issue 2, 2nd Qtr 2016 16

THE M&TS JOURNAL

He has performed and managed cost segregation studies on hundreds of properties, including hotels, senior living facilities, manufacturing facilities, research & development facilities, office buildings, hospitals, and retail properties. Walter has experience in tangible asset valuations for tax, book, insurance placement, due diligence, and business planning purposes in the Healthcare, Hospitality, Manufacturing, Chemical, Food Processing, Cable and Telecommunications industries nationally.
Walter holds a Master of Arts degree in Economics from Montclair State University, Bachelor of Science degree in Finance and a Bachelor of Arts degree in Economics from Kean University. He is an Accredited Senior Appraiser (“ASA”) with the American Society of Appraisers, a member of the Association of Production and Inventory Control Supervisors (“APICS”), and is an Accredited Senior Cost Segregation Professional (“SCSP”) with the American Society of Cost Segregation Professionals (“ASCSP”) and is a member of the American Economic Association.

Volume 33, Issue 1, 1st Qtr 2017 8

THE MTS JOURNAL

Looking at the above three flyers we could imagine the following participants attending and bidding at each auction:
Lot of 1 Laptop Computer: Small Business, Student, Homeowner, or Boutique Computer Shop.
Lot of 100 Laptop Computers: Small Chain of Electronic Stores, School or University, Mid-Sized Company.
Lot of 1000 Laptop Computers: Large Chain of Retail Stores or National Wholesale Electronic Warehouse Company.
At our 1 Laptop Computer auction we can imagine that small businesses and/or individuals would be the auction participants. We would not expect mid-sized or large institutions to participate in such an auction. The lot size of such an auction would not interest mid-sized and large institutions looking to purchase property at a discounted price, due to the larger quantities they would need to acquire to meet the institution’s needs or discount pricing one would expect for higher volume purchases.
At the 100 Laptop Computers auction, lot size becomes too large for small institutions or individuals. Small institutions and individuals lack the resources ($), distribution network, and/or need for 100 units, even if a savings of $100 per unit is realized. Due to the quantity purchased participants would expect a discount and pay wholesale pricing due to the economies of scale that would be realized by high volume purchases of such units. Let’s call this level of purchasing “Wholesale Pricing”.
Our 1000 Laptop Computers auction would likely be restricted to those companies needing 1000 units, or those who have the large scale distribution network. Let’s call this level of purchasing “Distributor Pricing”.
As you can see, lot size and those participating in an auction will define what market level the winning bid represents. Now imagine an appraiser receives a report stating that used laptop computers recently sold at auction for $300 per unit, with no additional information detailing lot size or participants, he could conclude in error that $300 was the market price that small businesses or individuals are paying for used laptop computers.
Auction Types
While completing my research to write this article I found there are many different styles of auction. I stopped counting when I reached 24 styles. Auctions, like auction participants, are diverse and varied. State run lotteries, like lotto, are a type of auction. Charitable auctions like Chinese and Tricky Tray Auctions combine auction with raffle. There are all types of raffle, outcry, and sealed bid auctions. Bid pricing can start low ($) and move high ($), start high ($) and move low ($), keep the participants informed, or keep the participants in the dark.
No matter what the name, or style of the auction, all auctions fall into one of four types:

Volume 33, Issue 1, 1st Qtr 2017 9

THE MTS JOURNAL

  1. English Auction (outcry): An auction where bids start at a low price. Buyers call out sequentially higher prices and the participant who bids the most wins the auction.
  2. Dutch Auction (outcry): An auction where bids start at an extremely high price with the auctioneer calling out successively lower prices until someone accepts the price called.
  3. First Price Auction (sealed bid): All participants place a sealed bid. The highest bidder wins the auction and pays the bid price.
  4. Vickrey Auction (sealed bid): All bidders place a private bid. The highest bidder wins the auction, but pays the bid price of the second highest bidder.
    Even in an outcry auction, other bidders during the auction do not know the strategy or final bid ($) that each auction participant is willing to place. One would assume that the final bid, regardless of the type of auction, would be at the top end of the fair market value spectrum based on the level of trade in which the final winning participant participates. What you will see is that the final bid prices have the potential to vary widely based on the strategy employed by the participant and auction type.
    Let’s Play a Game
    Let’s see what the dynamics and outcomes would be if we played a game using consistent bidding strategies from participants in each of the four types of auction.
    Up for Auction: 2010 Drill Deep Piling Drilling Rig
    Quantity: 1 unit
    Bids: The auctioneer has set the bidding in $5,000 increments.
    Strategy: Each bidder will look to buy the rig at the lowest possible price and may not exceed the authorized maximum bid authorized by each employer.
    The Auction Participants:
    Bidder A – Wholesaler – Maximum bid authorized: $30,000 (looking to win bid and resell drill rig in the next 30 days) Bidder B – Wholesaler – Maximum bid authorized: $35,000 (looking to win bid and resell drill rig in the next 360 days) Bidder C – Retailer – Maximum bid authorized: $40,000 (looking to win bid and resell drill rig in the next 45 days) Bidder D – Retailer – Maximum bid authorized: $45,000 (looking to win bid and resell drill rig in the next 180 days)
    Bidder E – General Contractor – Maximum bid authorized: $60,000 (The drilling rig they own and use now is beginning to fail and needs to be replaced in the next four to six months)
    Bidder F – General Contractor – Maximum bid authorized: $100,000 (The Company’s current Piling Drill Rig broke yesterday! It cannot be repaired! The company is losing $10,000 a day! The company is in distress! The company does not care what the fair value is, they need to acquire a new Rig before they go out of business!)
    As you can see, Bidders A through E have their maximum bid ($) at a price level that we would expect to see at the level of trade in which they are participating. Our Wholesalers are bidding low, Retailers are bidding high, Bidder E, as an end user, is willing to pay even more than the retailers who need to buy low and sell high. But look at Bidder F! Bidder F is about to go out of business if they can’t acquire this drill rig! Bidder F is in distress! Let’s see what happens!

Volume 33, Issue 1, 1st Qtr 2017 10

THE MTS JOURNAL

  1. English Auction (outcry): An auction where bids start at a low price. Buyers call out sequentially higher prices and the participant who bids the most wins the auction.

In the English Auction, with outcry bidding starting low and moving high, our winner; Bidder F, who was authorized to bid as high as $100,000, only bids and wins at $65,000 ($5,000 above Bidder E). Even though Bidder F’s business is in distress, and could bid as high as $100,000, he only needed to bid $65,000 to ensure a winning bid. Our appraiser, not knowing Bidder F’s company was in distress, could conclude the fair market value (end user) would be at some point in the $60,000 to $65,000 range.

  1. Dutch Auction (outcry): An auction where bids start at an extremely high price with the auctioneer calling out lower successively lower prices until someone accepts the price called.

In the Dutch Auction, even with outcry bidding, Bidder F is forced to bid the full $100,000, not knowing at what price point Bidders A through E will bid. In this system Bidder F’s bid is the only bid observed by our appraiser. If not given any
additional information, our appraiser could improperly conclude that $100,000 was the fair market value (end user), greatly above the $60,000 to $65,000 range observed in the English Auction.

Volume 33, Issue 1, 1st Qtr 2017 11

THE MTS JOURNAL

  1. First Price Auction (sealed bid): All participants place a sealed bid. The highest bidder wins the auction.

In the First Price Auction, with sealed bidding, Bidder F is forced to bid the full $100,000, not knowing at what price levels Bidders A through E will bid. In this system non-winning bids are disclosed. Our Appraiser would have an opportunity to observe the spread between Bidder E’s $60,000 and F’s $100,000 winning bid. In this case our Appraiser may conclude that addition research is needed before concluding that the fair market value (end user) is $100,000.

  1. Vickrey Auction (sealed bid): All bidders place a private bid. The highest bidder wins the auction, but pays the bid price of the second highest bidder.

In the Vickrey Auction, with sealed bidding, Bidder F will bid the full $100,000, win the bid, but only pay the next highest bid of $60,000 offered by Bidder E. Our appraiser, able to see all bids, would conclude that the fair market value (end user) would be $60,000.

Volume 33, Issue 1, 1st Qtr 2017 12

THE MTS JOURNAL

In all auctions, unless a degree of chance (raffle/lottery) is built into the auction, as in a Chinese or Lottery Auction; the highest bidder will always win. But what we have observed is that the “winning bid” can vary dramatically based on the strategy employed by the participant and the type of auction run. The final winning bid of Bidder F can be represented by $60,000,
$65,000, or $100,000 based on the type of auction. If we assume that Bidder E’s maximum bid of $60,000 is the true fair market value (end user) but our appraiser improperly reports $65,000, an overstatement of 8.333%, such a mistake may never be noticed or may be considered insignificant in our assignment. But a winning bid of $100,000, an overstatement of 66.666% may cause problems for the client and/or his appraisal practice.
A number of strategies can be employed in dealing with the issue of observed variances in auction data but it is clear that before such strategies can be put in place the type of auction, level of trade, and participants of an auction must be understood and properly analyzed in any appraisal assignment.
About the Author
Walter W. O’Connell, ME, ASA, SCSP is a Senior Consultant with Porto Leone Consulting, LLC (“PLC”) and is responsible for managing cost segregation studies and tangible asset valuations. He has provided these services to clients in a variety of industries for over ten years.
Prior to joining PLC, Walter worked in the manufacturing and distribution sectors as an Inventory Control Manager for Newell Rubbermaid (NYSE:CHX) and Marcolin S.p.A.. While working as an Inventory Control Manager, Walter specialized in Material Requirements Planning (“MRP”) and Manufacturing Resource Planning (“MRP II”), in matters of national and international purchasing, the procurement of production equipment, plant and production design, cost allocation studies, and inventory accounting.
He has performed and managed cost segregation studies on hundreds of properties, including hotels, senior living facilities, manufacturing facilities, research & development facilities, office buildings, hospitals, and retail properties. Walter has experience in tangible asset valuations for tax, book, insurance placement, due diligence, and business planning purposes in the Healthcare, Hospitality, Manufacturing, Chemical, Food Processing, Cable and Telecommunications industries nationally.
Walter holds a Master of Arts degree in Economics from Montclair State University, Bachelor of Science degree in Finance and a Bachelor of Arts degree in Economics from Kean University. He is an Accredited Senior Appraiser (“ASA”) with the American Society of Appraisers, a member of the Association of Production and Inventory Control Supervisors (“APICS”), and is an Accredited Senior Cost Segregation Professional (“SCSP”) with the American Society of Cost Segregation Professionals (“ASCSP”) and is a member of the American Economic Association.