Need help with Managerial Accounting comprehensive problem on Budgeting.

ACCT 2303; Jay Thibodeaux, Associate Professor Points Possible: 40 Method of submission: to be completed using Excel. If you use an opensource product, it may change some of your inpu Due date: See Blackboard Instructions 1 The student is required to complete the budge the "Budget" tab of this file. 2 The cells with orange fill are where the data is cells (no hard-coded numbers). Part of this as The Data for these formulas is in the "Given" t t, it may change some of your input, and I won't be able to grade it. t is required to complete the budget assignment using the template provided on t" tab of this file. th orange fill are where the data is to be entered. Formulas are required in ALL rd-coded numbers). Part of this assignment is assessing your ability to use Excel. or these formulas is in the "Given" tab of this file. OPTIMA, INC. General information: Selling price of bamboo rocking horses Linear feet of bamboo required per rocking horse Cost of bamboo per foot Hours required to build Average hourly direct labor rate 33.50 3 2.11 0.50 15.00 Additional information: Ending finished goods inventory (percentage of the next quarter's sales) Ending raw materials inventory (percentage of the next quarter's production) Variable manufacturing overhead rate per direct labor hour Estimated quarterly fixed manufacturing overhead Estimated monthly selling and administrative expenses: Variable Fixed 40% 30% Quarters/Year 8.00 275,000.00 4 6.00 200,000.00 Expected number of frames sold in following quarters: 4th Quarter 2013 55,000 1st Quarter 2014 65,000 2nd Quarter 2014 70,000 3rd Quarter 2014 75,000 4th Quarter 2014 90,000 1st Quarter 2015 90,000 2nd Quarter 2015 75,000 Additional information: Cash on hand at April 1 Percentage of sales in cash Percentage of credit sales collected during quarter of sale Percentage of credit sales collected in quarter following sale Percentage of direct material purchases paid in quarter purchased Percentage of direct material purchases paid in following quarter 250,000.00 80% 50% 50% 80% 20% Depreciation included in quarterly fixed manufacturing overhead Depreciation included in quarterly fixed selling and administartive expenses Amount expected to be paid for a piece of equipment (purchased in 4th Quarter) Minimum cash balance required Borrowing increments Quarterly dividend payments 75,000.00 50,000.00 750,000.00 40,000.00 1.00 250,000.00 Regarding Inventory: Work-in-process has a zero beginning and ending balance. Raw Materials Inventory dollar amounts need to be rolled forward to get the ending balance: Produced - Sold = Ending) Raw Materials Inventory dollar amounts need to be rolled forward to get the ending balance: Purchased - Used = Ending) Optima Company Balance Sheet 12/31/2013 (Beginning + (Beginning + Assets Cash Finished goods invenotry $ Direct materials inventory Accounts receivable Plant, Property, and equipment, net Total assets Liabilities and Stockholders' Equity Accounts Payable Capital Stock Retained earnings Total liabilities and stockholders equity 250,000.00 650,000.00 120,000.00 225,000.00 3,500,000.00 4,745,000.00 $ 90,000.00 3,000,000.00 1,655,000.00 4,745,000.00 (Beginning + (Beginning + Name: Optima Company Sales Budget for the year ending December 31, 2014 Qtr. 1 Qtr. 2 Qtr. 3 Budgeted Sales (Units) Unit Price Total Sales Optima Company Production Budget for the year ending December 31, 2014 Qtr. 1 Qtr. 2 Qtr. 3 Budgeted Sales (Units) Desired Ending Inventory (Units) Less: Beginning Inventory (Units) Budgeted Production (Units) Optima Company Direct Materials Purchases Budget for the year ending December 31, 2014 Qtr. 1 Qtr. 2 Qtr. 3 Budgeted Production (Units) Materials per unit Production needs Desired ending Inventory Less: Beginning Inventory Purchases Cost per unit Purchase cost Optima Company Direct Labor Budget for the year ending December 31, 2014 Qtr. 1 Qtr. 2 Qtr. 3 Budgeted Production (Units) Hours per unit Hours needed Cost per hour Total Cost Optima Company Overhead Budget for the year ending December 31, 2014 Qtr. 1 Qtr. 2 Qtr. 3 Budgeted hours Variable Rate Budgeted VOH Budgeted FOH Total OH Optima Company Selling and Administrative Expenses Budget for the year ending December 31, 2014 Qtr. 1 Qtr. 2 Qtr. 3 Budgeted Sales (Units) Variable Rate Variable expenses Fixed expenses Total expenses Optima Company Budgeted Cost Per Unit for the year ending December 31, 2014 Budgeted Manufacturing Costs: Direct Materials Direct Labor Overhead: Variable Fixed Total Unit Costs Optima Company Cost of Goods Sold Budget for the year ending December 31, 2014 Qtr. 1 Qtr. 2 Qtr. 3 Budgeted Sales (Units) Budgeted Manufacturing Costs per unit Budgeted Cost of Goods Sold Optima Company Cash Budget for the year ending December 31, 2014 Budgeted Cash Receipts Budgeted Sales Revenue Cash Collections Credit Collections (Current Month) Credit Collections (Following Month) Budgeted Cash Receipts Budgeted Cash Disbursements Budgeted Raw Materials Purchases Raw Materials Payments (Current Month) Raw Materials Payments (Following Month) Direct Labor Manufacturing Overhead less: Depreciation Selling and Administrative Expenses less: Depreciation Equipment Purchases Dividend Payments Budgeted Annual Cash Flow Beginning Cash Balance Plus: Cash Receipts Less: Cash Payments Preliminary Cash Balance Cash Borrowed / Repaid Ending Cash Balance Optima Company Income Statement for the year ending December 31, 2014 Sales Less: COGS Gross margin Less: S&A Expenses Income before taxes Optima Company Retained Earnings Statement for the year ending December 31, 2014 Beginning Retained Earnings Add: Net Income(Loss) Deduct Dividends Ending Retained Earnings Optima Company Balance Sheet Decmeber 31, 2014 Assets Cash Accounts Receivable Finished Goods Inventory Direct Materials Inventory Plant, Property, and Equipment Total Assets Liabilities and Stockho Accounts Payable Line of Credit Payable Capital Stock Retained Earnings Total Liabilities & Stockholders' ny ber 31, 2014 Qtr. 4 Total Qtr. 4 Total Qtr. 4 Total Qtr. 4 Total ny et ber 31, 2014 ny es Budget ber 31, 2014 ny get ber 31, 2014 ny et ber 31, 2014 Qtr. 4 Total Qtr. 4 Total ny penses Budget ber 31, 2014 ny Unit ber 31, 2014 Unit Cost ny Budget ber 31, 2014 ny Qtr. 4 Total ber 31, 2014 ny nt ber 31, 2014 ny tement ber 31, 2014 ny 14 Liabilities and Stockholders Equity nts Payable f Credit Payable ned Earnings Liabilities & Stockholders' Equ