Your spreadsheet has a tab named JARVIS which contains sales forecasts and purchase forecasts for Jarvis Solutions. You need this information to complete the cash budget. You are tasked with completing a cash budget for January through April.

Sales forecast are provided in Excel. Collections estimates were obtained from the credit department: 20% are collected during the month of the sale; 60% are collected the month following the sale; and 18% are collected in the second month following the sale. Youll notice that the collections only sum to 98%. This is because 2% of sales are never collected (bad debt). No discounts are offered for early payment. Total purchases for raw material and labor are provided in Excel. Payments for the raw materials and labor are typically made during the month following when the purchase is made (one-month lag). Administrative salaries amount to $17,000 a month; lease payments are $8,200 a month; depreciation expense is $3,500 a month; miscellaneous expenses are $4,245 a month; tax payments are owed in March and total $6,500. Cash on hand on January 1 is $5,500. A minimum cash balance of $4,000 will be maintained throughout the cash budget period.

Requirements:

1. Prepare a monthly cash budget for January through April. Be sure to clearly show the surplus (deficit) of cash.

2. Based on the deficit cash shown on your cash budget, what is the dollar amount that Jarvis should have for a line of credit over this 4-month period? Note: dont add a cushion, just base this amount off of the cash budget

Input Data
Collections during month of sale	20%
Collections during month after sale	60%
Collections during second month after sale	18%
Lease payments	$8,200
Target cash balance	$4,000
General and administrative salaries	$17,000
Depreciation charges	$3,500
Tax payments (March)	$6,500
Miscellaneous expenses	$4,245
Cash on hand January 1	$5,500
	November	December	January	February	March	April
Sales Estimates	$110,000	$83,000	$94,000	$85,000	$70,000	$64,000
Labor and Raw Material Purchases	$53,590	$61,100	$55,250	$45,500	$41,600	$38,350