Here is what I need help with:

ACC 2610 - Budget Project NEW2017 Revised:10-17-2017 (beginning cash correction) Based on the information below, you are to prepare all operations budgets, financial budgets and schedules for a bicycle manufacturer An MS Excel template has been provided to help you in the preparation. Note: When using calculations in the template you should transfer values of rounded whole numbers, i.e. not just adjusting the number of digits to the right of the decimal. Doing calculations in sheets referencing cells in other sheets instead of transferring rounded whole numbers will cause schedules/budgets to not reconcile. Scenario Wing Bicycle Company manufactures and sells 2 sizes of bicycles, 21.5 inch and 22.5 inch. Sales Their sales team has committed to the following projected monthly unit sales for the next year, 21.5 Bike 22.5 Bike Jan Feb Mar Apr MayJun Jul AugSep Oct Nov Dec 368 472 633 616 443 345 322 317 317 317 328 420 320 410 550 535 385 300 280 275 275 275 285 365 The selling price for the 21 5inch bike is $455. For the 22.5 inch bike it is $490. The desired ending inventory for each month is 15% of the following month's sales for the 21.5inch bike and 12% for the 22.5 inch bike. The inventory at January 1 of the budget year is expected to be 55 units of the 21 5inch bike and 38 units of the 22.5 inch bike. The projected sales for Jan of the year following the budget are 385 of the 21.5inch bike and 350 for the 22.5 inch bike. Materials The total materials for the 21.5inch bike and 22.5 inch bike are $164.45 and $186.30 respectively (Bill of Materials in workbook for information purposes) The production department would like to have an ending raw materials inventory of 20% of the next month's production requirements. Desired ending production inventory for December is $15,700 for 21.5inch bike and S15,300 for 22.5 inch bike. Beginning raw materials inventory value for Jan of the budget year are $16,370 for the 21 5 inch bike and S18,597 for the 22.5 inch bike. Direct Labor Direct labor is 3.9 hours at $15.25 per hour for the 21inch bike and 4.4 hours at $15.25 per hour for the 22.5 inch bike (Process sheet in workbook for information purposes) Manufacturing Overhead Manufacturing Overhead is based on direct labor hours at a rate of $22.25 per DLH for each of the bikes. Budgeted monthly fixed overhead is as follows: Salaries Utilities Depreciation Maintenance nsurance Property Taxes S 5,000 2,200 7,200 2,800 2,000 1,200 Sales, General & Administrative Sales, General and Administrative (SG&A) expenses are based on unit sales for variable expenses at $11.20 per unit. Fixed SG&A expenses are $18,000 per month. Part of these SG&A expenses are non- cash and amount to 57,800 per month. Cash Cash is expected to be collected at 55% of the prior month sales and 45% of the sales from 2 months prior. Nov/Dec sales for the prior year are 422,000 and $475,000 respectively. Monthly cash disbursements for materials are 50% of current month's purchases and 50% of last month's purchases. Trade accounts payable should be based on materials purchases and the balance at Dec 31 of the year prior to the budget year is estimated to be 542,000 Assume all other expenses are paid in the month incurred. The beginning cash balance for Jan of the budget year is estimated to be $ 39,979 The company has estimated balance sheet amounts as of Jan 1 of $225,000 for Land, S1,280,000 for Property, Plant & Equipment, $2,125,000 for Common Stock and Retained Earnings of S510,915 Wing Bicycle Company Budgeted Balance Sheet @ 12/31 3 Assets: 4 Cash 5Accounts Receivable 6Raw Materials 7 Finished Goods 8 Land 225,000 1,280,000 1,505,000 ty, Plant & Equipment (Net) 0 Total Assets 2 3 Liabilities & Stockholders Equity: 4 Accounts Payable 5 Common Stock $21,250,000 6 Retained Earnings 7 Total Liabilities & Stockholders Equity 8 9 $21,250,000 Wing Bicycle Company Statement of Retained Earnings @ 12/31/20XX Retained Earnings 01/01/20xx Less: Dividends Add: Net Income Retained Earnings 12/31/20xx$ 0 ACC 2610 - Budget Project NEW2017 Revised:10-17-2017 (beginning cash correction) Based on the information below, you are to prepare all operations budgets, financial budgets and schedules for a bicycle manufacturer An MS Excel template has been provided to help you in the preparation. Note: When using calculations in the template you should transfer values of rounded whole numbers, i.e. not just adjusting the number of digits to the right of the decimal. Doing calculations in sheets referencing cells in other sheets instead of transferring rounded whole numbers will cause schedules/budgets to not reconcile. Scenario Wing Bicycle Company manufactures and sells 2 sizes of bicycles, 21.5 inch and 22.5 inch. Sales Their sales team has committed to the following projected monthly unit sales for the next year, 21.5 Bike 22.5 Bike Jan Feb Mar Apr MayJun Jul AugSep Oct Nov Dec 368 472 633 616 443 345 322 317 317 317 328 420 320 410 550 535 385 300 280 275 275 275 285 365 The selling price for the 21 5inch bike is $455. For the 22.5 inch bike it is $490. The desired ending inventory for each month is 15% of the following month's sales for the 21.5inch bike and 12% for the 22.5 inch bike. The inventory at January 1 of the budget year is expected to be 55 units of the 21 5inch bike and 38 units of the 22.5 inch bike. The projected sales for Jan of the year following the budget are 385 of the 21.5inch bike and 350 for the 22.5 inch bike. Materials The total materials for the 21.5inch bike and 22.5 inch bike are $164.45 and $186.30 respectively (Bill of Materials in workbook for information purposes) The production department would like to have an ending raw materials inventory of 20% of the next month's production requirements. Desired ending production inventory for December is $15,700 for 21.5inch bike and S15,300 for 22.5 inch bike. Beginning raw materials inventory value for Jan of the budget year are $16,370 for the 21 5 inch bike and S18,597 for the 22.5 inch bike. Direct Labor Direct labor is 3.9 hours at $15.25 per hour for the 21inch bike and 4.4 hours at $15.25 per hour for the 22.5 inch bike (Process sheet in workbook for information purposes) Manufacturing Overhead Manufacturing Overhead is based on direct labor hours at a rate of $22.25 per DLH for each of the bikes. Budgeted monthly fixed overhead is as follows: Salaries Utilities Depreciation Maintenance nsurance Property Taxes S 5,000 2,200 7,200 2,800 2,000 1,200 Sales, General & Administrative Sales, General and Administrative (SG&A) expenses are based on unit sales for variable expenses at $11.20 per unit. Fixed SG&A expenses are $18,000 per month. Part of these SG&A expenses are non- cash and amount to 57,800 per month. Cash Cash is expected to be collected at 55% of the prior month sales and 45% of the sales from 2 months prior. Nov/Dec sales for the prior year are 422,000 and $475,000 respectively. Monthly cash disbursements for materials are 50% of current month's purchases and 50% of last month's purchases. Trade accounts payable should be based on materials purchases and the balance at Dec 31 of the year prior to the budget year is estimated to be 542,000 Assume all other expenses are paid in the month incurred. The beginning cash balance for Jan of the budget year is estimated to be $ 39,979 The company has estimated balance sheet amounts as of Jan 1 of $225,000 for Land, S1,280,000 for Property, Plant & Equipment, $2,125,000 for Common Stock and Retained Earnings of S510,915 Wing Bicycle Company Budgeted Balance Sheet @ 12/31 3 Assets: 4 Cash 5Accounts Receivable 6Raw Materials 7 Finished Goods 8 Land 225,000 1,280,000 1,505,000 ty, Plant & Equipment (Net) 0 Total Assets 2 3 Liabilities & Stockholders Equity: 4 Accounts Payable 5 Common Stock $21,250,000 6 Retained Earnings 7 Total Liabilities & Stockholders Equity 8 9 $21,250,000 Wing Bicycle Company Statement of Retained Earnings @ 12/31/20XX Retained Earnings 01/01/20xx Less: Dividends Add: Net Income Retained Earnings 12/31/20xx$ 0