Business Finance
Inflation, Sensitivity Analysis, and Integrating Budgeting With Performance.

Question Description

- Attached are 3 Modules (7, 8, and 9), at the end of each module, in Yellow under the word (Assignments) there are highlighted questions, about Inflation, Sensitivity Analysis, and Integrating Budgeting With Performance.

- To be specific:

- Module 7, questions 1-2

- Module 8, questions 1-3

- Module 9, questions 1-5

Please answer these questions by using Excel and its formulas. and Word if needed.


Unformatted Attachment Preview

MODULE 7 Inflation Learning Objectives: ■ ■ ■ ■ ■ Understand inflation Use terminology related to inflation Choose a base year Calculate constant dollars Choose a deflator We use the term inflation to indicate the declining purchase power of money over time. The general reason for inflation is that the quantity of money seeking to purchase goods and services increases faster than the quantity of goods and services offered for purchase. Because there are many complexities, such as the speed at which money passes from one purchase to the next, inflation is best measured by tracking the actual purchase price of typical goods—called a market basket—repeatedly over time. Doing this allows for construction of an index, that is, a series of numbers associated with dates that show the change in the price from a base point. At the base point, the value is set at 1, 100%, or sometimes 100. If it is 100, the value means 100%, so if you take the actual purchase price of all the goods in the market basket and divide it by the actual purchase price on the base date (the date when the base point is set), the result is 1 or 100%.1 Values on subsequent dates tend to be higher because the purchase price of a market basket tends to go up. Sometimes earlier dates are also shown, based on either historical data or estimation, and these values typically will be smaller because the purchase price of the market basket was less. The index that most readers hear about from popular news sources is the Consumer Price Index for All Urban Consumers, usually abbreviated as CPI. As the full title indicates, it is focused on urban consumer prices, meaning the prices of goods and services that members of a household in a city or suburb might purchase. Nominal Versus Constant or Real Dollars The dollars subject to inflation (i.e., those in the actual world used for actual purchases) are sometimes called nominal dollars, which means that they are perceived to reflect the value of a dollar on the date that they are used. Comparing amounts of money available (revenue or appropriations) or spent in the form of these dollars at different times is confusing. That’s because we cannot distinguish between the effects of inflation and the effects of other changes on the expenditure side and the revenue side. These might include, for example, differences in demand or efficiency on the expenditure side or differences in population or tax rates on the revenue side. To correct this, we make calculations using constant dollars, also sometimes called real dollars. Constant dollars start with nominal dollars and are then adjusted using an index. Figure 7.1 shows a comparison between nominal dollars and constant dollars. The series is sales and gross receipts “tax” for Alabama as reported by the US Census Bureau.2 The central solid line that rises from $2.5 million to a little more than $4.5 million is nominal dollars. The flatter dotted lines above and below the solid line both show constant dollars and, in fact, are roughly the same except for their levels (height) on the chart.3 They are at different levels on the chart because they have different base years,4 but first, there is a difference between either of these and the nominal series. On the left side, we see that the nominal series grows every year until 2003, when it drops slightly. In the constant series, after a drop in 1994, the series grows until 1999 and stays roughly flat until 2003, when it takes a sharp drop. Both series then grow until 2007, with the constant series growing at a slower rate. The nominal series continues to grow into 2008 at a slower rate; then it takes a sharp drop in 2009. It then recovers slightly above its former level in 2010 and continues to grow. The constant series begins to drop sharply in 2008 and 2009, partly recovers in 2010, and then continues to slowly decline. FIGURE 7.1 Alabama Revenues in Constant Dollars and Nominal Dollars (1993–2012) Sources: US Census Bureau, 2013, These two views of the series tell very different stories. The nominal series grows in almost all years, rapidly recovers from declines, has almost doubled over the last two decades, and is growing as of the last date represented on the graph. The constant series has grown in 9 of 19 intervals, saw most of its growth between 2003 and 2007, has grown less than 20% over the last two decades, and is currently in modest decline. The constant series provides a more realistic understanding of the changes in the purchasing power of Alabama’s revenue from this source. Thus, for many purposes, a first step to effective analysis may involve converting nominal dollars to constant dollars. Base Year Figure 7.1 shows two constant series that reflect the need for the analyst to make a choice. In the calculation (math) of constant dollars, the base year—meaning the year when the constant dollars and the nominal dollars have the same value— used in producing the constant series is arbitrary. But the choice is not. For many purposes, an analysis is conducted to communicate something specific. The message might be “If the value of money were what it was in 1993, we would only have $2.9 million (1993) in taxes right now. Real revenue has declined for four of the last five years. We need to find a new revenue source.” This is a largely backward-looking message aimed at telling a story about constant dollars and revenue-related policy implications. Here, where the emphasis is on storytelling and not on estimation for the current period, the use of the earlier base year may be appropriate. For other purposes, the main goal of the conversion of nominal dollars to constant dollars is to aid in estimation for the current period or the near future. When making estimates for the present, it is unhelpful to have dollar values that are substantially out-of-date. While the conversion to constant dollars will take away anything from the data that pushes values up to the near future, estimates that are in the near to current base period are still much more useful than those in substantially eroded dollars. Consequently, the base year should be the most recent year for which data and an appropriate index are available. If absolute precision is required, estimates made in this form may be projected into future years using assistance from projected index values; however, such inflating of estimates may be subject to rules in many budget environments. Where the user is uncertain which approach to use, the most recent period’s base should be preferred. Calculation of Constant Dollars The calculation of constant dollars is straightforward. The formula is as follows: This formula says that constant dollars in a time period, Ct, are found by multiplying the nominal dollars for that time period, Nt, by the fraction in which the numerator is the base year index value, IB, and the denominator is the periodic index number, It. This calculation is shown in Table 7.1. In the spreadsheet labeled Tables, Data,Worksheets-M07.xlsx, represented by Table 7.1, “Sales and Gross Receipts” is in column B, CPI is column C, row 4 contains 1993 data, and row 23 contains 2012 data. We used rounding to eliminate the unnecessary and sometimes confusing long decimal results generated, but often not revealed, by spreadsheet formats. TABLE 7.1 Alabama Revenue in Nominal Dollars (CPI) and Constant Dollars, With CPI (1993– 2012) Sources: US Census Bureau, 2013, Federal Reserve Bank of St. Louis, The Excel formula used for the Constant 2012 Dollars column for 1993 is as follows: =ROUND($B4*C$23/C4,0) This formula can be used to select any base year by changing the row number after the $ sign in the numerator of the fraction. Deflators and Indexes This demonstration uses CPI because it is the most common price index that users know. However, governments are not typical urban consumers. The US Bureau of Economic Analysis computes a consumption expenditures price deflator for urban governments. The series label is A829RD3A086NBEA, and it can be downloaded from the Federal Reserve Bank of St. Louis at Figure 7.2 shows the data series shown in Figure 7.1 with nominal dollars, constant dollars calculated using CPI, and constant dollars calculated using the state and local implicit price deflator. This deflator more specifically shows how inflation affects governmental spending power based on what governments purchase. Based on this calculation of constant dollars, any limited gains in revenue have been entirely eroded away in recent years. While analyses using CPI may be important for communicating how taxes affect the burden experienced by taxpayers (the data should also be adjusted to reflect per-capita or per-household information), analyses using the price deflator reflect the ability of the government to purchase goods and services with the money it has acquired. When selecting a deflator or index, the analyst should be careful to select the one that is most appropriate for the intended purpose. FIGURE 7.2 Comparing the Indexes Sources: US Census Bureau, 2013. Summary Inflation is the declining purchasing power of money over time. The dollars subject to inflation, meaning those in the actual world used for actual purchases, are sometimes called nominal dollars, while real dollars are those adjusted for inflation using an index. The most commonly used index is the CPI, or Consumer Price Index. An index used by government is the US Bureau of Economic Analysis’s index, which computes a consumption expenditures price deflator for governments that reflects government spending power based on what government bodies typically purchase. Assignments 1. Define the following: a. Nominal dollars b. Constant dollars 2. Lake City’s park gazebo is available for residents to rent for picnics and other gatherings. You have been tasked with building a compelling financial story to convince the city council to raise the rental rates. The rental revenue history is shown in Table 7.2, along with the CPI for each of the years. TABLE 7.2 Lake City: Park Gazebo Revenues and CPI (1984–2012) a. Calculate 2012 constant dollars for the rental revenue. b. Calculate 1984 constant dollars for the rental revenue. c. Create a line graph displaying the nominal dollars, 2012 constant dollars, and 1984 constant dollars across all years of data. d. How would you use these data to create a compelling financial argument to increase rental rates? Would you use all of the data? 3. A member of Lake City’s town council—who has been on the city council for almost 25 years, remembers everything, and has a particular fondness for the park—questions the data you have presented. He presents you with a newspaper clipping from 1996 that claims the revenue in 1984 was just under $20,000 per year. Back at your desk, you tackle your new task of determining where this “under $20,000 per year” figure came from as well as how to explain nominal dollars and constant dollars to this member of the town council. a. Using the same nominal dollars as in assignment 2, add a column and calculate 1995 constant dollars. b. Add the 1995 constant dollars data to your graph. c. Using this graph, write a simple explanation about nominal dollars and the use of different base years to create constant dollars. The explanation should be no more than a page and written for an audience that does not have a financial background. 4. Big East City’s Public Works Department is asking for an additional $100,000 for sign repairs in the next budget cycle because its costs have increased by at least that much since 1995. The department has provided you with the information in Table 7.3. Big East City has adjusted funding for each of its departments every year to keep up with the buying power of money. a. Calculate 2012 constant dollars for the expenditures. b. Calculate 1995 constant dollars for the expenditures. c. Create a line graph displaying the nominal dollars, 2012 constant dollars, and 1995 constant dollars across all years of data. d. Based on the data provided and the calculations you have completed above, does the Public Works Department’s request make sense? How much additional funding do you think it might need? TABLE 7.3 Big East City: Expenditures for Sign Repairs and Price Deflator (1995–2012) MODULE 8 Sensitivity Analysis Learning Objectives: ■ ■ ■ ■ Understand sensitivity analysis as a general tool that is applicable to many methods Apply scenario analysis Apply quantitative sensitivity analysis Prepare for use of sensitivity analysis with respect to other techniques Sensitivity analysis is a general term for determining the potential range of uncertainty when precise statistical methods are unavailable. There are several practices that sometimes serve this purpose. The basic idea of sensitivity analysis is to examine the effect of adjusting uncertain values across their possible range. This practice can be performed both quantitatively and qualitatively. The following discussion focuses on relatively easy-to-apply methods. However, at the end, more sophisticated methods are mentioned, and some of the resources refer to complex methods. Scenario Analysis With qualitative information, scenario analysis may be used. Scenario analysis consists of writing realistic stories (scenarios) that examine the anticipated way that events may happen. If scenario writing does not come naturally, the analyst might think of them as brief “What if?” problems such as these: “What if we close the fire station at 10th Street and …?” “What if we open a senior center at the armory on Main Street and …?” Think about what could realistically follow the and. What if a fire happened at a nearby apartment building, or what if the potential senior center were built in in a neighborhood where the number of senior citizens did not fill the center’s capacity? Creating realistic extreme, but not silly, scenarios will help determine the degree of uncertainty associated with cost estimates and related policy recommendations. Example of Scenario Analysis BASIC FACTS Summerville is a retirement mecca in the Southwest. With a population of 180,000, it has 40,000 residents over the age of 65 and 18,000 over the age of 75. Because of high-quality in-home care programs, 9,000 of the population age 75 and older live in independent or semi-independent housing. Roughly half of the senior population relies on Social Security, Supplemental Security Income, or similar government programs as their sole source of income. Most of these individuals are eligible for the Supplemental Nutritional Assistance Program (SNAP, formerly food stamps), although some are not enrolled. Presently, Summerville provides seniors with group meals and other similar senior services seven days a week at five multifunctional community centers. Summerville makes these available to all who come, regardless of income status, and it also provides meals-on-wheels to roughly 4,000 enrolled seniors who are unable to leave their home. Because of population growth, the community centers are crowded, and there are conflicts between the seniors and other users, particularly teens who want to use the same spaces for sports activities. The group meals program is thought to be underused. Summerville is proposing to open senior centers in facilities that are adjacent to the multifunctional community centers. These facilities will share kitchen facilities but will otherwise operate separately. Seniors will continue to have access to community centers, but non-seniors will generally not have access to the senior centers except as needed to assist seniors. Scenario 1: Particularly because of the relocation of meals and programming, the seniors adapt to the separate senior centers over the first 3 months. Conflicts over space no longer occur. After a few months, there is a small increase in senior center usage because individuals who were discouraged by the prior conflicts find the new arrangement more pleasant. Likewise, usage of the community center by nonseniors increases slightly, reflecting the additional capacity. The overall small increase in community center usage has little apparent budgetary impact, except for a small increase in maintenance and cleaning costs and a small increase in the meals budget. Scenario 2: With the relocation of meals and programming, the seniors move to the new senior centers within a short time. The publicity associated with the new senior centers attracts large numbers of seniors who were not even aware of the services in the community centers, as the latter had not been labeled senior centers. Soon, the senior centers are filled to capacity, and the facilities are looking to expand back into the community centers for needed extra space. The conflict is not resolved, and non-seniors begin to demand new sports facilities that are not associated with the community centers. The budgetary impact involves substantial increases for meals and new staff (to manage the unexpected growth in participation) and a longer-term capital cost of constructing three sports facilities located away from the community centers. Scenario 3: The seniors see the relocation of meals and programming as rejection by the community. Over the next year, participation dwindles, and two facilities are closed for lack of use. This leads to further lack of trust between seniors and other residents. With the decline in use of the senior centers, there are also other effects. The meals-on-wheels program has an increase in enrollment of over 1,000 individuals. The in-home care program sees a decline in success, with more individuals moving into nursing homes. Because of a lack of nursing home beds in the area, the seniors begin to demand that the community build and operate a nursing home. Budgetary impacts include the decreased cost of operating the senior meals program and other programs, the increased cost of meals-on-wheels, and the capital and possible operating costs associated with the nursing home (although these may eventually be paid by third parties). Understanding Sensitivity Analysis When an estimate of any sort includes a value that has been included based on judgment, the range of uncertainty can be estimated through sensitivity analysis. The first estimate should be based on the value or values that are judged to be correct. Then at least two more estimates should be made. These should be the two most extreme realistic estimates reflecting possible scenarios. If only one variable is included in the estimate, the analyst estimates the value the variable will have, then makes two more estimates using the highest and lowest possible values of that variable. With two or more variables, all combinations should be calculated, and the reported values should be the expected value and the highest and lowest possible values. Consider the senior center scenario analysis. One element of that analysis is the group meals program. A concern is that the meals program can be affected by the building of new senior centers. In Table 8.1, we consider what happens when the number of users changes. At present, there are 4,000 users, and the number is stable. So the estimate is 4,000. Based on current plans, the cost per meal for feeding these 4,000 seniors one meal a day is approximately $6 per meal. The annual cost is $8.76 million. If the number of users declines to 3,000 (a possible result if the service becomes less popular), the cost of this element of the program drops to $6.57 million. If the number of users increases to 5,000 (if the service becomes more popular), the cost increases to $10.95 million. So the possible cost r ...
Purchase answer to see full attachment

Final Answer


Question 2

Lake City: Park Gazebo Revenues and CPI


2011 dollar value
CPI for 2012
CPI for 2011
2011 dollar value



1993 constant dollar value
CPI for 1994
CPI for 1993
1994 constant dollar value







Msharon (10461)
University of Maryland

Thanks for the help.

Outstanding. Studypool always delivers quality work.

Tutor was very helpful and took the time to explain concepts to me. Very responsive, managed to get replies within the hour.

Similar Questions
Related Tags

Brown University

1271 Tutors

California Institute of Technology

2131 Tutors

Carnegie Mellon University

982 Tutors

Columbia University

1256 Tutors

Dartmouth University

2113 Tutors

Emory University

2279 Tutors

Harvard University

599 Tutors

Massachusetts Institute of Technology

2319 Tutors

New York University

1645 Tutors

Notre Dam University

1911 Tutors

Oklahoma University

2122 Tutors

Pennsylvania State University

932 Tutors

Princeton University

1211 Tutors

Stanford University

983 Tutors

University of California

1282 Tutors

Oxford University

123 Tutors

Yale University

2325 Tutors