As noted in the Instructions tab of the workbook. You should only enter formulas; not hard-coded numbers. Points will be deducted for hard-coded numbers.

Here are some check figures:

Total Direct Materials Purchases for the Year: $1,994,583.00. Total Budgeted Cash Disbursements for the Year: $10,487,921.80. Total Assets at 12/31/2014: $5,107,567.07.

Rounding function for cell F122: =round(-F121)+Given!G43

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 Assets Cash Finished goods invenotry $ 250,000.00 650,000.00 (Beginning + (Beginning + 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 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 65000 400 70000 400 75000 400 26,000,000 28,000,000 30,000,000 Optima Company Production Budget for the year ending December 31, 2014 Qtr. 1 Qtr. 2 Qtr. 3 Budgeted Sales (Units) 65,000 70,000 75,000 Desired Ending Inventory (Units) Less: Beginning Inventory (Units) Budgeted Production (Units) 13,000 15,000 20,000 0 13,000 15,000 78,000 72,000 80,000 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 78,000 3 234,000 72,000 3 216,000 80,000 3 240,000 Desired ending Inventory Less: Beginning Inventory Purchases Cost per unit Purchase cost 64,800 65,700 233,100 72,000 64,800 223,200 72,000 72,000 240,000 80 80 80 18,648,000 17,856,000 19,200,000 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 78,000 5 390,000 10 72,000 5 360,000 10 80,000 5 400,000 10 3,900,000 3,600,000 4,000,000 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 390,000 6 2,340,000 1,000,000 3,340,000 360,000 6 2,160,000 1,000,000 3,160,000 400,000 6 2,400,000 1,000,000 3,400,000 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 390,000 6 2,340,000 250000 360,000 6 2,160,000 250000 400,000 6 2,400,000 250000 2,590,000 2,410,000 2,650,000 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 90000 400 300000 1,600 36,000,000 120,000,000 ny et ber 31, 2014 Qtr. 4 Total 90,000 300,000 10,000 10,000 20,000 0 80,000 310,000 Qtr. 4 Total 80,000 3 240,000 310,000 3 930,000 65,700 72,000 233,700 65,700 65,700 930,000 80 80 ny es Budget ber 31, 2014 18,696,000 74,400,000 ny get ber 31, 2014 ny et Qtr. 4 Total 80,000 5 400,000 10 310,000 5 1,550,000 10 4,000,000 15,500,000 ber 31, 2014 Qtr. 4 400,000 6 2,400,000 1,000,000 3,400,000 Total 1,550,000 6 9,300,000 4,000,000 13,300,000 ny penses Budget ber 31, 2014 Qtr. 4 Total 400,000 6 2,400,000 250000 1,550,000 6 9,300,000 1,000,000 2,650,000 8,650,000 ny Unit ber 31, 2014 Unit Cost ny Budget ber 31, 2014 ny ber 31, 2014 Qtr. 4 Total 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