Excel Sheet Download 30898 | Frm Cash Management Chapter 08 Co Cash Model Direct Funding Excel

Partial file snippet.

Sheet 1: detailed cash flow projection

	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
		UNDP Country Office Weekly Detailed Cashflow Projection Sheet 
		
	
		Control Box
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Month and Year Projected
		Octorber
		2001
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		USD $
		LC
		

		

		

		

		

		

		

		

		

		

		

	
	
		UNDP Exchange Rate
		1
		145
		

		

		

		

		

		

		

		

		

		

		

	
	
		Imprest Level
		 725,000 
		0
		

		

		

		

		

		

		

		

		

		

		

	
	
		Beg. Cash Balance of the month
		 56,000 
		1,141,000 
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Column 1
		Column 2
		Column 3
		Column 4
	
	
		Cash Flow Components
		Week 1 
		Week 1
		Week 2
		Week 2
		Week 3
		Week3
		Week 4
		Week4
		Week 5
		Week 5
		Monthly Total
		Monthly Total
		Timing of Cash Flow
	
	
		USD
		Local Curr.
		USD
		Local Curr.
		USD
		Local Curr.
		USD
		Local Curr.
		USD
		Local Curr.
		USD
		Local Curr.
		

	
	
		Inflows from Local Sources:
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		A
		Receipts from Govt. Contribution 
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		a
		  Project 1 NEX
		

		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		Depends on projects
	
	
		b
		  Project 2 NEX 
		

		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		Depends on projects
	
	
		c
		  Project 3 NEX 
		

		0 
		10,150,000 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		10,150,000 
		Depends on projects
	
	
		d
		  Project 4 Agency execution 
		

		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		Depends on projects
	
	
		

		  Subtotal
		

		0 
		10,150,000 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		10,150,000 
		

	
	
		B
		Receipts from Agencies
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		  Subtotal
		

		22,500 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		22,500 
		0 
		Week 1
	
	
		C
		Receipts from Others
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		  Subtotal
		

		2,100 
		130,500 
		2,100 
		130,500 
		2,100 
		130,500 
		2,100 
		130,500 
		2,100 
		130,500 
		10,500 
		652,500 
		Every week
	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		D
		Total Inflows from Local Sources
		

		24,600 
		10,280,500 
		2,100 
		130,500 
		2,100 
		130,500 
		2,100 
		130,500 
		2,100 
		130,500 
		33,000 
		10,802,500 
		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Outflows:
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		E
		Proj. Advance/Expenditure
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		  Project 1 NEX 
		

		(144,000)
		(5,220,000)
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		(144,000)
		(5,220,000)
		Beginning of quarters
	
	
		

		  Project 2 NEX
		

		(120,000)
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		(120,000)
		0 
		Beginning of quarters
	
	
		

		  Project 3 NEX 
		

		(100,000)
		(14,500,000)
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		(100,000)
		(14,500,000)
		Beginning of quarters
	
	
		

		  Project 4 Agency execution
		

		0 
		0 
		(75,000)
		0 
		(25,000)
		0 
		0 
		0 
		(50,000)
		0 
		(150,000)
		0 
		Varies
	
	
		

		  Subtotal
		

		(364,000)
		(19,720,000)
		(75,000)
		0 
		(25,000)
		0 
		0 
		0 
		(50,000)
		0 
		(514,000)
		(19,720,000)
		

	
	
		F
		Non UNDP Agy
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		  Subtotal
		

		0 
		(543,750)
		(15,000)
		(418,750)
		(20,000)
		(500,000)
		(5,000)
		(435,000)
		(35,000)
		(821,250)
		(75,000)
		(2,718,750)
		Every week
	
	
		G
		Fixed Expense
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		  Salary and Allowance
		

		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		(225,000)
		(7,612,500)
		(225,000)
		(7,612,500)
		The Last week
	
	
		

		  Fees paid to Consultant and Services
		

		0 
		0 
		(5,000)
		(100,000)
		0 
		0 
		(5,000)
		(100,000)
		0 
		0 
		(10,000)
		(200,000)
		Week 2,4
	
	
		

		  Premise/Security 
		

		0 
		(3,625,000)
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		(3,625,000)
		Week 1
	
	
		

		  Rent
		

		(15,000)
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		(15,000)
		0 
		Week 1
	
	
		

		  Utility 
		

		0 
		0 
		0 
		(1,631,250)
		0
		0
		0 
		0 
		0 
		0 
		0 
		(1,631,250)
		Week 2
	
	
		

		  Phone
		

		0 
		0 
		0 
		0 
		(1,500)
		(870,000)
		0 
		0 
		0 
		0 
		(1,500)
		(870,000)
		Week 3
	
	
		

		  Vehicle operating 
		

		(375)
		(489,375)
		(375)
		(489,375)
		(375)
		(489,375)
		(375)
		(489,375)
		(375)
		(489,375)
		(1,875)
		(2,446,875)
		Every Week
	
	
		

		  Office supplies 
		

		0 
		0 
		(2,500)
		(543,750)
		0 
		0 
		(2,500)
		(543,750)
		0 
		0 
		(5,000)
		(1,087,500)
		Week 2 & 4
	
	
		z
		  Miscellanies
		

		(200)
		(116,000)
		(200)
		(116,000)
		(200)
		(116,000)
		(200)
		(116,000)
		(200)
		(116,000)
		(1,000)
		(580,000)
		Every Week
	
	
		

		  Subtotal
		

		(15,575)
		(4,230,375)
		(8,075)
		(2,880,375)
		(2,075)
		(1,475,375)
		(8,075)
		(1,249,125)
		(225,575)
		(8,217,875)
		(259,375)
		(18,053,125)
		

	
	
		H
		Variable Expense
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		  Capital spending - PC, equip
		

		(200)
		(12,000)
		(200)
		(12,000)
		(200)
		(12,000)
		(200)
		(12,000)
		(200)
		(12,000)
		(1,000)
		(60,000)
		Varies
	
	
		

		  Travel Expenses
		

		(500)
		0 
		0 
		(31,300)
		0 
		0 
		(3,500)
		(15,500)
		0 
		0 
		(4,000)
		(46,800)
		Varies
	
	
		

		  Activity Fees 
		

		0 
		0 
		0 
		(52,200)
		(40)
		(52,200)
		0 
		0 
		0 
		(52,200)
		(40)
		(156,600)
		Varies
	
	
		

		  Miscellanies
		

		(200)
		(11,000)
		(21,800)
		(32,600)
		(200)
		(11,000)
		(200)
		(11,000)
		(200)
		(11,000)
		(22,600)
		(76,600)
		Every Week
	
	
		

		  Subtotal
		

		(900)
		(23,000)
		(22,000)
		(128,100)
		(440)
		(75,200)
		(3,900)
		(38,500)
		(400)
		(75,200)
		(27,640)
		(340,000)
		

	
	
		I
		Miscellanies
		

		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		J
		Total Outflows:
		

		(380,475)
		(24,517,125)
		(120,075)
		(3,427,225)
		(47,515)
		(2,050,575)
		(16,975)
		(1,722,625)
		(310,975)
		(9,114,325)
		(876,015)
		(40,831,875)
		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		K
		Net Cash Flow from Local Sources
		

		(355,875)
		(14,236,625)
		(117,975)
		(3,296,725)
		(45,415)
		(1,920,075)
		(14,875)
		(1,592,125)
		(308,875)
		(8,983,825)
		(843,015)
		(30,029,375)
		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		L
		Beginning Balance
		

		56,000 
		1,141,000 
		269,125 
		1,404,375 
		137,150 
		137,650 
		78,735 
		102,575 
		52,860 
		105,450 
		N/A
		N/A
		

	
	
		M
		HQ Funding
		

		669,000 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		669,000 
		0 
		Week 1
	
	
		N
		Net Cash Balance before Conversion
		

		369,125 
		(13,095,625)
		151,150 
		(1,892,350)
		91,735 
		(1,782,425)
		63,860 
		(1,489,550)
		(256,015)
		(8,878,375)
		419,855 
		(27,138,325)
		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		O
		Anticipated Conversion/HQ Funding
		

		(90,315)
		13,095,625 
		(13,051)
		1,892,350 
		(12,293)
		1,782,425 
		(10,273)
		1,489,550 
		(61,230)
		8,878,375 
		(187,161)
		27,138,325 
		Depends on cash needs
	
	
		P 
		Actual Conversion made
		

		(100,000)
		14,500,000 
		(14,000)
		2,030,000 
		(13,000)
		1,885,000 
		(11,000)
		1,595,000 
		(70,000)
		10,150,000 
		(208,000)
		30,160,000 
		Depends on cash needs
	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Q
		Net Cash Balance after Conversion
		

		269,125 
		1,404,375 
		137,150 
		137,650 
		78,735 
		102,575 
		52,860 
		105,450 
		(326,015)
		1,271,625 
		211,855 
		3,021,675 
		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		R
		Indication of Exceeding Imprest Level
		0 
		N/A
		0 
		N/A
		0 
		N/A
		0 
		N/A
		326,015 
		N/A
		326,015 
		N/A
		Depends on cash needs
	
	
		S
		Addt'l HQ Funding
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		350,000 
		0
		350,000 
		0 
		Depends on cash needs
	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		T
		Est. Weekly Cash Balance
		

		269,125 
		1,404,375 
		137,150 
		137,650 
		78,735 
		102,575 
		52,860 
		105,450 
		23,985 
		1,271,625 
		N/A
		N/A
		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		A
		The timing and the amount of cash inflow from governments depends on each project's contract. Insert, revise or delete rows before the subtotal, based on your real projects.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		a
		Received upfront by Treasury in USD.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		b 
		Received by CO in local currency annual installment in February
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		c
		Received $140,000, or 50% by Treasury in USD, remaining in local currency 20,300,000 semi-annual installments in Apr and Oct.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		d
		Existing project with contributions received already.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		B
		Based on agreements with agencies on Cost Recovery for Common Services. CO should have a great degree of control when to collect the funds.Insert rows before the subtotal, based on real situation.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		C
		Input all other cash receipts in this category. Add rows for details before the subtotal if needed.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		D
		This is a formula adding up all of the previous subtotals. When inserting new inflow categories, make sure the formula stays relevant.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		E
		The disbursement for each project depends on its contact. Insert, revise, or delete rows before the subtotal, based on real projects.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		F
		CO should require UN and agencies provide monthly forecast for their Non-UNDP payment amounts, timing, etc. which can be fine tuned when actual payment requests are received. Add rows before subtotal if needed.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		G
		This is a example. The weekly amounts are based on the monthly estimated expenses and filled in the corresponding week. For simplicity sake, vehicle operating and miscellanious expenses are evenly disbursed in 5 weeks.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		CO should have certain amount and timing for each component of its own fixed expenses due to recurring nature.  
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		H
		Based on estimated weekly number to be fine tuned with actual invoices received.  
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		I
		All other expenses other than the above ones should be input in this category.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		J
		This is a formula.  If more breakdown on type of outflows is needed, simply incert more rows but make sure the formula stays relevant.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		K
		This is a formula adding total inflows and outflows from local sources.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		L
		The balance for week 1 should be keyed in according to the last month's cash books, which is in the control box Cell D7 and F7. The remaining columes are formula equal to previous  week's estimated balance in Row 70.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		M
		This is a  direct replenishment the CO receives from Treasury so the amount of funding is shown in week 1.  $669,000 represents imprest level of $725,000 less previous month ending cashbook balance, rounded.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		N
		This is a fomula adding up all cash inflows and outflows.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		O
		When COs are short of cash (When LC accounts is negative in this example), they should transfer funding from their USD account to their LC account to cover the shortage. )
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		P
		This row shows the actual funds transferred from USD account to LC account, or "conversion", Which make the cash balance on LC account more than 0 but not exceeding 25% of weekly utilization level. 
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		It's the row COs can manage based on their cash liquidity. 
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Q
		This is a formula adding up Row 60 and 63.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		R
		Any anticipated difficiency (negative number in Row 65) flags that the CO either needs additional replenishment from the Treasury for exceeding the imprest level.  Actively manage this can 
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		avoid the delay in receiving additional replenishment or authorization. 
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		S
		Row 67 in the sample Model show the minimum amount COs should require from the Treasure to cover their cash shortage.  However, we expect COs to round up the figure as shown in sample Model with amount of $350,000.  COs 
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		should actively manage the amount of additional HQ Funding when the Net Cash Flow after Conversion (Row 65) starts to turn negative, to make sure the Est. Weekly Cash Balance (Row 70) keep positive in every
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		week.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		T
		The final estimated weekly cash balance which CO should have by projecting the above cash flow cautiously.
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	



Sheet 2: summarized cash flow projection

	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
		UNDP Country Office Simplified Weekly Cash Projection Sheet
		
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Month and Year Projected
		Octorber
		2001
		

		

		

		

		

		

		

		

		

		

	
	
		

		USD $
		LC
		

		

		

		

		

		

		

		

		

		

	
	
		UNDP Exchange Rate
		1
		145
		

		

		

		

		

		

		

		

		

		

	
	
		Imprest Level
		 725,000 
		0
		

		

		

		

		

		

		

		

		

		

	
	
		Beg. Cash Balance of the month
		 56,000 
		1,141,000 
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Cash Flow Components
		Week 1 
		Week 1
		Week 2
		Week 2
		Week 3
		Week3
		Week 4
		Week4
		Week 5
		Week 5
		Monthly Total
		Monthly Total
	
	
		USD
		Local Curr.
		USD
		Local Curr.
		USD
		Local Curr.
		USD
		Local Curr.
		USD
		Local Curr.
		USD
		Local Curr.
	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Inflows from Local Sources:
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		A
		Receipts from Govt. Contribution
		

		0 
		10,150,000 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		10,150,000 
	
	
		B
		Receipts from Agencies
		

		22,500 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		22,500 
		0 
	
	
		C
		Receipts from Others
		

		2,100 
		130,500 
		2,100 
		130,500 
		2,100 
		130,500 
		2,100 
		130,500 
		2,100 
		130,500 
		10,500 
		652,500 
	
	
		D
		Total Inflows from Local Sources:
		

		24,600 
		10,280,500 
		2,100 
		130,500 
		2,100 
		130,500 
		2,100 
		130,500 
		2,100 
		130,500 
		33,000 
		10,802,500 
	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Outflows:
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		E
		Disbursement - Proj. Advance/Expenditure
		

		(364,000)
		(19,720,000)
		(75,000)
		0 
		(25,000)
		0 
		0 
		0 
		(50,000)
		0 
		(514,000)
		(19,720,000)
	
	
		F
		Disbursement - Non UNDP Agy
		

		0 
		(543,750)
		(15,000)
		(418,750)
		(20,000)
		(500,000)
		(5,000)
		(435,000)
		(35,000)
		(821,250)
		(75,000)
		(2,718,750)
	
	
		G
		Disbursement - Fixed Expense
		

		(15,575)
		(4,230,375)
		(8,075)
		(2,880,375)
		(2,075)
		(1,475,375)
		(8,075)
		(1,249,125)
		(225,575)
		(8,217,875)
		(259,375)
		(18,053,125)
	
	
		H
		Disbursement - Variable Expense
		

		(900)
		(23,000)
		(22,000)
		(128,100)
		(440)
		(75,200)
		(3,900)
		(38,500)
		(400)
		(75,200)
		(27,640)
		(340,000)
	
	
		I
		Misllanies
		

		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
	
	
		J
		Total Outflows:
		

		(380,475)
		(24,517,125)
		(120,075)
		(3,427,225)
		(47,515)
		(2,050,575)
		(16,975)
		(1,722,625)
		(310,975)
		(9,114,325)
		(876,015)
		(40,831,875)
	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		K
		Net Cash Flow from Local Sources
		

		(355,875)
		(14,236,625)
		(117,975)
		(3,296,725)
		(45,415)
		(1,920,075)
		(14,875)
		(1,592,125)
		(308,875)
		(8,983,825)
		(843,015)
		(30,029,375)
	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		L
		Beginning Balance
		

		56,000 
		1,141,000 
		269,125 
		1,404,375 
		137,150 
		137,650 
		78,735 
		102,575 
		52,860 
		105,450 
		N/A
		N/A
	
	
		M
		HQ Funding
		

		669,000 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		669,000 
		0 
	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		N
		Net Cash Flow before Conversion
		

		369,125 
		(13,095,625)
		151,150 
		(1,892,350)
		91,735 
		(1,782,425)
		63,860 
		(1,489,550)
		(256,015)
		(8,878,375)
		419,855 
		(27,138,325)
	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		O
		Anticipated Conversion/HQ Funding
		

		(90,315)
		13,095,625 
		(13,051)
		1,892,350 
		(12,293)
		1,782,425 
		(10,273)
		1,489,550 
		(61,230)
		8,878,375 
		(187,161)
		27,138,325 
	
	
		P
		Actual Conversion made
		

		(100,000)
		14,500,000 
		(14,000)
		2,030,000 
		(13,000)
		1,885,000 
		(11,000)
		1,595,000 
		(70,000)
		10,150,000 
		(208,000)
		30,160,000 
	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Q
		Net Cash Flow after Conversion
		

		269,125 
		1,404,375 
		137,150 
		137,650 
		78,735 
		102,575 
		52,860 
		105,450 
		(326,015)
		1,271,625 
		211,855 
		3,021,675 
	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		R
		Indication of Exceeding Imprest Level
		0 
		N/A
		0 
		N/A
		0 
		N/A
		0 
		N/A
		326,015 
		N/A
		326,015 
		N/A
	
	
		S
		Addt'l HQ Funding
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		0 
		350,000 
		0 
		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		T
		Est. Weekly Cash Balance
		

		269,125 
		1,404,375 
		137,150 
		137,650 
		78,735 
		102,575 
		52,860 
		105,450 
		23,985 
		1,271,625 
		N/A
		N/A
	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		A
		The timing and the amount of cash inflow from governments depends on each project's contract. Insert, revise or delete rows before the subtotal, based on your real projects.
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		B
		Based on agreements with agencies on Cost Recovery for Common Services. CO should have a great degree of control when to collect the funds.Insert rows before the subtotal, based on real situation.
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		C
		Input all other cash receipts in this category. Add rows for details before the subtotal if needed.
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		D
		This is a formula adding up all of the previous subtotals. When inserting new inflow categories, make sure the formula stays relevant.
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		E
		The disbursement for each project depends on its contact. Insert, revise, or delete rows before the subtotal, based on real projects.
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		F
		CO should require UN and agencies provide monthly forecast for their Non-UNDP payment amounts, timing, etc. which can be fine tuned when actual payment requests are received. Add rows before subtotal if needed.
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		G
		This is a example. The weekly amounts are based on the monthly estimated expenses and filled in the corresponding week. For simplicity sake, vehicle operating and miscellanious expenses are evenly disbursed in 5 weeks.
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		CO should have certain amount and timing for each component of its own fixed expenses due to recurring nature.  
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		H
		Based on estimated weekly number to be fine tuned with actual invoices received.  
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		I
		All other expenses other than the above ones should be input in this category.
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		J
		This is a formula.  If more breakdown on type of outflows is needed, simply incert more rows but make sure the formula stays relevant.
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		K
		This is a formula adding total inflows and outflows from local sources.
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		L
		The balance for week 1 should be keyed in according to the last month's cash books, which is in the control box Cell D7 and F7. The remaining columes are formula equal to previous  week's estimated balance in Row 70.
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		M
		This is a  direct replenishment the CO receives from Treasury so the amount of funding is shown in week 1.  $669,000 represents imprest level of $725,000 less previous month ending cashbook balance, rounded.
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		N
		This is a fomula adding up all cash inflows and outflows.
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		O
		When COs are short of cash (When LC accounts is negative in this example), they should transfer funding from their USD account to their LC account to cover the shortage. )
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		P
		This row shows the actual funds transferred from USD account to LC account, or "conversion", Which make the cash balance on LC account more than 0 but not exceeding 25% of weekly utilization level. 
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		It's the row COs can manage based on their cash liquidity. 
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Q
		This is a formula adding up Row 60 and 63.
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		R
		Any anticipated difficiency (negative number in Row 65) flags that the CO either needs additional replenishment from the Treasury for exceeding the imprest level.  Actively manage this can 
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		avoid the delay in receiving additional replenishment or authorization. 
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		S
		Row 67 in the sample Model show the minimum amount COs should require from the Treasure to cover their cash shortage.  However, we expect COs to round up the figure as shown in sample Model with amount of $350,000.  COs 
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		should actively manage the amount of additional HQ Funding when the Net Cash Flow after Conversion (Row 65) starts to turn negative, to make sure the Est. Weekly Cash Balance (Row 70) keep positive in every
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		week.
		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		T
		The final estimated weekly cash balance which CO should have by projecting the above cash flow cautiously.

The words contained in this file might help you see if this file matches what you are looking for:

...Sheet detailed cash flow projection undp country office weekly cashflow control box month and year projected octorber usd lc exchange rate imprest level beg balance of the column components week monthly total timing local curr inflows from sources a receipts govt contribution project nex depends on projects b c d agency execution subtotal agencies others every outflows e proj advanceexpenditure beginning quarters varies f non agy g fixed expense salary allowance last fees paid to consultant services premisesecurity rent utility phone vehicle operating supplies amp z miscellanies h variable capital spending pc equip travel expenses activity i j k net l na m hq funding n before conversion o anticipated conversionhq needs p actual made q after r indication exceeding s addt t est amount inflow governments each contract insert revise or delete rows based your real received upfront by treasury in co currency annual installment february remaining semiannual installments...

Related files

Share

Help

UNDP Country Office Weekly Detailed Cashflow Projection Sheet
Control Box
Month and Year Projected		Octorber	2001
		USD $	LC
UNDP Exchange Rate		1	145
Imprest Level		725,000	0
Beg. Cash Balance of the month		56,000	1,141,000

Column 1		Column 2										Column 3		Column 4
Cash Flow Components		Week 1	Week 1	Week 2	Week 2	Week 3	Week3	Week 4	Week4	Week 5	Week 5	Monthly Total	Monthly Total	Timing of Cash Flow
Cash Flow Components		USD	Local Curr.	USD	Local Curr.	USD	Local Curr.	USD	Local Curr.	USD	Local Curr.	USD	Local Curr.
*Inflows from Local Sources:*
A	Receipts from Govt. Contribution
a	Project 1 NEX	0	0	0	0	0	0	0	0	0	0	0	0	Depends on projects
b	Project 2 NEX	0	0	0	0	0	0	0	0	0	0	0	0	Depends on projects
c	Project 3 NEX	0	10,150,000	0	0	0	0	0	0	0	0	0	10,150,000	Depends on projects
d	Project 4 Agency execution	0	0	0	0	0	0	0	0	0	0	0	0	Depends on projects
	Subtotal	0	10,150,000	0	0	0	0	0	0	0	0	0	10,150,000
B	Receipts from Agencies
	Subtotal	22,500	0	0	0	0	0	0	0	0	0	22,500	0	Week 1
C	Receipts from Others
	Subtotal	2,100	130,500	2,100	130,500	2,100	130,500	2,100	130,500	2,100	130,500	10,500	652,500	Every week

D	Total Inflows from Local Sources	24,600	10,280,500	2,100	130,500	2,100	130,500	2,100	130,500	2,100	130,500	33,000	10,802,500

*Outflows:*
E	Proj. Advance/Expenditure
	Project 1 NEX	(144,000)	(5,220,000)	0	0	0	0	0	0	0	0	(144,000)	(5,220,000)	Beginning of quarters
	Project 2 NEX	(120,000)	0	0	0	0	0	0	0	0	0	(120,000)	0	Beginning of quarters
	Project 3 NEX	(100,000)	(14,500,000)	0	0	0	0	0	0	0	0	(100,000)	(14,500,000)	Beginning of quarters
	Project 4 Agency execution	0	0	(75,000)	0	(25,000)	0	0	0	(50,000)	0	(150,000)	0	Varies
	Subtotal	(364,000)	(19,720,000)	(75,000)	0	(25,000)	0	0	0	(50,000)	0	(514,000)	(19,720,000)
F	Non UNDP Agy
	Subtotal	0	(543,750)	(15,000)	(418,750)	(20,000)	(500,000)	(5,000)	(435,000)	(35,000)	(821,250)	(75,000)	(2,718,750)	Every week
G	Fixed Expense
	Salary and Allowance	0	0	0	0	0	0	0	0	(225,000)	(7,612,500)	(225,000)	(7,612,500)	The Last week
	Fees paid to Consultant and Services	0	0	(5,000)	(100,000)	0	0	(5,000)	(100,000)	0	0	(10,000)	(200,000)	Week 2,4
	Premise/Security	0	(3,625,000)	0	0	0	0	0	0	0	0	0	(3,625,000)	Week 1
	Rent	(15,000)	0	0	0	0	0	0	0	0	0	(15,000)	0	Week 1
	Utility	0	0	0	(1,631,250)	0	0	0	0	0	0	0	(1,631,250)	Week 2
	Phone	0	0	0	0	(1,500)	(870,000)	0	0	0	0	(1,500)	(870,000)	Week 3
	Vehicle operating	(375)	(489,375)	(375)	(489,375)	(375)	(489,375)	(375)	(489,375)	(375)	(489,375)	(1,875)	(2,446,875)	Every Week
	Office supplies	0	0	(2,500)	(543,750)	0	0	(2,500)	(543,750)	0	0	(5,000)	(1,087,500)	Week 2 & 4
z	Miscellanies	(200)	(116,000)	(200)	(116,000)	(200)	(116,000)	(200)	(116,000)	(200)	(116,000)	(1,000)	(580,000)	Every Week
	Subtotal	(15,575)	(4,230,375)	(8,075)	(2,880,375)	(2,075)	(1,475,375)	(8,075)	(1,249,125)	(225,575)	(8,217,875)	(259,375)	(18,053,125)
H	Variable Expense
	Capital spending - PC, equip	(200)	(12,000)	(200)	(12,000)	(200)	(12,000)	(200)	(12,000)	(200)	(12,000)	(1,000)	(60,000)	Varies
	Travel Expenses	(500)	0	0	(31,300)	0	0	(3,500)	(15,500)	0	0	(4,000)	(46,800)	Varies
	Activity Fees	0	0	0	(52,200)	(40)	(52,200)	0	0	0	(52,200)	(40)	(156,600)	Varies
	Miscellanies	(200)	(11,000)	(21,800)	(32,600)	(200)	(11,000)	(200)	(11,000)	(200)	(11,000)	(22,600)	(76,600)	Every Week
	Subtotal	(900)	(23,000)	(22,000)	(128,100)	(440)	(75,200)	(3,900)	(38,500)	(400)	(75,200)	(27,640)	(340,000)
I	Miscellanies	0	0	0	0	0	0	0	0	0	0	0	0

J	Total Outflows:	(380,475)	(24,517,125)	(120,075)	(3,427,225)	(47,515)	(2,050,575)	(16,975)	(1,722,625)	(310,975)	(9,114,325)	(876,015)	(40,831,875)

K	*Net Cash Flow from Local Sources*	(355,875)	(14,236,625)	(117,975)	(3,296,725)	(45,415)	(1,920,075)	(14,875)	(1,592,125)	(308,875)	(8,983,825)	(843,015)	(30,029,375)

L	Beginning Balance	*56,000*	*1,141,000*	269,125	1,404,375	137,150	137,650	78,735	102,575	52,860	105,450	N/A	N/A
M	HQ Funding	*669,000*	0	0	0	0	0	0	0	0	0	669,000	0	Week 1
N	*Net Cash Balance before Conversion*	369,125	(13,095,625)	151,150	(1,892,350)	91,735	(1,782,425)	63,860	(1,489,550)	(256,015)	(8,878,375)	419,855	(27,138,325)

O	Anticipated Conversion/HQ Funding	(90,315)	13,095,625	(13,051)	1,892,350	(12,293)	1,782,425	(10,273)	1,489,550	(61,230)	8,878,375	(187,161)	27,138,325	Depends on cash needs
P	Actual Conversion made	(100,000)	14,500,000	(14,000)	2,030,000	(13,000)	1,885,000	(11,000)	1,595,000	(70,000)	10,150,000	(208,000)	30,160,000	Depends on cash needs

Q	Net Cash Balance after Conversion	269,125	1,404,375	137,150	137,650	78,735	102,575	52,860	105,450	(326,015)	1,271,625	211,855	3,021,675

R	Indication of Exceeding Imprest Level	0	N/A	0	N/A	0	N/A	0	N/A	326,015	N/A	326,015	N/A	Depends on cash needs
S	Addt'l HQ Funding	0	0	0	0	0	0	0	0	350,000	0	350,000	0	Depends on cash needs

T	*Est. Weekly Cash Balance*	269,125	1,404,375	137,150	137,650	78,735	102,575	52,860	105,450	23,985	1,271,625	N/A	N/A

A	The timing and the amount of cash inflow from governments depends on each project's contract. Insert, revise or delete rows before the subtotal, based on your real projects.
a	Received upfront by Treasury in USD.
b	Received by CO in local currency annual installment in February
c	Received $140,000, or 50% by Treasury in USD, remaining in local currency 20,300,000 semi-annual installments in Apr and Oct.
d	Existing project with contributions received already.
B	Based on agreements with agencies on Cost Recovery for Common Services. CO should have a great degree of control when to collect the funds.Insert rows before the subtotal, based on real situation.
C	Input all other cash receipts in this category. Add rows for details before the subtotal if needed.
D	This is a formula adding up all of the previous subtotals. When inserting new inflow categories, make sure the formula stays relevant.
E	The disbursement for each project depends on its contact. Insert, revise, or delete rows before the subtotal, based on real projects.
F	CO should require UN and agencies provide monthly forecast for their Non-UNDP payment amounts, timing, etc. which can be fine tuned when actual payment requests are received. Add rows before subtotal if needed.
G	This is a example. The weekly amounts are based on the monthly estimated expenses and filled in the corresponding week. For simplicity sake, vehicle operating and miscellanious expenses are evenly disbursed in 5 weeks.
	CO should have certain amount and timing for each component of its own fixed expenses due to recurring nature.
H	Based on estimated weekly number to be fine tuned with actual invoices received.
I	All other expenses other than the above ones should be input in this category.
J	This is a formula. If more breakdown on type of outflows is needed, simply incert more rows but make sure the formula stays relevant.
K	This is a formula adding total inflows and outflows from local sources.
L	The balance for week 1 should be keyed in according to the last month's cash books, which is in the control box Cell D7 and F7. The remaining columes are formula equal to previous week's estimated balance in Row 70.
M	This is a direct replenishment the CO receives from Treasury so the amount of funding is shown in week 1. $669,000 represents imprest level of $725,000 less previous month ending cashbook balance, rounded.
N	This is a fomula adding up all cash inflows and outflows.
O	When COs are short of cash (When LC accounts is negative in this example), they should transfer funding from their USD account to their LC account to cover the shortage. )
P	This row shows the actual funds transferred from USD account to LC account, or "conversion", Which make the cash balance on LC account more than 0 but not exceeding 25% of weekly utilization level.
	It's the row COs can manage based on their cash liquidity.
Q	This is a formula adding up Row 60 and 63.
R	Any anticipated difficiency (negative number in Row 65) flags that the CO either needs additional replenishment from the Treasury for exceeding the imprest level. Actively manage this can
	avoid the delay in receiving additional replenishment or authorization.
S	Row 67 in the sample Model show the minimum amount COs should require from the Treasure to cover their cash shortage. However, we expect COs to round up the figure as shown in sample Model with amount of $350,000. COs
	should actively manage the amount of additional HQ Funding when the Net Cash Flow after Conversion (Row 65) starts to turn negative, to make sure the Est. Weekly Cash Balance (Row 70) keep positive in every
	week.
T	The final estimated weekly cash balance which CO should have by projecting the above cash flow cautiously.

UNDP Country Office Simplified Weekly Cash Projection Sheet

Month and Year Projected		Octorber	2001
		USD $	LC
UNDP Exchange Rate		1	145
Imprest Level		725,000	0
Beg. Cash Balance of the month		56,000	1,141,000


Cash Flow Components		Week 1	Week 1	Week 2	Week 2	Week 3	Week3	Week 4	Week4	Week 5	Week 5	Monthly Total	Monthly Total
Cash Flow Components		USD	Local Curr.	USD	Local Curr.	USD	Local Curr.	USD	Local Curr.	USD	Local Curr.	USD	Local Curr.

*Inflows from Local Sources:*
A	Receipts from Govt. Contribution	0	10,150,000	0	0	0	0	0	0	0	0	0	10,150,000
B	Receipts from Agencies	22,500	0	0	0	0	0	0	0	0	0	22,500	0
C	Receipts from Others	2,100	130,500	2,100	130,500	2,100	130,500	2,100	130,500	2,100	130,500	10,500	652,500
D	Total Inflows from Local Sources:	24,600	10,280,500	2,100	130,500	2,100	130,500	2,100	130,500	2,100	130,500	33,000	10,802,500

*Outflows:*
E	Disbursement - Proj. Advance/Expenditure	(364,000)	(19,720,000)	(75,000)	0	(25,000)	0	0	0	(50,000)	0	(514,000)	(19,720,000)
F	Disbursement - Non UNDP Agy	0	(543,750)	(15,000)	(418,750)	(20,000)	(500,000)	(5,000)	(435,000)	(35,000)	(821,250)	(75,000)	(2,718,750)
G	Disbursement - Fixed Expense	(15,575)	(4,230,375)	(8,075)	(2,880,375)	(2,075)	(1,475,375)	(8,075)	(1,249,125)	(225,575)	(8,217,875)	(259,375)	(18,053,125)
H	Disbursement - Variable Expense	(900)	(23,000)	(22,000)	(128,100)	(440)	(75,200)	(3,900)	(38,500)	(400)	(75,200)	(27,640)	(340,000)
I	Misllanies	0	0	0	0	0	0	0	0	0	0	0	0
J	Total Outflows:	(380,475)	(24,517,125)	(120,075)	(3,427,225)	(47,515)	(2,050,575)	(16,975)	(1,722,625)	(310,975)	(9,114,325)	(876,015)	(40,831,875)

K	*Net Cash Flow from Local Sources*	(355,875)	(14,236,625)	(117,975)	(3,296,725)	(45,415)	(1,920,075)	(14,875)	(1,592,125)	(308,875)	(8,983,825)	(843,015)	(30,029,375)

L	Beginning Balance	56,000	1,141,000	269,125	1,404,375	137,150	137,650	78,735	102,575	52,860	105,450	N/A	N/A
M	HQ Funding	669,000	0	0	0	0	0	0	0	0	0	669,000	0

N	*Net Cash Flow before Conversion*	369,125	(13,095,625)	151,150	(1,892,350)	91,735	(1,782,425)	63,860	(1,489,550)	(256,015)	(8,878,375)	419,855	(27,138,325)

O	Anticipated Conversion/HQ Funding	(90,315)	13,095,625	(13,051)	1,892,350	(12,293)	1,782,425	(10,273)	1,489,550	(61,230)	8,878,375	(187,161)	27,138,325
P	Actual Conversion made	(100,000)	14,500,000	(14,000)	2,030,000	(13,000)	1,885,000	(11,000)	1,595,000	(70,000)	10,150,000	(208,000)	30,160,000

Q	Net Cash Flow after Conversion	269,125	1,404,375	137,150	137,650	78,735	102,575	52,860	105,450	(326,015)	1,271,625	211,855	3,021,675

R	Indication of Exceeding Imprest Level	0	N/A	0	N/A	0	N/A	0	N/A	326,015	N/A	326,015	N/A
S	Addt'l HQ Funding	0	0	0	0	0	0	0	0	350,000	0

T	*Est. Weekly Cash Balance*	269,125	1,404,375	137,150	137,650	78,735	102,575	52,860	105,450	23,985	1,271,625	N/A	N/A

A	The timing and the amount of cash inflow from governments depends on each project's contract. Insert, revise or delete rows before the subtotal, based on your real projects.
B	Based on agreements with agencies on Cost Recovery for Common Services. CO should have a great degree of control when to collect the funds.Insert rows before the subtotal, based on real situation.
C	Input all other cash receipts in this category. Add rows for details before the subtotal if needed.
D	This is a formula adding up all of the previous subtotals. When inserting new inflow categories, make sure the formula stays relevant.
E	The disbursement for each project depends on its contact. Insert, revise, or delete rows before the subtotal, based on real projects.
F	CO should require UN and agencies provide monthly forecast for their Non-UNDP payment amounts, timing, etc. which can be fine tuned when actual payment requests are received. Add rows before subtotal if needed.
G	This is a example. The weekly amounts are based on the monthly estimated expenses and filled in the corresponding week. For simplicity sake, vehicle operating and miscellanious expenses are evenly disbursed in 5 weeks.
	CO should have certain amount and timing for each component of its own fixed expenses due to recurring nature.
H	Based on estimated weekly number to be fine tuned with actual invoices received.
I	All other expenses other than the above ones should be input in this category.
J	This is a formula. If more breakdown on type of outflows is needed, simply incert more rows but make sure the formula stays relevant.
K	This is a formula adding total inflows and outflows from local sources.
L	The balance for week 1 should be keyed in according to the last month's cash books, which is in the control box Cell D7 and F7. The remaining columes are formula equal to previous week's estimated balance in Row 70.
M	This is a direct replenishment the CO receives from Treasury so the amount of funding is shown in week 1. $669,000 represents imprest level of $725,000 less previous month ending cashbook balance, rounded.
N	This is a fomula adding up all cash inflows and outflows.
O	When COs are short of cash (When LC accounts is negative in this example), they should transfer funding from their USD account to their LC account to cover the shortage. )
P	This row shows the actual funds transferred from USD account to LC account, or "conversion", Which make the cash balance on LC account more than 0 but not exceeding 25% of weekly utilization level.
	It's the row COs can manage based on their cash liquidity.
Q	This is a formula adding up Row 60 and 63.
R	Any anticipated difficiency (negative number in Row 65) flags that the CO either needs additional replenishment from the Treasury for exceeding the imprest level. Actively manage this can
	avoid the delay in receiving additional replenishment or authorization.
S	Row 67 in the sample Model show the minimum amount COs should require from the Treasure to cover their cash shortage. However, we expect COs to round up the figure as shown in sample Model with amount of $350,000. COs
	should actively manage the amount of additional HQ Funding when the Net Cash Flow after Conversion (Row 65) starts to turn negative, to make sure the Est. Weekly Cash Balance (Row 70) keep positive in every
	week.
T	The final estimated weekly cash balance which CO should have by projecting the above cash flow cautiously.

Related files

Share

Share to social media

Help

Login Area