Fill in the Orange spaces using information found in the balance sheet. The empty cells with orange fields are where the data is to be entered. Formulas are required in ALL cells (no hard-coded numbers). Part of this assignment is assessing your ability to use Excel.

OPTIMA, INC. Additional information: Ending finished goods inventory (percentage of the next quarter's sales) Ending raw materials inventory (percentage of the next quarter's production Expected number of frames sold in following quarters: Optima Company Sales Budget for the year ending December 31, 2020 Budgeted Sales (Units) Unit Price Total Sales \begin{tabular}{rrrrl} \multicolumn{1}{c|}{ Qtr. 1 } & \multicolumn{1}{c}{ Qtr. 2 } & \multicolumn{1}{c}{ Qtr. 3 } & Qtr. 4 & Total \\ \hline 65,000.00 & 70,000.00 & 75,000.00 & 90,000.00 & 300,000.00 \\ \hline 33.50 & 33.50 & 33.50 & 35.50 & \\ \hline$2,177,500.00 & $2,345,000.00 & $2,512,500.00 & $7,035,000.00 & $14,070,000.00 \\ \hline \end{tabular} Optima Company Production Budget for the year ending December 31, 2020 \begin{tabular}{l|rrrrrr} & \multicolumn{1}{c}{ Qtr. 1 } & \multicolumn{1}{c}{ Qtr. 2 } & \multicolumn{1}{c}{ Qtr. 3 } & \multicolumn{1}{r}{ Qtr. 4 } & \multicolumn{1}{c}{ Total } \\ \cline { 2 - 6 } & 65,000 & 70,000 & 75,000 & 90,000 & 300,000 \\ Budgeted Sales (Units) & 28,000 & 30,000 & 36,000 & 36,000 & 130,000 \\ \hline Desired Ending Inventory (Units) & (26,000) & (28,000) & (30,000) & (36,000) & (120,000) \\ \hline Less: Beginning Inventory (Units) & 67,000 & 72,000 & 81,000 & 90,000 & 310,000 \\ \hline Budgeted Production (Units) & & & & \end{tabular} Optima Company Direct Materials Purchases Budget for the year ending December 31, 2020 Budgeted Production (Units) \begin{tabular}{rrrrr} \multicolumn{1}{c}{ Qtr. 1 } & Qtr. 2 & Qtr. 3 & \multicolumn{1}{c}{ Qtr. 4 } & \multicolumn{1}{l}{ Total } \\ \hline 67,000 & 72,000 & 81,000 & 90,000 & 310,000 \\ \hline 3 & 3 & 3 & 3 & \\ \hline 201,000 & 216,000 & 243,000 & 270,000 & 930,000 \\ 64,800 & 72,900 & 81,000 & 81,000 & 299,700 \\ (60,300) & (64,800) & (72,900) & (81,000) & (279,000) \\ \hline 205,500 & 224,100 & 251,100 & 270,000 & 950,700 \\ 2.00 & 2.00 & 2.00 & 2.00 & \\ \hline$411,000.00 & $448,200.00 & $502,200.00 & $540,000.00 & $1,901,400.00 \\ \hline \end{tabular} Optima Company Direct Labor Budget for the year ending December 31, 2020 \begin{tabular}{l|rrrrr|r} & Qtr. 1 & Qtr. 2 & Qtr. 3 & Qtr. 4 & Total \\ \cline { 2 - 6 } & 78 & 72 & 80 & 80 & 310 \\ Budgeted Production (Units) & 5.00 & 5.00 & 5.00 & 5.00 & \\ \hline Hours per unit & 390.00 & 360.00 & 400.00 & 400.00 & 1,550.00 \\ Hours needed & 10.00 & 10.00 & 10.00 & 10.00 & \\ \hline Cost per hour & $3,900.00 & $3,600.00 & $4,000.00 & $4,000.00 & $0.00 \\ \hline Total Cost & & & \end{tabular} Optima Company Overhead Budget for the year ending December 31, 2020 Budgeted hours Variable Rate Budgeted VOH Budgeted FOH Total OH \begin{tabular}{r|rrrr} \multicolumn{1}{c}{ Qtr. 1 } & Qtr. 2 & \multicolumn{1}{c}{ Qtr. 3 } & \multicolumn{1}{c}{ Qtr. 4 } & \multicolumn{1}{c}{ Total } \\ \hline 32,500 & 35,000 & 37,500 & 45,000 & 150,000 \\ 8.00 & 8.00 & 8.00 & 8.00 & \\ \hline 260,000.00 & 280,000.00 & 300,000.00 & 360,000.00 & 1,200,000.00 \\ 275,000.00 & 275,000.00 & 275,000.00 & 275,000.00 & 1,100,000.00 \\ \hline$535,000.00 & $555,000.00 & $575,000.00 & $635,000.00 & $2,300,000.00 \\ \hline \end{tabular} Optima Company Selling and Administrative Expenses Budget for the year ending December 31, 2020 Budgeted Sales (Units) Variable Rate Variable expenses Fixed expenses Total expenses \begin{tabular}{rrrrr} \multicolumn{1}{c}{ Qtr. 1 } & Qtr. 2 & Qtr. 3 & Qtr. 4 & \multicolumn{1}{c}{ Total } \\ \hline 65,000 & 70,000 & 75,000 & 90,000 & 300,000 \\ 6.00 & 6.00 & 6.00 & 6.00 & 6.00 \\ \hline 390,000.00 & 420,000.00 & 450,000.00 & 540,000.00 & 1,800,000.00 \\ 200,000.00 & 200,000.00 & 200,000.00 & 200,000.00 & 800,000.00 \\ \hline$590,000.00 & $620,000.00 & $650,000.00 & $740,000.00 & $2,600,000.00 \\ \hline \end{tabular} Optima Company Budgeted Cost Per Unit for the year ending December 31,2020 Budgeted Manufacturing Costs: Direct Materials Direct Labor Unit Cost Overhead: Variable Fixed Total Unit Costs Optima Company Cost of Goods Sold Budget for the year ending December 31,2020 Budgeted Sales (Units) Budgeted Manufacturing Costs per unit Budgeted Cost of Goods Sold Optima Company Cash Budget for the year ending December 31,2020 Budgeted Cash Receipts Budgeted Sales Revenue \begin{tabular}{ccccc} Qtr. 1 & Qtr. 2 & Qtr. 3 & Qtr. 4 & Total \\ \hline 2,177,500.00 & 2,345,000.00 & 2,512,500.00 & 7,035,000.00 & 14,070,000.00 \end{tabular} Cash Collections Credit Collections (Current Month) Credit Collections (Following Month) Budgeted Cash Receipts Budgeted Cash Disbursements Budgeted Raw Materials Purchases \begin{tabular}{cllll} Qtr. 1 & Qtr. 2 & Qtr. 3 & Qtr. 4 & Total \\ \hline 411,000.00 & 448,200.00 & 502,200.00 & 540,000.00 & 1,901,400.00 \end{tabular} 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, 2020 Sales Less: COGS Gross margin Less: S\&A Expenses Income before taxes Optima Company Retained Earnings Statement for the year ending December 31, 2020 Beginning Retained Earnings Add: Net Income(Loss) Deduct Dividends Ending Retained Earnings Optima Company Balance Sheet Decmeber 31, 2020 Assets Liabilities and Stockholders Equity Cash Accounts Receivable Accounts Payable Finished Goods Inventory Line of Credit Payable Direct Materials Inventory Capital Stock Plant, Property, and Equipment Retained Earnings Total Assets Total Liabilities \& Stockholders' E OPTIMA, INC. Additional information: Ending finished goods inventory (percentage of the next quarter's sales) Ending raw materials inventory (percentage of the next quarter's production Expected number of frames sold in following quarters: Optima Company Sales Budget for the year ending December 31, 2020 Budgeted Sales (Units) Unit Price Total Sales \begin{tabular}{rrrrl} \multicolumn{1}{c|}{ Qtr. 1 } & \multicolumn{1}{c}{ Qtr. 2 } & \multicolumn{1}{c}{ Qtr. 3 } & Qtr. 4 & Total \\ \hline 65,000.00 & 70,000.00 & 75,000.00 & 90,000.00 & 300,000.00 \\ \hline 33.50 & 33.50 & 33.50 & 35.50 & \\ \hline$2,177,500.00 & $2,345,000.00 & $2,512,500.00 & $7,035,000.00 & $14,070,000.00 \\ \hline \end{tabular} Optima Company Production Budget for the year ending December 31, 2020 \begin{tabular}{l|rrrrrr} & \multicolumn{1}{c}{ Qtr. 1 } & \multicolumn{1}{c}{ Qtr. 2 } & \multicolumn{1}{c}{ Qtr. 3 } & \multicolumn{1}{r}{ Qtr. 4 } & \multicolumn{1}{c}{ Total } \\ \cline { 2 - 6 } & 65,000 & 70,000 & 75,000 & 90,000 & 300,000 \\ Budgeted Sales (Units) & 28,000 & 30,000 & 36,000 & 36,000 & 130,000 \\ \hline Desired Ending Inventory (Units) & (26,000) & (28,000) & (30,000) & (36,000) & (120,000) \\ \hline Less: Beginning Inventory (Units) & 67,000 & 72,000 & 81,000 & 90,000 & 310,000 \\ \hline Budgeted Production (Units) & & & & \end{tabular} Optima Company Direct Materials Purchases Budget for the year ending December 31, 2020 Budgeted Production (Units) \begin{tabular}{rrrrr} \multicolumn{1}{c}{ Qtr. 1 } & Qtr. 2 & Qtr. 3 & \multicolumn{1}{c}{ Qtr. 4 } & \multicolumn{1}{l}{ Total } \\ \hline 67,000 & 72,000 & 81,000 & 90,000 & 310,000 \\ \hline 3 & 3 & 3 & 3 & \\ \hline 201,000 & 216,000 & 243,000 & 270,000 & 930,000 \\ 64,800 & 72,900 & 81,000 & 81,000 & 299,700 \\ (60,300) & (64,800) & (72,900) & (81,000) & (279,000) \\ \hline 205,500 & 224,100 & 251,100 & 270,000 & 950,700 \\ 2.00 & 2.00 & 2.00 & 2.00 & \\ \hline$411,000.00 & $448,200.00 & $502,200.00 & $540,000.00 & $1,901,400.00 \\ \hline \end{tabular} Optima Company Direct Labor Budget for the year ending December 31, 2020 \begin{tabular}{l|rrrrr|r} & Qtr. 1 & Qtr. 2 & Qtr. 3 & Qtr. 4 & Total \\ \cline { 2 - 6 } & 78 & 72 & 80 & 80 & 310 \\ Budgeted Production (Units) & 5.00 & 5.00 & 5.00 & 5.00 & \\ \hline Hours per unit & 390.00 & 360.00 & 400.00 & 400.00 & 1,550.00 \\ Hours needed & 10.00 & 10.00 & 10.00 & 10.00 & \\ \hline Cost per hour & $3,900.00 & $3,600.00 & $4,000.00 & $4,000.00 & $0.00 \\ \hline Total Cost & & & \end{tabular} Optima Company Overhead Budget for the year ending December 31, 2020 Budgeted hours Variable Rate Budgeted VOH Budgeted FOH Total OH \begin{tabular}{r|rrrr} \multicolumn{1}{c}{ Qtr. 1 } & Qtr. 2 & \multicolumn{1}{c}{ Qtr. 3 } & \multicolumn{1}{c}{ Qtr. 4 } & \multicolumn{1}{c}{ Total } \\ \hline 32,500 & 35,000 & 37,500 & 45,000 & 150,000 \\ 8.00 & 8.00 & 8.00 & 8.00 & \\ \hline 260,000.00 & 280,000.00 & 300,000.00 & 360,000.00 & 1,200,000.00 \\ 275,000.00 & 275,000.00 & 275,000.00 & 275,000.00 & 1,100,000.00 \\ \hline$535,000.00 & $555,000.00 & $575,000.00 & $635,000.00 & $2,300,000.00 \\ \hline \end{tabular} Optima Company Selling and Administrative Expenses Budget for the year ending December 31, 2020 Budgeted Sales (Units) Variable Rate Variable expenses Fixed expenses Total expenses \begin{tabular}{rrrrr} \multicolumn{1}{c}{ Qtr. 1 } & Qtr. 2 & Qtr. 3 & Qtr. 4 & \multicolumn{1}{c}{ Total } \\ \hline 65,000 & 70,000 & 75,000 & 90,000 & 300,000 \\ 6.00 & 6.00 & 6.00 & 6.00 & 6.00 \\ \hline 390,000.00 & 420,000.00 & 450,000.00 & 540,000.00 & 1,800,000.00 \\ 200,000.00 & 200,000.00 & 200,000.00 & 200,000.00 & 800,000.00 \\ \hline$590,000.00 & $620,000.00 & $650,000.00 & $740,000.00 & $2,600,000.00 \\ \hline \end{tabular} Optima Company Budgeted Cost Per Unit for the year ending December 31,2020 Budgeted Manufacturing Costs: Direct Materials Direct Labor Unit Cost Overhead: Variable Fixed Total Unit Costs Optima Company Cost of Goods Sold Budget for the year ending December 31,2020 Budgeted Sales (Units) Budgeted Manufacturing Costs per unit Budgeted Cost of Goods Sold Optima Company Cash Budget for the year ending December 31,2020 Budgeted Cash Receipts Budgeted Sales Revenue \begin{tabular}{ccccc} Qtr. 1 & Qtr. 2 & Qtr. 3 & Qtr. 4 & Total \\ \hline 2,177,500.00 & 2,345,000.00 & 2,512,500.00 & 7,035,000.00 & 14,070,000.00 \end{tabular} Cash Collections Credit Collections (Current Month) Credit Collections (Following Month) Budgeted Cash Receipts Budgeted Cash Disbursements Budgeted Raw Materials Purchases \begin{tabular}{cllll} Qtr. 1 & Qtr. 2 & Qtr. 3 & Qtr. 4 & Total \\ \hline 411,000.00 & 448,200.00 & 502,200.00 & 540,000.00 & 1,901,400.00 \end{tabular} 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, 2020 Sales Less: COGS Gross margin Less: S\&A Expenses Income before taxes Optima Company Retained Earnings Statement for the year ending December 31, 2020 Beginning Retained Earnings Add: Net Income(Loss) Deduct Dividends Ending Retained Earnings Optima Company Balance Sheet Decmeber 31, 2020 Assets Liabilities and Stockholders Equity Cash Accounts Receivable Accounts Payable Finished Goods Inventory Line of Credit Payable Direct Materials Inventory Capital Stock Plant, Property, and Equipment Retained Earnings Total Assets Total Liabilities \& Stockholders' E