Finance 2 Budgeting Project Example Inc. has just closed the books for the year ending December 31, 2019. Based on the information provided, answer the questions that follow December 31, 2019 Balance Sheet ASSETS LIABILITIES AND FOUITY Cash $50.000 Accounts Payable 5307050 Accounts Receivable, net 6,930,000 Salary Payable 329,875 Inventories 0 Interest Payable 458,333 Finished Goods Bank Line (896) 600.000 330,000 Raw Materials 59.100 389 100 Current Assets 7.369 100 Current Liabilities 1,695,258 Bond (1095) 5.000.000 Fixed Assets, net of accumulated depreciation 5.542.600 Total Liabilities 6,695,258 Common Stock 1,450,000 Retained Earnings 4.766_142 Total Equity 6216442 Total Assets $12.9111700Total Liabilities & Equity $12.911,700 *** Inventory Finished Goods represents 15,000 units of finished goods at a cost of $22.00 per unit, Raw materials for 19,700 lbs at $3.00 per pound, Work in process inventory is zero at December 31 The company is now in the process of finishing its projections for the year 2020. Historical sales for the last quarter as well as sales projections for the next thirteen months are shown below. Month Total Sales Units Sald and Sales Price October 2012 4200,000 (100,000 units at $42.00 each) November 2019 3200,000 (100,000 units at $42.00 each) December 2019 4200,000 {{4100.000 units at $42.00 each) January 2020 4200.000 (100.000 units at $42.00 eah) February 3.780.000 (90,000 units at $42.00 each March 3,360,000 (80,000 units at $42.00 each April 2 940,000 (20.000 units at $42.00 each May 3.360,000 (80.000 units at $42.00 each) June 3.780.000 (90.000 units at $42.00 each) July 4 200.000 (100 000 units at $42.00 each) August 4284,000 (102.000 units at $42.00 each September 4410.000 105.000 units at $42.00 each) October 4494000 107.000 units at $42.00.cach) November 4,620,000 (110 000 units at $42.00 each December 4,620,000 (110,000 units at $42.00 each) Monthly sales 2019 4.620,000 (110.000 units at $42.00 each) For forecasting purposes the company assumes 30% of sales are collected the month following the sale, 70% in the second month after the sale. Desired ending finished goods inventory is 20% of next month's snies. Purchases of raw materials are made in the month they are used in production and each unit produced requires 2 pounds of material The cost per pound is $3.00 Payments for raw materials are made 50% in the month of purchase and 50% in the month after purchase. Raw material desired ending inventory is 10% of next month's material required for production Labor costs are $13.00 per unit and are paid 75% in the month incurred and 25% in the following month. Direct labor rates have been increase from $11.00 in 2019 to $13.00 in 2020 Production overhead is allocated at $5.00 per unit (PDOHR) regardless of the number of units produced in a month, but the actual cash overhead expense is $475,000 per month paid in the month incurred Selling and administrative total fixed costs are $250,000 per month and sales commissions are $12.00 per unit sold both are paid monthly Current depreciation is $25,000 per month. This is on equipment used in the office. All production equipment is leased and therefore no depreciation is necessary Interest on the line is paid at the end of each month based on the ending loan balance at the end of the previous month. The interest on the Bond in paid every January (Interest on the bond is accrued monthly) Additional office equipment will be purchased at the end of January and July for $600,000 each and will be added to fixed assets. The equipment will be depreciated straight-line over ten years Targeted Cash at each month end is $50,000, with any excess cash being used to pay down the Bank line of credit and any shortfall below $50,000 funded by borrowing against the line. Any excess cash above $50,000 will be invested once the bank line of credit has been completely paid off (For simplicity, assume any investment does not yield any monthly cash flow and do the interest.) not accr REQUIRED 1. For the 12 months of 2020 a Calculated the number of units to be produced each month b. Calculate the cost of producing these units. e. Compute the value of the ending inventory for each quarter 2. Construct a pro forma income statement for cach quarter. (Note that to complete the second third and fourth quarters you need to complete steps 3-5 below for the previous quarter) 3. Construct a schedule of cash receipts for each of the months. 4. Construct a schedule of cash payments for each of the months. 5. Construct a complete monthly cash budget including borrowing and repayment on the bank line of credit. Assume the company can borrow on the first of the month and repay on the last day of the month Finance 2 Budgeting Project Example Inc. has just closed the books for the year ending December 31, 2019. Based on the information provided, answer the questions that follow December 31, 2019 Balance Sheet ASSETS LIABILITIES AND FOUITY Cash $50.000 Accounts Payable 5307050 Accounts Receivable, net 6,930,000 Salary Payable 329,875 Inventories 0 Interest Payable 458,333 Finished Goods Bank Line (896) 600.000 330,000 Raw Materials 59.100 389 100 Current Assets 7.369 100 Current Liabilities 1,695,258 Bond (1095) 5.000.000 Fixed Assets, net of accumulated depreciation 5.542.600 Total Liabilities 6,695,258 Common Stock 1,450,000 Retained Earnings 4.766_142 Total Equity 6216442 Total Assets $12.9111700Total Liabilities & Equity $12.911,700 *** Inventory Finished Goods represents 15,000 units of finished goods at a cost of $22.00 per unit, Raw materials for 19,700 lbs at $3.00 per pound, Work in process inventory is zero at December 31 The company is now in the process of finishing its projections for the year 2020. Historical sales for the last quarter as well as sales projections for the next thirteen months are shown below. Month Total Sales Units Sald and Sales Price October 2012 4200,000 (100,000 units at $42.00 each) November 2019 3200,000 (100,000 units at $42.00 each) December 2019 4200,000 {{4100.000 units at $42.00 each) January 2020 4200.000 (100.000 units at $42.00 eah) February 3.780.000 (90,000 units at $42.00 each March 3,360,000 (80,000 units at $42.00 each April 2 940,000 (20.000 units at $42.00 each May 3.360,000 (80.000 units at $42.00 each) June 3.780.000 (90.000 units at $42.00 each) July 4 200.000 (100 000 units at $42.00 each) August 4284,000 (102.000 units at $42.00 each September 4410.000 105.000 units at $42.00 each) October 4494000 107.000 units at $42.00.cach) November 4,620,000 (110 000 units at $42.00 each December 4,620,000 (110,000 units at $42.00 each) Monthly sales 2019 4.620,000 (110.000 units at $42.00 each) For forecasting purposes the company assumes 30% of sales are collected the month following the sale, 70% in the second month after the sale. Desired ending finished goods inventory is 20% of next month's snies. Purchases of raw materials are made in the month they are used in production and each unit produced requires 2 pounds of material The cost per pound is $3.00 Payments for raw materials are made 50% in the month of purchase and 50% in the month after purchase. Raw material desired ending inventory is 10% of next month's material required for production Labor costs are $13.00 per unit and are paid 75% in the month incurred and 25% in the following month. Direct labor rates have been increase from $11.00 in 2019 to $13.00 in 2020 Production overhead is allocated at $5.00 per unit (PDOHR) regardless of the number of units produced in a month, but the actual cash overhead expense is $475,000 per month paid in the month incurred Selling and administrative total fixed costs are $250,000 per month and sales commissions are $12.00 per unit sold both are paid monthly Current depreciation is $25,000 per month. This is on equipment used in the office. All production equipment is leased and therefore no depreciation is necessary Interest on the line is paid at the end of each month based on the ending loan balance at the end of the previous month. The interest on the Bond in paid every January (Interest on the bond is accrued monthly) Additional office equipment will be purchased at the end of January and July for $600,000 each and will be added to fixed assets. The equipment will be depreciated straight-line over ten years Targeted Cash at each month end is $50,000, with any excess cash being used to pay down the Bank line of credit and any shortfall below $50,000 funded by borrowing against the line. Any excess cash above $50,000 will be invested once the bank line of credit has been completely paid off (For simplicity, assume any investment does not yield any monthly cash flow and do the interest.) not accr REQUIRED 1. For the 12 months of 2020 a Calculated the number of units to be produced each month b. Calculate the cost of producing these units. e. Compute the value of the ending inventory for each quarter 2. Construct a pro forma income statement for cach quarter. (Note that to complete the second third and fourth quarters you need to complete steps 3-5 below for the previous quarter) 3. Construct a schedule of cash receipts for each of the months. 4. Construct a schedule of cash payments for each of the months. 5. Construct a complete monthly cash budget including borrowing and repayment on the bank line of credit. Assume the company can borrow on the first of the month and repay on the last day of the month