Using SAS for Budget Analysis

[colabear]

Hello,

I am tasked with determining whether a contractor will spend their allocated funds during the fiscal year. My dataset contains the following: 

Fiscal Year	Vendor Name	Paid Date	Service Detail CD	Cost	Sub Categories
2019	name	9/4/2017	31	$1,300.00	Typical
2019	name	9/4/2018	32	$199.99	Complex/High Dollar
2019	name	1/31/2019	68	$4,300.00	Complex/High Dollar

Each contractor has a specific allocation amount. I am trying to figure out how to project whether they will spend all their funds. The previous FY expenditure is included in the dataset. I have never used the proc timeseries or the Time Series Forecasting System prior to today. However, the output I get does not really tell me much or it results in flat expenditure (straight line). I also tried proc arima but I don't think I am understanding its functionality. As always, any guidance is greatly appreciated! 

[tomrvincent]

I'd think you'd also need funds and paid frequency as well.

[colabear]

Hello Tom,

Thank you for your response. I have the amount each contractor has been allocated. How do I put it in the dataset in an effective manner? Each service is paid whenever it's approved so i'm not sure how I could determine frequency. The paid_date variable is when the service was paid. I think i'm struggling the most with how to format my dataset. 

[Reeza]

Can you provide a more detailed example showing, what you have as input and what you want as output. If you don’t know this yet, you’re not at the programming stage yet.

[colabear]

Hello,

This is my input data:

data input;
infile datalines delimiter=',';
input Primary_Disability Secondary_Disability Contractor_Name Paid_Date	Unit_Type Cost Service_CD Service_Description Contract_ID Cost_Type;
datalines;
Hearing	Physical Contractor1	9/4/2018	Item	$1,300.00 31	Hearing Aid Purchase	3	Complex/High Dollar
Hearing	Physical Contractor1	9/4/2018	Service	$199.99  32	Hearing Aid Service Charge, Earmolds, and Batteries	3	Complex/High Dollar
Physical Cognitive	Contractor1 9/4/2018 Service	$300.00 68	Modification Inspection or Review by Engineer	3	Complex/High Dollar
Hearing Contractor2	9/4/2018	Item	$426.00 	31	Hearing Aid Purchase	15	Complex/High Dollar
Physical Vision	Contractor3	9/5/2018	Item	$105.02 	9	Assistive technology, not otherwise listed	2	Typical
Vision Contractor3	9/5/2018	Item	$105.02 	9	Assistive technology, not otherwise listed	2	Typical
Other	Physical	Contractor 4	9/7/2018	Item	$5,600.00 	62	Major Modification (e.g. permanent)	7	Typical
Physical Contractor5	9/28/2017	Service	$510.00 	26	Medical diagnostic & evaluation services	11	Typical
Physical Contractor6	9/28/2017	Service	$150.00 	65	Pre-modification evaluation or title search	11	Typical
Vision Contractor7	9/28/2017	Hour	$159.12 	40	Orientation & Mobility Training	11	Typical
Physical Contractor8	9/28/2017	Service	$200.00 	65	Pre-modification evaluation or title search	11	Typical
;
data amounts;
infile datalines delimiter=',';
input Contractor Amount ;
datalines;
Contractor1	$60,000.00
Contractor2	$150,000.00
Contractor3	$1,250,656.00
Contractor4	$350,000.00
Contractor5	$260,000.00
Contractor6	$420,000.00
Contractor7	$750,000.00
Contractor8	$68,000.00
;

This output below is what would be helpful. I'm not sure I need a graph. 

[Reeza]

Your data steps do not work for me. 

You can calculate the sum using PROC MEANs. Then merge the summarized data with the amounts data file to calculate your variance. 

I also don't see where the budget amount is. 

To get a report that looks like that you'll likely want to recalculate the items and then use PROC REPORT to display the information. 

Here's how you can calculate the summary statistics. 

proc means data=input noprint;
class Contractor_Name Primary_Disability Secondary_Disability Paid_Date;
var cost;
format paid_date monyy7.;
output out=summary sum=paid;
run;

[colabear]

Reeza,

The proc means was helpful for summarizing the data. The amounts datasets reflects the allocation amounts for each contractor. I may just have to analyze all this in Excel. Thank you! 

[tomrvincent]

So you're looking to total the charges by contractor for the year and compare it to the contractor's annual budget?

[colabear]

Hello,

Essentially, I need to project whether each contractor will spend all of their allocated funds (amount table). So, contractor #1 has spend $X ytd, how likely are they to spend the rest of their contracted amount before the end of the FY?

The input data set contains several variables including contractor, paid date, service, cost, cost type, ID, and fiscal year. There are other variables but I'm not sure if they are needed for this exercise. I have two fiscal years worth of data: 2018 (Sep-Aug) and 2019 (Sep-Jan). I believe for forecasting, the previous year's data is used to predict future costs. I think the biggest issue right now is ensuring my dataset if formatted correctly to execute any of the procedures. 

I definitely do not have a financial/accounting background so it's making this all that more difficult for me! I appreciate your help 🙂 

[tomrvincent]

So can't you just extrapolate the projected annual charge based on what they've spent YTD? If you have numbers to the end of September, just multiply that by 4/3 and compare it to the allocation.

[colabear]

Allow me to clarify something. Cost actually reflects expenditure. It's just named cost in the dataset. I will change it to "paid". It's possible that this is an unreasonable task given these contractors spend wildly different amounts each month. Typically more mid-year and beginning and end of the FY. 

I multiplied FY17 paid amounts by 4/3  for each months. What is the rationale for 4/3 instead of maybe 10%?  

I feel I am at a loss! 😞

[tomrvincent]

Sept is 3/4th of the year. If you've paid $30 by then, you can estimate total year as $40.