Forecasting by regression is appropriate when a number of factors influence a variable of interest.

Lindon Robison

Learning goals. At the end of this chapter, you should be able to: [1] distinguish between deterministic and statistical forecasts; [2] recognize the difference between endogenous and exogenous variable forecasts; [3] recognize the difference between dependent and independent variables in statistical forecasts; [4] recognize the value of quantitative forecasts; [5] understand how to apply economic theory to guide forecasts, and [6] be able to complete present value [PV] models by forecasting endogenous and exogenous variables.

Learning objectives. To achieve your learning goals, you should complete the following objectives:

• Understand the difference between forecasts and predictions.
• Be able to recognize different kinds of deterministic and statistical forecasts.
• Recognize the difference between exogenous and endogenous variables and forecasts.
• Distinguish between independent and dependent variables in regression equations.
• Learn the difference between quantitative forecasts and qualitative predictions.
• Understand why present value [PV] models require cash receipts and cash expense forecasts.
• Learn how to find forecast confidence intervals.
• Utilize information about time and use costs to forecast cash costs of goods sold [COGS] and overhead expenses [OEs].
• Understand how to use correlations among variables to make forecasts.
• Practice forecasting techniques for simple investment problems.

[1]

Forecasts and predictions. This chapter is about forecasting. Forecasts use observations and perceptions about the past to predict the future. The origin of the word forecast comes from the word “fore” which means before plus the word “casten” which means to scheme, plan, or prepare. Thus, when we forecast, we predict something before it happens so that we can plan or prepare for it.

Related to forecasts are predictions. A prediction is also a statement about what someone believes will happen in the future. We sometimes distinguish between predictions and forecasts based on how we arrived at our estimate of the future. A prediction is a guess, less scientific, and more subjective than a forecast. In this view, predictions are more eclectic in the information they collect and use to describe the future–including past observations, informed opinions, conditional relationships, signs, impressions, and other foreshadowings of the future.

Everyday forecasts. We all make forecasts. We depend on them. Indeed, nearly every choice we make is preceded by a forecast. We are so dependent on our almost “every moment” forecasts that we may give them little notice. For example, we may give little thought to our forecast that if we turn on the water faucet, then water will pour out. Still our forecast precedes and motivates our act of turning on the faucet.

The relative importance of forecasts. What makes some forecasts more important than others are when they lead to significant resource commitments and are costly if not impossible to reverse. Buying a house, a new car, or choosing one’s companion may fall into the category of important forecasts. When making important forecasts, we may need to make several related forecasts. In the case of buying a car, we may want to forecast its frequency of repairs, its fuel efficiency, its riding comfort, and rather it impresses our partner. To improve our car-related forecasts, we may visit with friends who have made similar purchases. We may read professional car buying guides that evaluate the performance and cost of alternative cars of interest. We may even rent the car of interest to experience the car upfront and personal. But at the end of the day, we forecast our car purchase experience and make our choice—and live with the results.

Forecast accuracy. The accuracy of our forecasts varies depending on the quality of information and analysis behind them and the time distance to the future forecast. Consider the following. Because we have a long history of turning on the water faucet and experiencing the flow of water, we may be nearly certain of the relationship between turning on the faucet and experiencing the flow of water. In this case, the accuracy of our forecast is high. On the other hand, we may have heard that an eatery serves a tasty Rueben sandwich and based on the “expert” opinions of our friends, we may forecast that if we eat the Rueben at the eatery, we will be pleased with the experience. In this case the accuracy of our forecast is moderate. [Who is to say that our friends have the same tastes in sandwiches that we do?] Some forecasts are embedded in advertisements—that by consuming certain products, you will become popular and rich and your skin blemishes will disappear. These forecasts should be questioned unless supported by double-blind studies—because they are not motivated to report accurate results.

Forecasting, a two-step procedure. Forecasting is a two-step procedure. In the first step, we collect and analyze data to discover variables are related to each other over time. Suppose we observe an exogenous variable at time t, xt, and find that it is significantly correlated with the same variable in the previous periods. Then we could write: xt = f[xt-1,xt-2,⋯] + εt where εt is an error term that represents what is not explained by the exogenous variable in the current and adjacent time periods.

Suppose that in the first step, we forecast a value for xt that we designate as x̂t since it is an estimate because we don’t know what the actual value is. Then to estimate two periods in the future we solve using coefficient we found in step one to find: x̂t+1 = f[x̂t,xt-1,⋯]. In the case that x̂t+1 – x̂t = c then our forecast k periods in the future is: x̂t+k = x̂t + kc.

What follows. In what follows, we distinguish between different forecast equations based on the nearly certain information that provides the basis for our forecasts. While all forecast equations provide estimates of the future, some of them are derived deterministically and some statistically. In the case of statistically estimated forecast equations, it will be important to distinguish between dependent and independent variables. When describing the kinds of variables forecast, we will use the earlier language of Chapters 5 and 7, to distinguish between exogenous and endogenous variables. The purpose of this chapter is, at the end of the day, to find the forecasted endogenous and exogenous variables required to complete PV models. Questions at the end of the chapter can be used to practice and gain some forecasting experience.

Exogenous and endogenous variables. Exogenous variables are defined outside of a system. Endogenous variables are defined within the system. When we constructed coordinated financial statements [CFS] and PV templates, we distinguished between variables whose values were determined within the CFS and PV template, endogenous variables, and those variables whose values were determined outside of the CFS and PV template, exogenous variables. Within both the CFS system and PV templates, endogenous and exogenous variables are related to each other using deterministic equations.

Dependent and independent variables. When conducting experiments, it is common to use the language of dependent and independent variables. Independent variables are controlled by the experimenter and varied to determine their influence on dependent variables related to independent variables by statistical models. However, sometimes the relationship between dependent and independent variables is not determined experimentally but inferred and observed. This is the sense that we use dependent and independent variables in forecast equation.

Independent variables and what do we know for sure? We require independent variables to forecast dependent variables. Our independent variables are those used as exogenous variables in the CFS and PV templates used to determine endogenous variables. So, what independent variables do we know for sure? There are only two things that we know for sure that can be used as independent variables in our forecast equation: the time distance to a future date and the values of variables already realized, including lagged values of the dependent variable. If we were to include a contemporaneous variable to our independent variables in our forecast equation, we would need to forecast it as well as the dependent variable. This sometimes occurs when large forecast models forecast several dependent variables using multiple equations. In our discussion, we limit our forecasts to single equations. Finally, our confidence in the correlation between our dependent variables and time and lagged values increases with the quality and quantity of information used to infer the correlations.

Still, human behavior can often be predictable—we often act the same way in similar circumstances, and we develop habits that seem ingrained. We often use these tendencies to aid us in our forecasts. One important category of events that we use to guide our forecasts are commitments and covenants to perform in a predictable way in the future. For example, most have borrowed and agreed to repay on loans following specified terms. These performance commitments can be treated as nearly time dependent exogenous variables.

Forecasting endogenous variables. While forecasts mostly focus on exogenous variables when used within CFS and PV templates, we may find it useful to forecast endogenous variables. Forecasting endogenous variables is usually not necessary because they are determined by relationships established within the CFS and PV template. However, we recognize that our exogenous variables are estimated with error so that endogenous variable forecasts derived from the exogenous variable forecasts also contain errors. As a result, we may find it useful to over-identify some endogenous variables and a check on accuracy of our exogenous variables.

So, what have we learned? We learned that system variables may be either exogenous variables determined outside of the system or endogenous variables determined within the system by exogenous variables. Exogenous variables used to determine endogenous variables are dependent variables forecast by statistical or other models using independent variables that have been observed or that are highly correlated with the passage of time. In sum, we are mostly interested in forecasting exogenous variables [for our purposes] to use in CFS or PV templates. While we could technically, forecast endogenous variables, and sometimes we do, they are not our primary concern because they are determined within the CFS or PV templates.

In what follows, we will organize our forecast equation around what we know for sure. As a result, some forecast equations depend only on the passage of time. Others depend on the lagged values of a dependent variables. And still others depend on the lagged values of independent variables. Finally, we may create hybrid forecast equations that combine time and lagged values of dependent and independent variables. Forecasts that combine contemporaneous independent variables require accompanying equations that forecast their values—forecast equations in which they are the dependent variables.

Trend lines. Consider forecasting exogenous variables that are highly correlated with or are dependent on the passage of time. Time may enter the forecast equation in several different forms, but to forecast future periods only requires time as the independent variable. An example may be an equation that forecasts the condition of a building’s roof k periods in the future. The condition of the roof and its remaining life might both be expressed as a function of the roof’s age. Consider such an expression:

A roof’s remaining useful life equals its useful life when constructed – it age.

Forecast equations that depend mostly on the passage of time are often referred to as trend lines. In this case, the endogenous variable y depends only on a constant and the independent variable t. This forecast equation can be expressed as:

[11.1]  

The trend line forecast k periods in the can simply be written as:

[11.2]  

In equation [11.2], the hat over the endogenous variable signifies that we have an estimate, not an actual value.

Lagged dependent variable. Suppose that Lon wants to forecast the demand for his services next season. His forecast might begin with the demand for his services he experienced during the previous period. One reason to emphasize the demand for Lon’s services in the previous period is because so many conditions that existed last year will exist this year. To reflect the connection between this year’s forecast and what happened last year, we specify:

Services in period t = services provided in period [t – 1] + an error term

To complicate our forecasts, we recognize that the change in demand for Lon’s services in period t will depend on the number of new lawn care providers [Lon’s competition] plus change in the number of potential customers measured by the number of new households in Lon’s service area with a yard. Call these changes in the demand less changes increases in services provided by others equal to change in services ∆st and write the result as:

Services in period t =services provided in period t – 1 + ∆st + error.

Distributed lags of independent variables. Closely related to forecasts that depend on the passage of time and lagged values of depend variables are lagged values of independent variable on which the dependent variable depends. This forecast equation is relevant when the dependent variable depends on the outcome of other independent variables. Some variables such as costs of goods sold [COGS], depend on production activities in the previous period. So, if we intend to forecast COGS, we may want to relate them to some function of production activities in the previous period.

COGS this period = some function of production activity levels in the previous period.

A popular forecast equation that combines both the lagged dependent variable and independent variables is the infinite geometric distributed lag [IGL] forecast equation. We derive it next.

Suppose we want to forecast a dependent variable at the end of period t, yt, whose value we assume depends on the infinite lagged values of an independent variable z, zt-1,zt-2,zt-3,⋯ weighted by the geometric factor |ρ|COGS. OE are costs difficult to assign to any one production activity and vary over time. In our simplified analysis, we did not assume any OE. This is an unreasonable assumption since such fixed costs such as insurance, rental of office and storage space, and licenses all produce fixed costs. In the previous four years, fixed costs were $350, $390, $450, $515. In addition, in the most recent two years, Lon was required to rent addition space that cost $100 in each period. We use the OE observations and estimate the following equation using our observed data.

[11.24]  

The way our forecasts work in practice is that we find some historical basis for the equation and from those relationships estimate our coefficients.

Now we illustrate the various forecasting methods using the data in our example template. To begin, suppose that we are confident in the cash flow data appearing in year two since year one may be a transition year. Dividing cash COGS by cash sales we find:

[11.25]  

As a result, in the future, we will assume that COGS equal 78% of whatever we estimate sales to equal. Whatever we estimate sales to equal, we will assume that COGS equal

Other forecasts. Sometimes, failing to find forecasts of direct interest to the calculation of our investment’s NPV, AE, and IRR—we can often find forecasts highly correlated with our forecasts of interest. For example, suppose we are interested in the price of hard red winter wheat but can only observe past prices of corn and soybeans. Finding past relationships between the price of hard red winter wheat and corn and soybean prices, we can use forecasts of corn and soybean prices to forecast future prices of hard red winter wheat.

Real versus nominal data values. In our PV templates, we project CR, cash COGS and cash OE. We can employ several assumptions about future cash flow associated with these values. The first one is that there is no change from data entered in year one. This assumption is consistent with capitalization formula that assume zero change in future cash flow. It may also be relevant if the model assumes that data are in real numbers so that numbers do not change with inflation. Furthermore, we may assume that the future is represented by the present and these relationships between variables in the present should be preserved.

Testing for robustness. That we populate our PV templates with estimates suggests that our templates will not be completely accurate. They will be consistent because the templates relate variables to each other consistently, but they are not likely to be completely accurate. We simply are not likely to estimate all of the future values of our variables correctly. As a result, we should calculate NPV, AE, and IRR under a variety of assumptions about future values so that we have some impression about the robustness of our estimates.

So, what have we learned? We have learned that forecasting recognizes two different kinds of statements. The first of these is of the form: if t then yt. From this relationship between t and yt, we make forecasts that include linear trend lines and other more complicated functions of time. Other kinds of forecast depend on lagged values of the dependent and independent variables.

We have been careful to distinguish between dependent and independent variables in forecast equations and exogenous and endogenous variables in CFS and PV templates. In the latter case, the relationship between exogenous and endogenous variables are deterministic that depend on accounting identities. The problem occurs that when we forecast exogenous variables, these are estimated with error that implies that our endogenous variables in CFS and PV templates are also estimated with error. Then it may be useful to find overidentified estimates of our endogenous variables to validate our exogenous variable forecasts—using the structure of our CFS and PV templates to check for accuracy. These require that we forecast our endogenous variables as well as our exogenous variables using economic and other logic to guide our estimation of endogenous variables. This type of estimation, like other forecasts will be of the form if xt then yt.

Not everything we know can be reduced to a number. If all the lessons learned from the past could be translated into a quantitative forecast function, we could leave much of forecasting to computers [they already carry a significant forecasting role]. But such is not the case. We all know much more that can be represented by a numerical forecast. When historical data is not available, we generally employ qualitative forecasting techniques that most often depend on the judgment of experts—that may be aided by quantitative forecasts.

Experts and all the rest of us observe, absorb, and process informative through our multiple sources and senses. So how do we do it? We may not be able to describe the process because our intuitive sense are complicated and profound. Book like “Thinking Fast and Slow” try to describe some aspects of our capacity to analyze and predict using information much more sophisticated that has been described in this chapter. This is not to disrespect the amazing information captured by digital coding, especially in the sciences, but when it comes to the “animal spirits” that operate in interpersonal interaction, it pays to leave room for qualitative judgments and forecasts.

So, we employ and appreciate professional forecasters to share their qualitative forecasts, sometimes augmented by quantitative forecasts. In may be the case that we will increasing translate qualitative forecasts into quantitative ones—and multitude are the examples of qualitative forecasts being improved using quantitative information.

Professional forecasts are usually experts in their specialty such a yields and prices of agricultural and forestry products as well as margins between revenue and cost. An example of expert forecast services are those offered by the Food and Agricultural Policy Research Institute [FAPRI] at the University of Missouri. FAPRI makes annual 10-year baseline projections for grain, oilseed, livestock markets, and a variety of other indicators. Even here, it is important to moderate forecasts using microeconomic principles and experience in making projections including a range of scenarios. Understanding intermediate and longer-term industry supply curves including regional and international dimensions is very important for most projects. Extension specialists, such as Dr. Jim Hilker, who have distinguished themselves with accurate forecasts can also be an invaluable resource.

One advantage of professional forecasts is that they account for so many influences that uncomplicated forecasters may ignore. The other advantage is that when we present our forecasts and responses to our forecasts are important—it is always helpful to have a reliable source for support.

Forecasts and present value models. While this chapter is about forecasting, we are most interested in forecasting PV model exogenous variables. Solving PV models requires that we forecast the future value of cash flow generated by an investment. PV models include exogenous and endogenous variables.

PV models discount future cash flows to find their equivalent worth in the present—since it is in the present where we live and make decisions. To repeat for emphasis, we cannot solve PV models without forecasting future exogenous variable cash flow on which the present value of an investment depends. When we invest in the present, all we know for sure is the cost of the investment, the time distance to different futures, lagged values of endogenous and exogenous variables, and future commitments like loan terms. Every other piece of data requires us to solve the PV problem and exogenous variable forecasts.

To illustrate, the template below is identified as: PV Template: Green & White Services. It will be helpful in our forecasting discussions in this chapter to continue using Lon’s yard care investment example. We will embellish it somewhat to focus on certain aspects of forecasting while maintaining the main characteristics of the problem.

Data for Green & White Services. Looking to the future, Lon is considering the purchase of a lawn care/snow removal business. He will purchase the good will [customer base] of the previous owner of the business and equipment for $40,000. His expected service calls, service prices and variable cost per service are reported below:

Forecast number of services are described Table 11.10 column B. Prices and cost per service forecasts are described in columns C and D and when multiplied by projected services produce sales revenue in column E and expenses in column H. Note that these are accumulated sales and costs, not cash. The sum of accounts receivable [AR] and inventory [INV] at the end of the period are reported in column F while the sum of accounts payable [AP] and accrued liabilities [AL] are reported in column I. Cash receipts [CR] and cash expenses [CE] are found by adjusting sales and expenses for changes in asset and liability accounts and the results are recorded in columns G and J respectively. Allowable depreciation rates are recorded in column K and when multiplied by the capital asset purchase of $40,000 produce depreciation amounts recorded in M. Finally, debt outstanding is recorded in Column N.

Besides the annual data forecasts, we also record certain rate forecasts for Green & White Services. Some of these as well as some of the annual forecasts may be either exogenous or endogenous variables depending on how the endogenous defining equations are define. We include the current exogenous rates as:

We now ask, where can we find future values for template variables that will permit us to solve multi-period PV problems? The answer is that we forecast or estimate [guess] the value of exogenous variables in the template and based on these exogenous variable forecasts, we solve for the endogenous forecast variables.

Populating the PV template with exogenous and exogenous variables. Like Coordinated Financial Statements [CFS], we populate PV templates with exogenous and endogenous variables. The columns of data in Table 11.9 are organized into exogenous and endogenous variables. The exogenous variables are highlighted. Endogenous variables are not. The main distinction between exogenous and endogenous variables within the template is the following. The values of exogenous variables are determined outside of the template. The values of endogenous variables are determined within the main template. Excel makes it easy to distinguish between exogenous and endogenous variables. We can identify endogenous variables within the template because they require an operations such as +,-,*,^, or /. They may also require an operation embedded in an Excel function such as PV, PMT, NPER, RATE, IRR, or NPV.

Otherwise, they are exogenous. If the cell requires an operation, it will be signaled by an “=” sign that references other cells included in an operation. Exogenous variables may reference cells outside of the PV model with an equal sign when they contain values on which an operation will be performed. Alternatively, exogenous data cells in the template may include a number. The exception to this means for identifying endogenous variables is when they are calculated in separate pages and then copied into the main template.

We now describe in more detail the variables and data needed to populate our PV templates that enable us to find rolling NPV, AE, and IRR estimates for investments and invested equity. We begin with the only information we know for sure, future dates: 2019, 2020, 2021, etc. Therefore, we begin populating our PV templates with the one thing we know about the future: future dates recorded in column 1.

The investment acquisition amount. We record the investment acquisition amount in cell B4. Its value is determined outside of the template making it an exogenous variable. We treat the investment’s acquisition value as though it were a cash expenditure unless funded by debt, an amount recorded in cell C4. When we employ debt to acquire the investment, the focus is on the equity invested—equal to the investment amount less the debt used to acquire the investment.

If we purchased the investment[s] on credit, we enter the loan principal and interest payments in the years they are paid. We don’t enter the loan amount because money came from a lender and was received by the seller of the investment—Lon is only an intermediary in the exchange

The investment amount is also a well-documented number. Durable sellers post their offer prices. Markets establish and record the prices at which durables are exchanged. References for used durable prices like Kelly’s Blue Book for cars are generally available. Salvage values that occur sometime in the future are much harder to estimate. The IRS provides some economic life estimates and depreciation data equal to percent changes in the value of the original durable. Theses may help us establish book values and may provide some guide for determining the durables’ liquidation value.

Complicating our investment data are projects that incur investments costs over several time-periods. Indeed, one might consider repair and maintenance expenditures designed to improve the life and durable performance to represent additional investments. In other cases, the investment includes several durable that we replace at different intervals. For example, feeder calf operations routinely replace one cohort of calves with another but the feeding equipment and physical facilities we replace less often than the feed calves.

Liquidation values. To find rolling NPV, AE, and IRR estimates, we require not only an investment acquisition amount, but investment liquidation and market values over time. An investment’s acquisition value, V0, is the amount of money exchanged to acquire the investment. An investment’s book value in period t, Vtbook, equals it acquisition value minus its accumulated depreciation. An investment’s liquidation value in period t, Vtliquidation, is the amount of money a buyer is willing to exchange for the investment when it is sold. If an investment’s liquidation value is greater than its acquisition value, we refer to the difference as capital gains. If an investment’s liquidation value is less that its acquisition value but greater than its book value, we refer to the difference as depreciation recovery. Finally, if the liquidation value is less than its book value, we refer to the difference as capital losses. We describe capital gains, depreciation recovery, and capital losses in the following equations:

for Vtliquidation > V0 > Vtbook, capital gains = Vtliquidation – V0 > 0,

for V0 > Vtliquidation > Vtbook, depreciation recapture = Vtliquidation – Vtbook > 0, and

for V0 > Vtbook > Vtliquidation, capital losses = Vtbook – Vtliquidation > 0.

We determine accounting book values by applying tax regulations that specify depreciation amounts depending on the type and age of the investment. Since depreciation forecasts depend on the age and amount of the investment, we generally consider them endogenous variables. Furthermore, we recognize that liquidation values of investments are also generally determined by projecting market values and cost data and may be endogenously or exogenously variables. When we do not expect to liquidate the investments, but want to estimate their NPV, AE and IRR, we may equate market liquidation value with book values. That we populate our PV templates with forecasts, sophisticated guesses, suggests that our template is not completely accurate. As a result, we should calculate NPVs, AEs, and IRRs under a variety of guesses so that we have some impression about the robustness of our estimates.

We conclude this chapter with the observations that we are all forecasters—all the time. We are all today making decisions and plans that will influence our conditions in the future–and these require forecasts. As we review the nature of forecasts, we should be humbled by the process and two factors. The first one is that forecasts have been often wrong. As evidence, review the forecasts that predicted the great recession of 2008. And the second factor that should humble us is that the only thing we know for certain about the future is the time it will take us to get there.

We emphasize here that there exist many potential forecast equations. These should be evaluated based on their accuracy, their implementation difficulty, availability of data, and the importance of the forecast. Still, we need to accept that what should determine our confidence in the accuracy and correctness of our forecasts is finding exogenous variables highly correlated with the passage of time. Fortunately, there are several exogenous variables that are satisfy this requirement. Most natural phenomenon have some predictable life cycle patterns of growth, production, and decay. We all make commitments that commit us to some action in the future that we often complete. As a result, our own actions in the future can often be predicted. Still shocks often leave disturbs our confident in our forecasts.

Still, we must make decisions in the present that commit us to outcomes in the future—and there is no alternative. We will all continue to forecast and hope to become better in the effort. Hopefully, this chapter has provided some ideas on how we can all make better forecasts.

Questions

1. Describe the differences between forecasts and predictions.
2. How would you characterize the main business of forecasters?
3. List five forecasts and five predictions that you have made during the last week. Describe your confidence in the accuracy of your forecasts and predictions and what determined your confidence.
4. Forecasts can be accurate or inaccurate or wrong and right. Explain.
5. Evaluate the following forecast. A turkey woke up on Thanksgiving Day and forecast that it would be like every other day?
6. List two kinds of shocks. Provide an example from the nation news of the shocks.
7. Given the likelihood that not all of our forecasts will be accurate or correct, how can we plan these eventualities?
8. What is the difference between dependent and independent variables in forecast equations?
9. Explain the difference between exogenous and endogenous variables in the context of CFS and PV templates.
10. We may forecast exogenous and endogenous variables. Explain the difference in these forecasts and how they may be used.
11. Forecasting can be described as a two-step process.
12. The commodity price index is today 123 percent of its base 10 years ago.
    1. Using a deterministic method, forecast the price index next year. If corn prices today are $3.18 forecast next year’s corn prices using the following methods:
    2. Using a naïve forecast.
    3. Using a geometric average forecast based on the commodity price index.
    4. Assume that corn prices during the past four years were: $2.56, $3.90, $2.89. $3.25, and $2.89. Use a linear regression model to forecast corn prices next year and the year after next.
    5. Using a regression equation with independent variables t and t^2 to forecast corn prices next year and the year after that.
    6. Compare and comment of the R2 for the linear and quadratic function and the t statistics for coefficients of the independent variables in the equations.
13. Consider the PV template described in Table 11.1. Then resolve the template assuming that expenses [COGS +OEs] were an endogenous variable. Find the value of expenses using the exogenous variable sales data. Determine the relationship between COGS that vary with sales and OEs that mostly vary with the passage of time.
14. Assume the following endogenous and exogenous variables have been observed in the past. Forecast Y[7] using the data below. Determine the accuracy of your forecast and the confidence interval around your forecast.

If the Data menu ribbon does not show the Data Analysis button at the far right, you can enable it in the Excel Options.

Select File and then Options.

In the Excel Options popup window, first select the Add-ins menu, and then select the Go… button to Manage Excel Add-ins.

Finally, in the Add-ins popup window, check the Analysis ToolPak option and select OK.

When you return to the Excel spreadsheet Data menu ribbon, the Data Analysis button is now available.