This is a Master Budget plan, with the check figures. I am struggling to get to those numbers and was wondering if I could get some help.

ACC 2610 - Budget Project R1 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. Their sales team has committed to the following projected monthly unit sales for the next year. 21.5 Bike 22.5 Bike Jan 368 320 Feb 472 410 Mar 633 550 Apr 616 535 May 443 385 Jun 345 300 Jul 322 280 Aug 317 275 Sep 317 275 Oct 317 275 Nov 328 285 Dec 420 365 The selling price for the 21.5 inch 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.5 inch 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.5 inch 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.5 inch bike and 350 for the 22.5 inch bike. The total materials for the 21.5 inch 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.5 inch bike and $15,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 $18,597 for the 22.5 inch bike. Direct labor is 3.9 hours at $15.25 per hour for the 21.5 inch 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 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 $ 5,000 2,200 7,200 Maintenance Insurance Property Taxes 2,800 2,000 1,200 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 noncash and amount to $7,800 per month. 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 $42,000. Assume all other accruals are paid in the month incurred. The beginning cash balance for Jan of the budget year is estimated to be $75,000. The company has estimated balance sheet amounts as of Jan 1 of $225,000 for Land, $1,280,000 for Property, Plant & Equipment, $2,125,000 for Common Stock and Retained Earnings of $510,915. 8-22-16 additional information. QtyReqd Item 1 Seat Post 1 Saddle 1 Seat Tube 1 Down Tube 1 Top Tube 2 Grips 1 Gear Shifters 1 Handlebar Stem 2 Headset 1 Head Tube 2 Front Brake 1 Forks 1 Quick Release 1 Front Wheel 1 Bottom Bracket 2 Pedal 2 Cranks 1 Chainings 1 Chain 1 Rear Mechanical 1 Rear Wheel 1 Sprocket Cluster 2 Rear Brake Total Cost UnitOfMeasure Each Each Each Each Each Each Each Each Each Each Each Each Each Each Each Each Each Each Each Each Each Each Each Cost 2.5 7.85 3.3 4.55 4.35 1.85 5.8 2.1 4.75 3.65 4.45 9.75 5.15 12.85 1.95 4.85 3.95 8.45 7.05 7.25 19.55 9.75 4.45 Extended Cost 2.5 7.85 3.3 4.55 4.35 3.7 5.8 2.1 9.5 3.65 8.9 9.75 5.15 12.85 1.95 9.7 7.9 8.45 7.05 7.25 19.55 9.75 8.9 $ 164.45 QtyReqd Item 1 Seat Post 1 Saddle 1 Seat Tube 1 Down Tube 1 Top Tube 2 Grips 1 Gear Shifters 1 Handlebar Stem 2 Headset 1 Head Tube 2 Front Brake 1 Forks 1 Quick Release 1 Front Wheel 1 Bottom Bracket 2 Pedal 2 Cranks 1 Chainings 1 Chain 1 Rear Mechanical 1 Rear Wheel 1 Sprocket Cluster 2 Rear Brake Total Cost UnitOfMe asure Each Each Each Each Each Each Each Each Each Each Each Each Each Each Each Each Each Each Each Each Each Each Each Cost 2.5 7.85 4.15 5.95 5.75 1.85 5.8 2.1 6.75 3.65 4.45 9.75 5.15 18.85 1.95 4.85 3.95 8.45 10.05 7.25 21.75 12.75 4.45 Extended Cost 2.5 7.85 4.15 5.95 5.75 3.7 5.8 2.1 13.5 3.65 8.9 9.75 5.15 18.85 1.95 9.7 7.9 8.45 10.05 7.25 21.75 12.75 8.9 $ 186.30 Sequence WorkArea Description 10 Cut Cut Tubing 20 Deburr Deburr Cut Tubing 30 Close Flaten tube ends 40 Weld Weld Tubing for Frame 50 Assembly Assembly 60 Inspect Inspection 70 Package Package Bike Total Time per Unit Direct Variabel Time Per Labor OH Rate Pc (Hrs) Rate (Hr) (DLHR) 0.5 0.25 0.75 0.75 1.25 0.15 0.25 3.9 $ 15.25 $ 22.25 Sequence WorkArea Description 10 Cut Cut Tubing 20 Deburr Deburr Cut Tubing 30 Close Flaten tube ends 40 Weld Weld Tubing for Frame 50 Assembly Assembly 60 Inspect Inspection 70 Package Package Bike Total Time per Unit Direct Variabel Time Per Labor OH Rate Pc (Hrs) Rate (Hr) (DLHR) 0.65 0.25 0.85 0.85 1.4 0.15 0.25 4.4 $ 15.25 $ 22.25 Jan 21.5 Bike 22.5 Bike Total Units Total $ Feb Mar 0 $ - Apr 0 $ - May 0 $ - Jun 0 $ - Jul 0 $ - Aug 0 $ - Sep 0 $ - Oct 0 $ - Nov 0 $ - Dec 0 $ - Total 0 $ - Price 0 $ 0 $ 0 0 $ - $ 688 882 1183 1151 828 645 602 592 592 592 613 785 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 $ 0 $ $ - 9153 - Jan 455 490 0 Jan 21.5 Bike Budgeted Sales 22.5 Bike Budgeted Sales 21.5 Bike Desired Ending Inventory 22.5 Bike Desired Ending Inventory 21.5 Bike Total Inventory Required 22.5 Bike Total Inventory Required Less: 21.5 Bike Beginning Inventory Less: 22.5 Bike Beginning Inventory 21.5 Bike Units to Produce 22.5 Bike Units to Produce Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Jan Salaries Utilities Depreciation Maintenance Insurance Property Taxes Total Fixed Overhead $ Feb - $ Mar - $ Apr - $ May - $ Jun - $ Jul - $ Aug - $ Sep - $ Oct - $ Nov - $ Dec - $ Total - $ $ $ $ $ $ $ - Jan Budgeted Sales Variable SG&A rate Variable SG&A Expense Fixed SG&A Expense Total SG&A Expenses Less: Non-Cash Expenses Cash SG&A Expenses Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - Jan 21.5 Bike (% next month sales) 22.5 Bike (% next month sales) 21.5 Bike Raw Materials (% next months production) 22.5 Bike Raw Materials (% next months production) Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Beginning (units) Beginning ($$) Jan 21.5 Bike Production 22.5 Bike Production 21.5 Bike Materials Per Unit $ 22.5 Bike Materials Per Unit $ 21.5 Bike Materials Needed for Production 22.5 Bike Materials Needed for Production Add: 21.5 Bike Material Desired Ending Inventory Add: 22.5 Bike Material Desired Ending Inventory Total 21.5 Bike Materials Needed Total 22.5 Bike Materials Needed Less: 21.5 Bike Materials Beginning Inventory Less: 22.5 Bike Materials Beginning Inventory 21.5 Materials Materials to Purchase 22.5 Materials Materials to Purchase Total Materials to Purchase Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total $ $ - $ $ - $ $ - $ $ - $ $ - $ $ - $ $ - $ $ - $ $ - $ $ - $ $ - $ $ - $ $ - $ $ - $ $ - $ $ - $ $ - $ $ - $ $ - $ $ - $ $ - $ $ - $ $ - $ $ - $ $ $ - $ $ $ - $ $ $ - $ $ $ - $ $ $ - $ $ $ - $ $ $ - $ $ $ - $ $ $ - $ $ $ - $ $ $ - $ $ $ - $ $ $ - Jan 21.5 Bike (units) 22.5 Bike (units) 21.5 Bike Direct Labor/Unit 22.5 Bike Direct Labor/Unit Labor Hours Required (21.5) Labor Hours Required (22.5) Total Labor Hours Required Direct Labor Rate Direct Labor Costs Feb $ - Mar $ - Apr $ - May $ - Jun $ - Jul $ - Aug $ - Sep $ - Oct $ - Nov $ - Dec $ - Total $ - $ - Jan Direct Labor Hours Required Variable Mfg Overhead Rate Variable Mfg Overhead Costs Fixed Mfg Overhead Costs Total Mfg Overhead Costs Less: Non-Cash Costs Cash Disbursements For Mfg OH Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ - $ Total Mfg OH per DLH $ $ - 21.5 Bike OH per unit - 22.5 Bike OH per unit Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Beginning Cash Collected 100% in Jan Dec % of Prior Month 1 Sales % of Prior Month 2 Sales Sales Cash Collected 1 Month After Sale 2 Months After Sale Total Cash Collected Accounts Receivable $ $ $ $ - $ $ $ $ - $ $ $ $ - $ $ $ $ - $ $ $ $ - $ $ $ $ - $ $ $ $ - $ $ $ $ - $ $ $ $ - $ $ $ $ - $ $ $ $ - $ $ $ $ - Accounts Payable 12/31 50 % Month 1 - 50% Month 2 Jan Purchases Feb Purchases Mar Purchases Apr Purchases May Purchases Jun Purchases Jul Purchases Aug Purchases Sep Purchases Oct Purchases Nov Purchases Dec Purchases Total Disbursements Total Materials to Purchase Jan $ - Feb $ $ Mar Apr May Jun Jul Aug Sep Oct $ $ $ $ $ $ $ - $ Dec $ $ Nov - $ - $ - $ - $ - $ - $ - $ - $ - - $ - $ - $ $ - Total $ - $ $ $ $ $ $ $ $ $ $ $ $ $ - $ - Accounts Payable 12/31/xx Jan Beginning Balance Add: Cash Collections Total Cash Available $ Feb - $ Mar - $ Apr - $ May - $ Jun - $ Jul - $ Aug - $ Sep - $ Oct - $ Nov - $ Dec - $ Total - $ - - $ $ $ $ $ $ - Less: Cash Disbursements Materials Direct Labor Manufacturing Overhead SG&A Total Disbursements Ending Cash Balance $ $ - $ $ - $ $ - $ $ - $ $ - $ $ - $ $ - $ $ - $ $ - $ $ - $ $ - $ $ Wing Bicycle Company Budgeted Profit (Loss) Statement for Year Ended 12/31 Sales Cost of Goods Sold Gross Margin S, G & A Expenses Operating Income Taxes @ 30% Net Income $ - $ $ $ - Wing Bicycle Company Budgeted Balance Sheet @ 12/31 Assets: Cash Accounts Receivable Raw Materials Finished Goods Land Property, Plant & Equipment (Net) Total Assets $ - Liabilities & Stockholders Equity: Accounts Payable Common Stock Retained Earnings Total Liabilities & Stockholders Equity $ - Sales: Price Per Unit 21.5 Bike 22.5 Bike Total Sales Costs: Budgeted Annual Unit Sales Extended Sales $ $ 0 $ - Budgeted Annual Unit Per Unit Cost Sales Extended Costs 21.5 Bike $ 22.5 Bike $ Total Cost of Goods Sold $ - Materials 21.5 Bike 22.5 Bike Direct Labor Overhead Ending Ending Total Per Inventory Inventory Unit Cost Units Value $ $ $ $ $ - 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 $ - Master Budget Project Unit Sales Budget Total should be $4,313,540 Production Budget 21.5 Bike Units Produce - 4,901 22.5 Bike Units Produce - 4,259 Fixed Overhead Budget Total Fixed Overhead Budget - $244,800 SG & A Budget Total - $224,914 Direct Materials Budget Total Materials to Purchase - $1,595,454 Direct Labor Budget Direct Labor Cost - $577,266 Manufacturing Overhead Budget Cash Disbursements for MOH - $1,000,640 Cash Collections Accounts Receivable - $499,951 Cash Disbursements Budget Total Disbursements - $1,567,605 Cash Budget Ending Cash Balance - $1,148,044 Profit/Loss Statement Net income - $513,664 Balance Sheet Total Assets - $3,219,428 Total Liabilities & Stockholders' Equity - $3,219,428 COGS Budget Total cost of goods sold - $3,261,221 Total Ending Inventory Value - $35,433 Statement of Retained Earnings Retained earnings at end of period - $1,024,579