Broussard Skateboard's sales are expected to increase by 25% from $7.6 million in 2019 to $9.50 million in 2020. Its assets totaled $5 million at the end of 2019. Broussard is already at full capacity, so its assets must grow at the same rate as projected sales. At the end of 2019, current liabilities were $1.4 million, consisting of $450,000 of accounts payable, $500,000 of notes payable, and $450,000 of accruals. The after-tax profit margin is forecasted to be 3%, and the forecasted payout ratio is 65%. Use the AFN equation to forecast Broussard's additional funds needed for the coming year. Enter your answer in dollars. For example, an answer of $1.2 million should be entered as $1,200,000. Do not round intermediate calculations. Round your answer to the nearest dollar.

Transcribed Image Text:

You were recently hired by Friendly Robots (FR) to be their bookkeeper. FR imports, customizes and sells a single product, the Friendly Fast Food Cook, a robot. That robot is sold to different fast-food restaurants to replace the manual labour. The robots can cook hamburgers, french frieds, and other fast-food products. One of your first tasks is to prepare a budget and the pro-forma financial statements for the next quarter. You have been provided with the following financial information to as The opening balance sheet - also provided in a separate tab. Friendly Robots Balance Sheet 9/31/2021 Cash $12,100 Accounts payable $34,000 Accounts receivable 118,020 Short term loan 0 Inventory 20,000 Taxes payable 13,500 Prepaid insurance 18,000 Total current liabilities 47,500 Total current assets 168,120 Long term loan payable 96,000 Total liabilities 143,500 Property, plant and equipment 280,101 Common shares 150,000 Accumulated depreciation 35,140 Retained earnings 119,581 244,961 Total shareholders equity Net property, plant and equipment Total assets 269,581 $413,081 $413,081 Total liabilities and equity 2. FM sells its robots for $4,200 each. Recent and forecasted sales (in units) are as follows: July (actual) 30 August (actual) 25 September (actual) 32 October 40 November 45 December 42 January 48 February March 52 50 3. Management like to have enough robots on hand to cover 25% of the next months expected sales. This policy was achieved in the third quarter of 2021 as the comp on hand as of September 30th. FR purchases the generic robots for $2,000 per unit. Purchases are paid for 50% in the month of purchase and the remaining 50% the follo 4. All sales are on credit with 20% being collected in the month of sale, 70% being collected the month after sale and the remaining 10% being collected two months aft 5. Fixed operating expenses are $65,000 every month, including $20,00 in depreciation. Included in fixed operating expenses is $2,000 per month for insurance, which is The company plans to pay a dividend of $5,000 in December. 6. 7. The company will be purchasing a truck to be used for deliveries in November. The expected cost will be $80,000, paid with cash. 8. Interest is paid monthly on the long term debt at the rate of 5% per year. The remaining balance of the loan will be repaid in 2025. 9. Income taxes are estimated to be 20% of earnings before taxes. FR pays income tax instalments of $5,000 every month. 10. The terms of the long term debt require FR to maintain a minimum cash balance of $10,000. A line of credit is available to cover any shortfall. 11. Interest is paid monthly on the previous months line of credit balance at 8% per year. Any cash above the $10,000 balance at the end of the month will be used to rep Required: Use Excel to complete this assignment. Each student is to create his/her own Excel file, and complete the assignment individually. Use formulas wherever possible. Your spreads The items in the budget should appear in the following order: 1. The balance sheet for September 30, 2021 (as given).. 2. A cash receipts schedule for October, November and December. Check figure: Cash collections for October should be $138,180 3. A purchases schedule in units for October, November, and December. Check figure: October purchases should be 41 unit. 4. A cash payments schedule for purchases in October, November and December. Check figure: October cash payment for purchases should be $75,250 5. A cash payments schedule for October, November and December Check figure: October's total cash payments should be $129,650 6. A cash budget for October, November and December, including a calculation of cumulative loan at the bottom. Check figure At the end of November the cash balance should be $10,000 and the cumulative loan should be $41,180. 7. The pro-forma income statements for October, November and December. You should also have a total column which totals all three months. i. Subtotals for EBIT and EBT should be included. ii. List all expenses separately (do not combine). iii. Show long-term and short-term interest separately. iv. Hint: Cost of goods sold is not the same thing as purchases. Check figure: October earnings after taxes should be $13,280. 8. A pro-forma retained earnings schedule for the quarter ended December 31st. Check figure: Ending retained earnings should be $165,681. 9. A pro-forma balance sheet at December 31st. Hint: Consider what will cause balances to change from the September 30 2021 balance sheet. Check figure: Total assets should be $470,981. Sales (in units) Selling price Sales (in $) Sales Current month Prior month Two months Cash collections Sales (in units) Desired ending inventory Total needs Beginning inventory Required purchases Cost per unit Purchases Purchases Current month Prior month Sales Budget August September October November December Quarter January February Schedule of Cash Collections August September October November December Quarter January February Purchases Budget August September October November December Quarter January February Cash Disbursements for Purchases August September October November December Quarter January February Cash Payments Schedule August September October November December Quarter January February Friendly Robots Income Statement For the Quarter Ended December 31, 2021 October Friendly Robots Statement of Retained Earnings For the Quarter Ended December 31, 2021 Friendly Robots Balance Sheet 31-Dec-21 November December Total quarter You were recently hired by Friendly Robots (FR) to be their bookkeeper. FR imports, customizes and sells a single product, the Friendly Fast Food Cook, a robot. That robot is sold to different fast-food restaurants to replace the manual labour. The robots can cook hamburgers, french frieds, and other fast-food products. One of your first tasks is to prepare a budget and the pro-forma financial statements for the next quarter. You have been provided with the following financial information to as The opening balance sheet - also provided in a separate tab. Friendly Robots Balance Sheet 9/31/2021 Cash $12,100 Accounts payable $34,000 Accounts receivable 118,020 Short term loan 0 Inventory 20,000 Taxes payable 13,500 Prepaid insurance 18,000 Total current liabilities 47,500 Total current assets 168,120 Long term loan payable 96,000 Total liabilities 143,500 Property, plant and equipment 280,101 Common shares 150,000 Accumulated depreciation 35,140 Retained earnings 119,581 244,961 Total shareholders equity Net property, plant and equipment Total assets 269,581 $413,081 $413,081 Total liabilities and equity 2. FM sells its robots for $4,200 each. Recent and forecasted sales (in units) are as follows: July (actual) 30 August (actual) 25 September (actual) 32 October 40 November 45 December 42 January 48 February March 52 50 3. Management like to have enough robots on hand to cover 25% of the next months expected sales. This policy was achieved in the third quarter of 2021 as the comp on hand as of September 30th. FR purchases the generic robots for $2,000 per unit. Purchases are paid for 50% in the month of purchase and the remaining 50% the follo 4. All sales are on credit with 20% being collected in the month of sale, 70% being collected the month after sale and the remaining 10% being collected two months aft 5. Fixed operating expenses are $65,000 every month, including $20,00 in depreciation. Included in fixed operating expenses is $2,000 per month for insurance, which is The company plans to pay a dividend of $5,000 in December. 6. 7. The company will be purchasing a truck to be used for deliveries in November. The expected cost will be $80,000, paid with cash. 8. Interest is paid monthly on the long term debt at the rate of 5% per year. The remaining balance of the loan will be repaid in 2025. 9. Income taxes are estimated to be 20% of earnings before taxes. FR pays income tax instalments of $5,000 every month. 10. The terms of the long term debt require FR to maintain a minimum cash balance of $10,000. A line of credit is available to cover any shortfall. 11. Interest is paid monthly on the previous months line of credit balance at 8% per year. Any cash above the $10,000 balance at the end of the month will be used to rep Required: Use Excel to complete this assignment. Each student is to create his/her own Excel file, and complete the assignment individually. Use formulas wherever possible. Your spreads The items in the budget should appear in the following order: 1. The balance sheet for September 30, 2021 (as given).. 2. A cash receipts schedule for October, November and December. Check figure: Cash collections for October should be $138,180 3. A purchases schedule in units for October, November, and December. Check figure: October purchases should be 41 unit. 4. A cash payments schedule for purchases in October, November and December. Check figure: October cash payment for purchases should be $75,250 5. A cash payments schedule for October, November and December Check figure: October's total cash payments should be $129,650 6. A cash budget for October, November and December, including a calculation of cumulative loan at the bottom. Check figure At the end of November the cash balance should be $10,000 and the cumulative loan should be $41,180. 7. The pro-forma income statements for October, November and December. You should also have a total column which totals all three months. i. Subtotals for EBIT and EBT should be included. ii. List all expenses separately (do not combine). iii. Show long-term and short-term interest separately. iv. Hint: Cost of goods sold is not the same thing as purchases. Check figure: October earnings after taxes should be $13,280. 8. A pro-forma retained earnings schedule for the quarter ended December 31st. Check figure: Ending retained earnings should be $165,681. 9. A pro-forma balance sheet at December 31st. Hint: Consider what will cause balances to change from the September 30 2021 balance sheet. Check figure: Total assets should be $470,981. Sales (in units) Selling price Sales (in $) Sales Current month Prior month Two months Cash collections Sales (in units) Desired ending inventory Total needs Beginning inventory Required purchases Cost per unit Purchases Purchases Current month Prior month Sales Budget August September October November December Quarter January February Schedule of Cash Collections August September October November December Quarter January February Purchases Budget August September October November December Quarter January February Cash Disbursements for Purchases August September October November December Quarter January February Cash Payments Schedule August September October November December Quarter January February Friendly Robots Income Statement For the Quarter Ended December 31, 2021 October Friendly Robots Statement of Retained Earnings For the Quarter Ended December 31, 2021 Friendly Robots Balance Sheet 31-Dec-21 November December Total quarter

Answer rating: 100% (QA)

Increase in assets Total assets growth rate 5000000 25 1250000 Increase in spontaneous li... View the full answer