Please show calculations or formulas

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 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 Long term loan payable Total liabilities Common shares Retained earnings Total shareholders equity Total liabilities and equity $34,000 0 13,500 47,500 96,000 143,500 150,000 119,581 269,581 $413,081 Property, plant and equipment Accumulated depreciation Net property, plant and equipment Total assets 280,101 35,140 244,961 $413,081 2. FM sells 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 30 25 32 40 45 42 48 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 50% 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. 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. 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 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 $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. Aline 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. 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 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 (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. 1. Subtotals for EBIT and EBT should be included. . il List all expenses separately (do not combine). ii. 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 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. 9 Check figure: Total assets should be $470,981. : . Other information: Format: For numbers, use the Accounting format with zero decimal places. If your ending balance sheet is out by $1 or $2, do not worry about it, it's just rounding error. Marks will be awarded for a neat, well laid out and formatted spreadsheet. It should be easy to follow and have a good flow to it. , . Schedules should flow down a worksheet, not side by side. So, the opening balance sheet would be at the top, the cash receipts schedule below it. . Lines should be included to show where calculations occur, with double lines 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. Formulas: Many of the numbers in the schedules, most of the numbers in the income statement, and all of the numbers in the retained earnings statement and the closing balance sheet should be the result of formulas or called cells. For the pro-forma balance sheet, some of the formulas will start with a reference to the opening balance sheet. For example, taxes payable will be the opening amount from September 30, plus total tax expense, minus total tax instalments. 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, 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 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 Long term loan payable Total liabilities Common shares Retained earnings Total shareholders equity Total liabilities and equity $34,000 0 13,500 47,500 96,000 143,500 150,000 119,581 269,581 $413,081 Property, plant and equipment Accumulated depreciation Net property, plant and equipment Total assets 280,101 35,140 244,961 $413,081 2. FM sells 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 30 25 32 40 45 42 48 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 50% 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. 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. 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 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 $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. Aline 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. 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 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 (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. 1. Subtotals for EBIT and EBT should be included. . il List all expenses separately (do not combine). ii. 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 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. 9 Check figure: Total assets should be $470,981. : . Other information: Format: For numbers, use the Accounting format with zero decimal places. If your ending balance sheet is out by $1 or $2, do not worry about it, it's just rounding error. Marks will be awarded for a neat, well laid out and formatted spreadsheet. It should be easy to follow and have a good flow to it. , . Schedules should flow down a worksheet, not side by side. So, the opening balance sheet would be at the top, the cash receipts schedule below it. . Lines should be included to show where calculations occur, with double lines 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. Formulas: Many of the numbers in the schedules, most of the numbers in the income statement, and all of the numbers in the retained earnings statement and the closing balance sheet should be the result of formulas or called cells. For the pro-forma balance sheet, some of the formulas will start with a reference to the opening balance sheet. For example, taxes payable will be the opening amount from September 30, plus total tax expense, minus total tax instalments. 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