Create a Cash Budget with the information provided: Using excel

Camping produces stuff for horsing around, such as saddles Sales for the next 5 months are projected to be: January 30,000 February 50,000 March 48,000 April 42,000 May 52,000 The following information pertains to the budget assumptions. a. Finished goods inventory on January 1 is expected to be 10000 units. The desired ending FGI for any month (except for the Dec 31 inventory as noted previously) is expected to be 22% of the following month's sales. b. Data for materials used are: Leather 6 yards per harness $35.50 per yard Bone 4 parts per harness $15.00 per part Raw materials ending inventory is always budgeted to equal 15% of the following month's production needs. On December 31, the ending inventory of Leather is expected to be 18,000 yards and for Bone is expected to be 12,000 parts. c. Direct labor used per harness: Labor type hours per harness wage rate Leather worker 5 hours per saddle $17 per hour Assembler worker 4 hours per saddle $15 per hour. d. Overhead each month is estimated at: Fixed per month Variable per DLH (total leatherworker plus assembler) 0.9 0.75 1 Supplies Power Maintenance Supervision Depreciation Taxes Other 25000 28000 90000 8600 86000 1.4 e. Selling and Admin is estimated each to be: variable cost (S per unit sold (cost driver)) Fixed portion (per month) 60000 1.3 Salaries Commissions Depreciation Shipping Other 10000 2.5 0.8 ON bolu 26000 f. Selling price per saddle is $600.00. g. Sales are all on account. 60% of sales are collected in the month of sale and 40% in the month following the sale. Accounts receivable on January 1 is $150,000. h. March is a planned purchase of equipment for $500,000. i Borrowings are at 4% per year interest, and borrowings are assumed to be at the beginning of the month required and at the end of the month of repayment. January 1 beginning cash is 520,000. Interest on borrowings is paid before any principal can be paid. To the extent possible, the company strives to repay any debt at the end of each month if there is cash available. Minimum cash balance at the end of any month is $10,000