Simulation (MS Excel)

Simulation 1

Input the information from the ‘cash flow data’ document into the Cash Flow template.  After inputting all information, label this worksheet as ‘year 1 estimate’

2. Examine the actual cash-flow budget for year 1.  Copy this worksheet into your excel document as a 2^nd worksheet labeled ‘year 1 actual.’

3. A number of unexpected realities confronted you as you implemented Year 1’s budget.  These included:

• Could not hire any staff until April due to a very weak applicant pool
• Because of a lack of staff, donations were not successful until May.  And even then, donations per month were unpredictable
• No students applied for the nonprofit organization’s scholarships
• Utility expenses varied widely, especially in the winter months which were colder than normal
• Office expenses were also somewhat unpredictable due to three printers and toner for the printers having to be purchased.
• For the August board meeting, bad weather forced three of the board members to change their flight in order to arrive at the board meeting on time.  These changes totaled $3500.00
• Additionally, during the board meeting, one member submitted a $5000.00 expense for a weekend of golf and dinners with potential donors.  The member claimed this expense was used to help bring in more donations.  The expense was paid.
• Additional expenses were generated during the board meeting due to the following unexpected events:

o   All board members flew first class on planes to Michigan

o   One board member rented a limousine from the Detroit airport to Mt. Pleasant

<Both of these expenses were paid>

4. Based on this information, develop a budget for Year 2.  Include this budget as a new worksheet in your document (thus, your excel document should have three worksheets:  year 1 estimate, year 1 actual, and year 2 estimate).
5. Also, write a budget narrative for each of your line items for Year 2.  That is, justify why the amount of money is needed for each line item.  Additionally, include any other policy ideas you think are needed to control the budget for year 2.

Simulation 2

The excel spreadsheet has property tax revenue for a locality.  In the first worksheet labeled "Property Tax Data" you are given property tax revenue for the years 1980-2006.  Based on this data, your job is to estimate property revenue for years 2007-2012 using the Simple Moving Average (SMA) technique, the Transformation Moving Average (TMA) technique, and the Exponential Smoothing Technique (EXS).  As a result, you will have three different estimations for property tax revenue for years 2007-2012.  These revenue estimations have to be done in excel by using the "excel equation option."  

Next, you have to determine which estimation technique generates the most accurate estimate.  In the second worksheet labeled "Property Tax Data Actual 1980-2012" you are given the actual property tax data for years 1980-2012.  With the actual property tax data from years 2007-2012, calculate the percentage error of both of your estimates annually for years 2007-2012.  These percentage error estimations have to be done in excel by using the "excel equation option."  

Third, generate three "line charts" (also called scatterplots) in excel that show the actual property tax data compared to the estimated property tax data for years 2007-2012 for the SMA, TMA, AND EXS techniques.  

For this simulation, I want you to email me one excel document with:  the SMA, TMA, and EXS estimates for years 2007-2012, the percent errors for the SMA, TMA, and EXS estimates for years 2007-2012, and the three line charts showing the SMA estimate compared to the actual property tax data for years 1980-2012, the TMA estimated compared to the actual property tax data for years 1980-2012, and the EXS estimated compared to the actual property tax data for years 1980-2012

Note:  In order to do well on this simulation, you should structure your excel document so that I can easily follow your equations and calculations.   

Simulation 3

The excel spreadsheet has property tax revenue for a locality.  In the first worksheet labeled "Property Tax Data" you are given property tax revenue for the years 1980-2006.  Based on this data, your job is to estimate property revenue for years 2007-2012 using the Simple Moving Average (SMA) technique, the Transformation Moving Average (TMA) technique, and the Exponential Smoothing Technique (EXS).  As a result, you will have three different estimations for property tax revenue for years 2007-2012.  These revenue estimations have to be done in excel by using the "excel equation option."  

Next, you have to determine which estimation technique generates the most accurate estimate.  In the second worksheet labeled "Property Tax Data Actual 1980-2012" you are given the actual property tax data for years 1980-2012.  With the actual property tax data from years 2007-2012, calculate the percentage error of both of your estimates annually for years 2007-2012.  These percentage error estimations have to be done in excel by using the "excel equation option."  

Third, generate three "line charts" (also called scatterplots) in excel that show the actual property tax data compared to the estimated property tax data for years 2007-2012 for the SMA, TMA, AND EXS techniques.  

For this simulation, I want you to email me one excel document with:  the SMA, TMA, and EXS estimates for years 2007-2012, the percent errors for the SMA, TMA, and EXS estimates for years 2007-2012, and the three line charts showing the SMA estimate compared to the actual property tax data for years 1980-2012, the TMA estimated compared to the actual property tax data for years 1980-2012, and the EXS estimated compared to the actual property tax data for years 1980-2012

Note:  In order to do well on this simulation, you should structure your excel document so that I can easily follow your equations and calculations.