Sales Budget November August September October December Quarter January February Sales (in units) Selling price Sales (in $) Schedule of Cash Collections October November December August September Quarter January February Sales Current month Prior month Two months Cash collections Purchases Budget November December August September October Quarter January February Sales (in units) Desired ending inventory Total needs Beginning inventory Required purchases Cost per unit Purchases Cash Disbursements for Purchases October November December August September Quarter January February Purchases Current month Prior month Cash Payments Schedule October November December August September Quarter January February Friendly Robots Income Statement For the Quarter Ended December 31, 2021 October November December Total quarter Friendly Robots Statement of Retained Earnings For the Quarter Ended December 31, 2021 Friendly Robots Balance Sheet 31-Dec-21 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, trench fries, 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 assist you in your task: The opening balance sheet - also provided in a separate tab. Friendly Robots Balance Sheet 9/31/2021 Cash Accounts receivable Inventory Prepaid insurance Total current assets $12,100 118,020 20,000 18,000 168.120 Accounts payable Short term loan Taxes payable Total current liabilities Longterm loan payable Total liabilities Common shares Retained earning Total shareholders equity Totallibilities and equity $34,000 0 13,500 47,500 96,000 143,500 150,000 119.581 269,581 5413,081 Property, plant and equipment Accumulated depreciation Net property, plant and equipment Totalaus 280.101 35 140 244,961 5413,081 2 Fisells its robots for $4,200 each. Recent and forecasted sales in units are as follows: July (actual) August (actual) September (actual) October November December January February March 10 25 32 40 45 42 45 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 company had 10 robots on hand as of September 30th. FR purchases the generic robots for $2,000 per unit. Purchases are paid for S0% in the month of purchase and the remaining 50% the following month. 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 after the sale 5. Fored 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 paid once a year in July. Variable operating expenses average $150 per robot sold. $. 6. The company plans to pay a dividend of$5,000 in December 7. The company will be purchasing a truck to be used for deliveries in November. The expected cost will be 580,000, paid with cash. . , 3. SX 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 510,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 repay any existing line of credit balance 8. 9 Required Use Excel to complete this assignment. Each student is to create his/her own Excelle, and complete the assignment individually. Use formulas wherever possible. Your spreadsheet should be formatted to show amounts to the nearest dollar (no cents. The items in the budget should appear in the following order 1 The balance sheet for September 30, 2021 fas given) 2 A cash receipts schedule for October, November and December Check figures 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 di un 4. A cash payments schedule for purchases in October November and December Check fgwe: October cash payment for purchases should be 575,250 SA cash payments schedule for October November and December Check figurer 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 lean should be $41,180 7. The pro-forma come statements for October November and December. You should also have a total column which totals all three months Subtotoh for EBIT and EBT should be included List alles separately do not combine) * Show long term and short term interest separately Hint Cost of goods sold is not the same thing as purchases Check fore: October earning after ones should be $13,200 A pro forma retained earnings schedule for the readed Desember Check for Ending retained earning should be $165,681. A proforma balance sheet at December 3, Hint: Consider what will cause balances to change from the September 30 2031 balance sheet 20 Check figure Total assets should be $470.981 . Other information Format For numbers, the Accounting format with a decinal places if your ending balance sheet is out by stor, do not worry about just rounding error Market wil be awarded for a neat will laid out and formatted spreadsheet it should be easy to follow and have a good flow to it Schedules should flow down a worksheet mot side by side. So, the opening balance sheet would be at the top, the cash recipes schedule below Lines should be included to show where calculations occur, with double Ines at the bottom of each schedule. See the balance sheet on the previous page Dollar signs should be used only at the top and bottom of columns in a schedule, not on every number . . Sales Budget November August September October December Quarter January February Sales (in units) Selling price Sales (in $) Schedule of Cash Collections October November December August September Quarter January February Sales Current month Prior month Two months Cash collections Purchases Budget November December August September October Quarter January February Sales (in units) Desired ending inventory Total needs Beginning inventory Required purchases Cost per unit Purchases Cash Disbursements for Purchases October November December August September Quarter January February Purchases Current month Prior month Cash Payments Schedule October November December August September Quarter January February Friendly Robots Income Statement For the Quarter Ended December 31, 2021 October November December Total quarter Friendly Robots Statement of Retained Earnings For the Quarter Ended December 31, 2021 Friendly Robots Balance Sheet 31-Dec-21 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, trench fries, 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 assist you in your task: The opening balance sheet - also provided in a separate tab. Friendly Robots Balance Sheet 9/31/2021 Cash Accounts receivable Inventory Prepaid insurance Total current assets $12,100 118,020 20,000 18,000 168.120 Accounts payable Short term loan Taxes payable Total current liabilities Longterm loan payable Total liabilities Common shares Retained earning Total shareholders equity Totallibilities and equity $34,000 0 13,500 47,500 96,000 143,500 150,000 119.581 269,581 5413,081 Property, plant and equipment Accumulated depreciation Net property, plant and equipment Totalaus 280.101 35 140 244,961 5413,081 2 Fisells its robots for $4,200 each. Recent and forecasted sales in units are as follows: July (actual) August (actual) September (actual) October November December January February March 10 25 32 40 45 42 45 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 company had 10 robots on hand as of September 30th. FR purchases the generic robots for $2,000 per unit. Purchases are paid for S0% in the month of purchase and the remaining 50% the following month. 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 after the sale 5. Fored 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 paid once a year in July. Variable operating expenses average $150 per robot sold. $. 6. The company plans to pay a dividend of$5,000 in December 7. The company will be purchasing a truck to be used for deliveries in November. The expected cost will be 580,000, paid with cash. . , 3. SX 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 510,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 repay any existing line of credit balance 8. 9 Required Use Excel to complete this assignment. Each student is to create his/her own Excelle, and complete the assignment individually. Use formulas wherever possible. Your spreadsheet should be formatted to show amounts to the nearest dollar (no cents. The items in the budget should appear in the following order 1 The balance sheet for September 30, 2021 fas given) 2 A cash receipts schedule for October, November and December Check figures 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 di un 4. A cash payments schedule for purchases in October November and December Check fgwe: October cash payment for purchases should be 575,250 SA cash payments schedule for October November and December Check figurer 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 lean should be $41,180 7. The pro-forma come statements for October November and December. You should also have a total column which totals all three months Subtotoh for EBIT and EBT should be included List alles separately do not combine) * Show long term and short term interest separately Hint Cost of goods sold is not the same thing as purchases Check fore: October earning after ones should be $13,200 A pro forma retained earnings schedule for the readed Desember Check for Ending retained earning should be $165,681. A proforma balance sheet at December 3, Hint: Consider what will cause balances to change from the September 30 2031 balance sheet 20 Check figure Total assets should be $470.981 . Other information Format For numbers, the Accounting format with a decinal places if your ending balance sheet is out by stor, do not worry about just rounding error Market wil be awarded for a neat will laid out and formatted spreadsheet it should be easy to follow and have a good flow to it Schedules should flow down a worksheet mot side by side. So, the opening balance sheet would be at the top, the cash recipes schedule below Lines should be included to show where calculations occur, with double Ines at the bottom of each schedule. See the balance sheet on the previous page Dollar signs should be used only at the top and bottom of columns in a schedule, not on every number