Need help on Balance before financing, Minimum cash balance, and excess.
AutoSave OFF HES ACCT 285 - Spring 2021 - Excel Project Template - Saved to my Mac Home Insert Draw Page Layout Formulas Data Review Share Comments 8! Connections Properties View Tell me 8 Clear Sa Reapply Sort Filter Advanced o - + Show Detail From From New Database HTML Text Query Refresh All Stocks Geogra... A Consolidate Group Ungroup Subtotal - Hide Detail Edit Links Text to Columns Flash Fill Remove Data Duplicates Validation What If Analysis Analysis Tools B16 fx G H 1 1 K L M N o P Q R s 4th Qtr A B D E F 1 Cash Budget 2 2022 3 15Qtr 2nd Qtr 3rd Qtr TOTAL 4 Beginning cash balance S 68,000 S 132,659 $ 198,965 S 278.280S 68,000 5 Cash collections S 135,700$ 134,320$ 156,580 S 176,280 $ 602,880 6 Available cash balance $ 203,700$ 266,979 $ 355,545 $ 454,560 $ 670,880 7 Cash disbursements: 8 Direct material purchased in prior quarter S 6,300 $ 2,971 S 2,9555 3,593 $ 15,819 9 Direct material purchased in current quarter S 16.837 S 16,744 | 5 20,350 $ 22,6985 76,637 10 Direct labor S 12,845 S 12,2675 14,980 S 16,969 $ 57,062 11 Overhead S 12,233 S 12,182 S 12,422 S 12,597 S 49,435 12 Selling & administrative s 21,825 S 20,850 $ 24,750 S 27,675 S 95.100 13 Equipment purchases S 3,000 $ 1,800 $ 4,800 14 Dividends S 1,000 $ 1,000 15 Total cash disbursements $ 71,041 $ 68,015 $ 77,265 $ 83,532 $ 299,852 16 Balance before financing 17 Minimum cash balance 18 Excess (deficiency) 19 Ending cash balance $ 132,659 $ 198,965 $ 278,280 $ 371,028 $ 371,028 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 NAME INPUTS Beg. Balance Sheet Sales & Collections Production RM, DL, & OH S&A Cash Income Statement Balance Sheet Goal Seek + + 100% AutoSave OFF ACCT 285 - Spring 2021 - Excel Project Template - Saved to my Mac Home Insert Draw Page Layout Formulas Data Review Share Comments View Tell me 8 L! Connections Clear Eo 21-1- + Show Detail Properties Refresh All Stocks Geogra... Sort From From New Database HTML Text Query Filter A Reapply Advanced Consolidate AL Flash Fill Group Ungroup Subtotal - Hide Detail Text to Columns Edit Links Remove Data Duplicates Validation What If Analysis Analysis Tools B22 fx 5% B H 1 1 K L L M N 0 Q R S T U V W D E F G Individual Excel Spreadsheet - Budget Case Inputs Main Street Donuts 3 2022 (projected) 2023 (projected) 4 Sales: 1 * Qtr 2nd atr 3rd Qtr 4th otr 19 Qtr 2 Qtr 5 Boxes of donuts 9,500 9,000 11,000 12,500 10,200 9,800 6 Selling price per box $ 10.00 7 8 Boxes of little bites 5,000 6,400 7.000 7,600 5,200 6,600 9 Selling price per box S 7.00 10 11 Collections: 12 Quarter of sale 90% 13 Quarter after sale 10% 14 15 Finished Goods Inventory: 16 Ending FG inventory requirement 1% of next quarter's sales 17 Ending FG inventory units (December 31, 2021) 145 boxes 18 19 Raw Materials Inventory 20 Raw materials (dough) required per box 24 ounces 21 Raw materials cost per ounce $0.09 per ounce 22 Ending RM inventory requirement 5% lof next quarter's production needs 23 Ending RM inventory units (December 31, 2021) 17,411 ounces 24 Paid in quarter of purchase 85% 25 Paid in following quarter 15% 26 27 Direct labor: 28 Labor required per box 0.08 hours 29 Labor cost per hour $ 17.00 per hour 30 31 Manfacturing Overhead: 32 Variable S 1.50 per box 33 Fixed $ 12,000 per quarter 34 Noncash fixed included in above) $ 900 per quarter 35 36 Selling & Admin Expenses 37 Variable $ 1.95 per box 38 Fixed $ 4,000 per quarter 39 Noncash fixed (included in above) $ 700 per quarter 40 41 Cash: 42 Minimum cash balance $ 20,000 43 44 Other: 45 Kitchen equipment purchase $ 3,000 in June (2 Quarter) 46 Kitchen equipment purchase $ 1,800 in July (3 Quarter) 47 Dividends payment $ 1,000 Declared every December (Paid every January - 1st Quarter) 48 NAME INPUTS Beg. Balance Sheet Sales & Collections Production RM, DL, & OH S&A Cash Income Statement Balance Sheet Goal Seek + + 92% AutoSave OFF ACCT 285 - Spring 2021 - Excel Project Template - Saved to my Mac Home Insert Draw Page Layout Formulas Data Review Share Comments View Tell me AL 8 L! Connections o 21-1- + Show Detail Properties Clear Reapply Advanced Stocks Geogra... Sort Filter Refresh All From From New Database HTML Text Query Consolidate Flash Fill Text to Columns Group Ungroup Subtotal - Hide Detail a Edit Links Remove Data Duplicates Validation What If Analysis Analysis Tools B32 foc A B D E F H 1 J K L M N o Q R s T 6,300 Main Street Donuts Balance Sheet As of December 31, 2021 Liabilities and Equities S 68,000 Accounts payable S $ 18,700 Notes payable $ S 1.567 Interest payable $ S 728 Dividend payable S S 80,000 Common stock S Retained earnings $ 168,995 Total Liabilities and equities S 1,000 100,000 61,695 168.995 1 2 3 4 Assets 5 Cash 6 Accounts receivable 7 Raw materials inventory 8 Finished goods inventory 9 Property, plant and equipment, net 10 11 Total assets 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 NAME INPUTS Beg. Balance Sheet Sales & Collections Production RM, DL, & OH S&A Cash Income Statement Balance Sheet Goal Seek + + 100% AutoSave OFF HES ACCT 285 - Spring 2021 - Excel Project Template - Saved to my Mac Home Insert Draw Page Layout Formulas Data Review Share Comments 8! Connections Properties View Tell me 8 Clear Sa Reapply Sort Filter Advanced o - + Show Detail From From New Database HTML Text Query Refresh All Stocks Geogra... A Consolidate Group Ungroup Subtotal - Hide Detail Edit Links Text to Columns Flash Fill Remove Data Duplicates Validation What If Analysis Analysis Tools B16 fx G H 1 1 K L M N o P Q R s 4th Qtr A B D E F 1 Cash Budget 2 2022 3 15Qtr 2nd Qtr 3rd Qtr TOTAL 4 Beginning cash balance S 68,000 S 132,659 $ 198,965 S 278.280S 68,000 5 Cash collections S 135,700$ 134,320$ 156,580 S 176,280 $ 602,880 6 Available cash balance $ 203,700$ 266,979 $ 355,545 $ 454,560 $ 670,880 7 Cash disbursements: 8 Direct material purchased in prior quarter S 6,300 $ 2,971 S 2,9555 3,593 $ 15,819 9 Direct material purchased in current quarter S 16.837 S 16,744 | 5 20,350 $ 22,6985 76,637 10 Direct labor S 12,845 S 12,2675 14,980 S 16,969 $ 57,062 11 Overhead S 12,233 S 12,182 S 12,422 S 12,597 S 49,435 12 Selling & administrative s 21,825 S 20,850 $ 24,750 S 27,675 S 95.100 13 Equipment purchases S 3,000 $ 1,800 $ 4,800 14 Dividends S 1,000 $ 1,000 15 Total cash disbursements $ 71,041 $ 68,015 $ 77,265 $ 83,532 $ 299,852 16 Balance before financing 17 Minimum cash balance 18 Excess (deficiency) 19 Ending cash balance $ 132,659 $ 198,965 $ 278,280 $ 371,028 $ 371,028 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 NAME INPUTS Beg. Balance Sheet Sales & Collections Production RM, DL, & OH S&A Cash Income Statement Balance Sheet Goal Seek + + 100% AutoSave OFF ACCT 285 - Spring 2021 - Excel Project Template - Saved to my Mac Home Insert Draw Page Layout Formulas Data Review Share Comments View Tell me 8 L! Connections Clear Eo 21-1- + Show Detail Properties Refresh All Stocks Geogra... Sort From From New Database HTML Text Query Filter A Reapply Advanced Consolidate AL Flash Fill Group Ungroup Subtotal - Hide Detail Text to Columns Edit Links Remove Data Duplicates Validation What If Analysis Analysis Tools B22 fx 5% B H 1 1 K L L M N 0 Q R S T U V W D E F G Individual Excel Spreadsheet - Budget Case Inputs Main Street Donuts 3 2022 (projected) 2023 (projected) 4 Sales: 1 * Qtr 2nd atr 3rd Qtr 4th otr 19 Qtr 2 Qtr 5 Boxes of donuts 9,500 9,000 11,000 12,500 10,200 9,800 6 Selling price per box $ 10.00 7 8 Boxes of little bites 5,000 6,400 7.000 7,600 5,200 6,600 9 Selling price per box S 7.00 10 11 Collections: 12 Quarter of sale 90% 13 Quarter after sale 10% 14 15 Finished Goods Inventory: 16 Ending FG inventory requirement 1% of next quarter's sales 17 Ending FG inventory units (December 31, 2021) 145 boxes 18 19 Raw Materials Inventory 20 Raw materials (dough) required per box 24 ounces 21 Raw materials cost per ounce $0.09 per ounce 22 Ending RM inventory requirement 5% lof next quarter's production needs 23 Ending RM inventory units (December 31, 2021) 17,411 ounces 24 Paid in quarter of purchase 85% 25 Paid in following quarter 15% 26 27 Direct labor: 28 Labor required per box 0.08 hours 29 Labor cost per hour $ 17.00 per hour 30 31 Manfacturing Overhead: 32 Variable S 1.50 per box 33 Fixed $ 12,000 per quarter 34 Noncash fixed included in above) $ 900 per quarter 35 36 Selling & Admin Expenses 37 Variable $ 1.95 per box 38 Fixed $ 4,000 per quarter 39 Noncash fixed (included in above) $ 700 per quarter 40 41 Cash: 42 Minimum cash balance $ 20,000 43 44 Other: 45 Kitchen equipment purchase $ 3,000 in June (2 Quarter) 46 Kitchen equipment purchase $ 1,800 in July (3 Quarter) 47 Dividends payment $ 1,000 Declared every December (Paid every January - 1st Quarter) 48 NAME INPUTS Beg. Balance Sheet Sales & Collections Production RM, DL, & OH S&A Cash Income Statement Balance Sheet Goal Seek + + 92% AutoSave OFF ACCT 285 - Spring 2021 - Excel Project Template - Saved to my Mac Home Insert Draw Page Layout Formulas Data Review Share Comments View Tell me AL 8 L! Connections o 21-1- + Show Detail Properties Clear Reapply Advanced Stocks Geogra... Sort Filter Refresh All From From New Database HTML Text Query Consolidate Flash Fill Text to Columns Group Ungroup Subtotal - Hide Detail a Edit Links Remove Data Duplicates Validation What If Analysis Analysis Tools B32 foc A B D E F H 1 J K L M N o Q R s T 6,300 Main Street Donuts Balance Sheet As of December 31, 2021 Liabilities and Equities S 68,000 Accounts payable S $ 18,700 Notes payable $ S 1.567 Interest payable $ S 728 Dividend payable S S 80,000 Common stock S Retained earnings $ 168,995 Total Liabilities and equities S 1,000 100,000 61,695 168.995 1 2 3 4 Assets 5 Cash 6 Accounts receivable 7 Raw materials inventory 8 Finished goods inventory 9 Property, plant and equipment, net 10 11 Total assets 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 NAME INPUTS Beg. Balance Sheet Sales & Collections Production RM, DL, & OH S&A Cash Income Statement Balance Sheet Goal Seek + + 100%
