PLEASE HELP! I need help with balancing my Pro Forma Balance sheet on excel sheet (Balance Sheet), "Awsome, LLC Pro Forma Balance Sheet at December 31, 2021" 8th picture. The other pictures are what you need to figure it out. The last 2 pictures are the assignment. The rest is what i have done.
These photos are a closer look at the spread sheet just incase some were blurry or not
Awesome, LLC Projected Balance Sheet at December 31, 2020 Assets Current Assets: Cash Accounts Receivable Inventory Liabilities & Stockholder's Equity Current Liabilities: 20,000.00 Accounts Payable 110,000.00 150,000.00 Total Liabilities $ $ 80,000.00 $ 80,000.00 Long Term Assets: Building & Equiptment, net Stockholder's Equity 200,000.00 Common Stock Retained Earnings 300,000.00 100,000.00 Total Stockholder's Equity 400,000.00 Total Assets 480,000.00 Total Liabilities & Stockholder's Equity $ 480,000.00 3 Beginning Balance Sheet Budgeting Assumptions Purchase Budget Cash Receipts Budget Awesome, LLE Ruting Assumptions for the year ending December 11, 2021 wand Beginning Balance Sheet Budgeting Assumption Purchase Budget Cash Receipts Budget Awesome, LLC Purchase Budget For the year ending December 31, 2021 Month Me Semely $ 200.000.00 $ 220,000.00 SIA, 3230.000 DO 500 $3,000.00 $175.000,00 1.000,00 51.00.00 201,00 3200,000.00 $ 200.000 200 23000 S.M.000.00 $13,000.00 $100,000.00 $126.000.000.000,00 BOLO DO 5105.000.00 $100,000.00 $11,000.00 $ 12,000.00 $ 125,000.00 $12.000 $112.000 60 SACO BE LOODOWE SOTO 0056 DI SOFAS DE $ COVERS AS OS RES. COMO SE S MESOS DOS 0000S 0S 0S CORTS 5.88.00.00 $11.400.00 $134,200.00 $. 120 000 SO.700.00 $0.00 $0.00 $100.00 $168.100.400.00 $ 131 400.00 $ 15000 TELS Cash Reciepts Budget For the year ending December 31, 2021 Month - January bruary - March Mey A September October November December $ 200,000.00 $ 220,000.00 10,000.00 $ 210,000.00 $ 200,000.00 $100,000.00 $175,000.00 $ 170,000.00 15.000 $200,000.00 $ 210,000.00 5 220.000 of The Year tuled Sales Dollar Schedule of pested Cush Collections Beginning Account Receivable 10 Seles 11 February Sales 12 March Sales Art Sales 14 May Sales ser 15 My Sales MS 10 September Sales 19 October Ses 20 November Ses December Sales 22 Bad Oet: Total Cash Collection Accounts Beceivable Ending Balance $ 110,000.00 $BLOOD.00 116,000.00 $ 35,000.00 $ 127,600.00 72.000.00 $ 101.400.00 84.000,00 5 121,000.00 00 31000.00 5 72.000.00 $ 104,400.00 JUDO, DO $ 101,500.00 58,000.00 5.00 5.24.000.00 $ 101,100.00 $ 10,000.00 $ 116,000.00 S 84,000,00 5 121,000.00 $ 18,000.00 $106.13.3 $ 204,000.00 $ 199,900.00 $ 188,400.00 $ 20.000.00 $ 184,000.00 $ 17,900.00 $ 100.00.00 S 172.600.00 $ 187,300.00 $ 200,000.00 $ 200,000.00 $933.000.00 Beginning Salance Sheet Budgeting Assumption Purchase Budget Cash Receipts Budget C Awesome, LLC. Cash Disbursement Budget For the year ending December 31, 2021 Month January februar Sear Duo De Verona BLOOD $110,400 DO 5134.200.00 $120.400 005 10.200.00 5105,100.00 $ 102,00.00 $110,100.00 $119.100.00 $125,400.00 $131,400.00 $137,400.00 $1,14,200.00 Months of the Year Pemred m tayaler A6 10 February 1 Man 12 ore Mer 5.00 5.26.0.0 S 49,160.00 13.120.00 $ 77,280.00 37,200.00 M.940,00 $ 10.00 $ 14,420.00 12.760,00 S 7,448.00 $ 3.500.00 $ 73.710.00 S 0.00 72.410.00 $ 13,050.00 S 72.470.00 $ 3.730.00 $0.00 S. 37.620.00 $ 87,760.00 5.420.00 1.500.00 3.41,220,00 My 16 1 Deter 19 30 December 22 Total Car Diamet $100.640.00 95.260.00 5394340.00 $123320.00 $117,180.00 $10,080.00 $14.400.00 S04660.00 $112.800.00 $120,990.00 $127.200,00 91,300.00 1.226.000.00 596.150.00 Hudgeting Assumptions Purchase Budget Cash Recepts Budget Cash Disbursement Budget Castle for the year ending December 11 201 AC 2000 SERRES FUERTES, SI 264.000 100.000 $20 OOOD STAR 0000011440000 5 16.00 177.000 DO 10.000 200.000,00 200.000,00 520 530023333333333333333333333333 SED SOOJUSES, LES 3.3 4011330 La Chiaramente SC Opera Total Cash Doment read Credit Rets Mercumded Tel Financing Cow |tarterCallets S ELS TETETTELEILLS BESOCRES DE IDENTITEZE SITEEREITOS BEBES ELETINES . ut Gecepts Sort Cathed Cath Budget ht 12 Align Font 2 ipboard 5 B C A Budgeted Income Statement Awesome, LLC For the year ended December 31, 2021 $ 2,350,000.00 Sales: Cost of Good Sold: Gross Profit: $ (1,410,000.00) $ 940,000.00 Operating Expenses: Selling Expenses: Depreciation Expense: Other Operating Expenses: Total Operating Expenses: $ $ $ (236,000.00) (24,000.00) (480,000.00) (740,000.00) 5 Total Operating Income: $ 200,000.00 7 3. Interest Expense: Bad Debt Expense: $ $ (46,266.67) $ 153,733.33 1 Net Income: 2 3 otions Purchase Budget Cash Receipts Budget Ca Ready EO 1 Awesome, LLC. Pro Forma Balance Sheet at December 31, 2021 Liabilities & Stockholder's Equity Liabilities: | 254,933.33 Accounts Payable: 132,000.00 124,200.00 Total Liabilities: 96,180.00 $ $ 96,180.00 2 3 4 5 Assets 6 Current Assets: 7 Cash: 8 Account's Receivable: 9 Inventory: 10 11 Long-Term Assets: 12 Building & Equipment: 13 14 15 16 17 Total Assets: 18 19 20 Cash Disbursement Budget Stockholder's Equity: 176,000.00 Common Stock: Retained Earnings: 300,000.00 249,733.33 Total Stockholder's Equity: 549,733.33 687,133.33 Total Liabilities & Stockholder's Equity: $ 645,913,33 Cash Budget Income Statement Balance Sheet 8 Statement of Sto Awesome, LLC. Statement of Stockholder's Equity at December 31, 2021 Common Stock 300000 0 5 Beginning Balance: Add: Issuance of Common Stock: 3 Add: Net Income: 9 Less: Dividends Paid 0 Ending Balance Retained Earnings Total Stockholder's Equity 100000 400000 400000 153733 553733 -4000 549733 249733 549733 300000 12 13 14 15 16 17 18 19 ho Cash Budget Income Statement Balance Sheet Statement of Stockholder's Equi Awesome, LLC. is a wholesale electrical distributor. The following projections have been made for the 2021 budget: Sales Forecast January, 2021 February March April May June July August September October November December $200,000 $220,000 $180,000 $210,000 $200,000 $180,000 $175,000 $170,000 $185,000 $200,000 $210,000 $220,000 Cost Data: Average Purchase Price of Product: 60% of selling price Commission to Sales People: 10% of sales Other Operating Expenses: $42,000 per month, including $2,000 depreciation, not including interest expense (if any.) January, 2022 $230,000 Awesome, LLC Projected Balance Sheet at December 31, 2020 Assets Equities Cash $ 20,000 Accounts Payable Accounts Receivable 110,000 (for merchandise) $ 80,000 Inventory 150,000 Common Stock 300,000 Building & Equipment, net 200,000 Retained Earnings 100,000 Total $480.000 Total $480.000 Other Information: a. Awesome maintains inventory at 90% of the coming month's sales requirements. b. Sales are collected 40% in the month of sale, 58% in the following month. c. Bad debts are written off immediately, if not collected in the month following the sale. d. Purchases are paid 30% in the month of purchase, 70% in the following month. e. All other expenses requiring cash are paid in the month incurred. f. The board of directors plans to declare a $4,000 dividend on May 10, 2021, payable June 2, 2021. g. The company wishes to maintain a minimum cash balance of $15,000. A line of credit has been negotiated, to be used only when necessary, at an interest rate of 12%. h. No capital purchases are planned. Required: In Excel, prepare a monthly budget for January to December, 2021, with totals for the year, to include: 1. A budgeted income statement 2. A purchase budget 3. A cash receipts budget 4. A cash disbursements budget 5. A cash budget and 6. A pro forma balance sheet as of December 31, 2021. Notes & Hints... 1. If you feel that some critical piece of information is missing, make and highlight an appropriate assumption. If you wish, you can check with me about the need for and validity of a particular assumption. 2. I recommend constructing the statements for one month and then copying the formulas over to the remaining months. I will look at the copy of your spreadsheet on the disk to see what formulas you have used to develop your model. 3. Make sure you provide instructions for other users, if the loan calculation isn't automatic. 4. Ignore taxes. B 1 2 3 4 All 12 Months $ $ $ 0.40 0.58 0.60 0.02 $ $ $ $ 0.10 40,000.00 2,000.00 5 6 Sales Budget: 7 Budgetes Sales in Dollars ($) 8 Percentage of Sales Collected in Same Month as Sale 9 Percentage of Sales Collected in Following Month as Sale 10 11 Expenses: 12 Cost of Good Sold: 13 Bad Debt Expense: 14 Sales Commision Expense: 15 Operating Expense: 16 Depreciation Expense: 17 18 Purchases: 19 Percentage of Purchase Paid in Sames Month as Purchase: 20 Percentage of Purchase Paid in Following Month as Purchase: 21 Accounts Payable Beginning Balance: 22 23 Dividend: 24 Declared: 25 Disbursed: 26 27 Cash Budget: 28 Minimum Cash Balance 29 Simple Interest Rate Per Month: $ $ 0.30 0.70 15,000.00 0.01 $ Budgeting Assumptions Purchase Budget Ready 0 Awesome, LLC Budgeting Assumptions For the year ending December 31, 2021 Months Lamary February March April Mary uly Aut September October November December January 2023 $ 200,000.00 $ 220,000.00 $ 190,00000 $ 210,000.00 $ 200,000.00 $ 180,000,00 $ 175,000.00 $ 12,000.00 $ 15,000.00 $ 200,000.00 $ 210,000.00 $ 220,000.00 $ 200,000.00 20 -10 10 DO 30 10 $10,000.00 4,000.00 $ 4.000.00 =NS X 29 C B A 1 2 3 4 January February $ 200,000.00 $ 220,000 $ 120,000.00 $ 132,000 $ 118,800.00 $ 97,200 $ 238,800.00 $ 229,200 $6150 DD BOSS 5 5 Budgeted Sales in Dollars 7 8 Budgeted Cost of Goods Sold: 9 Add: Desired Ending Inventory: 10 Total Needs: 11 12 Less: Beginning Balance: 13 14 Required Purchases: 15 16 17 18 19 20 Budgeting Assumptions Ready $ 88,800.00 $ 110,40 Purchase Budget Cash Receipts Bud B D E F G Awesome, LLC. Purchase Budget For the year ending December 31 Months January February March April June July $ 200,000.00 $ 220,000.00 $180,000.00 $ 210,000.00 $ 200,000.00 $180,000.00 $175,0 May Y $ 120,000.00 $ 132,000.00 $ 108,000.00 $ 126,000.00 $ 120,000.00 $ 108,000.00 $105,0 $ 118,800.00 $ 97,200.00 $113,400.00 $ 108,000.00 $ 97,200.00 $ 94,500.00 $ 91,8 $ 238,800.00 $ 229,200.00 $221,400.00 $ 234,000.00 $ 217,200.00 $ 202,500.00 $196,8 CADEAU $ 88,800.00 $ 110,400.00 $124,200.00 $ 120,600.00 $ 109,200.00 $ 105,300.00 $ 102,3 se Budget Cash Receipts Budget Cash Disbursement Budget + IVI Awesome, LLC. Purchase Budget e year ending December 31, 2021 Months May June July August September October November December January 201 $ 200,000.00 $180,000.00 $175,000.00 $170,000.00 $185,000.00 $ 200,000.00 $ 210,000.00 $ 220,000.00 $230,000.00 $ 120,000.00 $ 108,000.00 $ 105,000.00 $102,000.00 $111,000.00 $ 120,000.00 $ 126,000.00 $ 132,000.00 $ 138,000.00 $ 97,200.00 $ 94,500.00 $ 91,800.00 $ 99,900.00 $ 108,000.00 $ 113,400.00 $ 118,800.00 $ 124,200.00 $ 217,200.00 $ 202,500.00 $196,800.00 $ 201,900.00 $ 219,000.00 $ 233,400.00 $ 244,800.00 $ 256,200.00 $ 109,200.00 $105,300.00 $102,300.00 $110,100.00 $ 119,100.00 $ 125,400.00 $ 131,400.00 $ 137,400.00 A B D E Cash Reciepts For the year ending Dec January February March April Ju May $ 200,000.00 $ 220,000.00 $ 180,000.00 $ 210,000.00 $ 200,000.00 $ $ 110,000.00 $ 80,000.00 $ 116,000.00 $ 88,000.00 $ 127,600.00 $ 72,000.00 $ 104,400.00 $ 84,000.00 $ 121,800.00 $ 80,000.00 $ Month's Of The Year Budgeted Sales in Dollars Schedule of Expected Cash Collections: Beginning Account Receivable: January Sales: February Sales: March Sales: April Sales: May Sales: June Sales: July Sales: August Sales: September Sales: October Sales: November Sales: December Sales: Bad Debt: Total Cash Collections: Accounts Receivable Ending Balance: (DOC) 14,400.00) SGOL S00.00 $ 186,333.33 $ 204,000.00 $ 199,600.00 $ 188,400.00 $ 201,800.00 $ 132,000.00 Budgeting Assumptions Purchase Budget Cash Receipts Budget Cash Disbursement Bu H Cash Reciepts Budget For the year ending December 31, 2021 Month April May June July August September October November - December 000.00 $ 210,000.00 $ 200,000.00 $ 180,000.00 $ 175,000.00 $ 170,000.00 $ 185,000.00 $ 200,000.00 $ 210,000.00 $ 220,000.00 600.00 000.00 $ 104,400.00 $ 84,000.00 121,800.00 $ 80,000.00 $ 116,000.00 $ 72,000.00 $ 104,400.00 $ 70,000.00 $ 101,500,00 $ 68,000.00 $ 98,600.00 $ 74,000.00 $ 107,300.00 $ 80,000.00 $ 116,000.00 $ 84,000.00 $ 121,800.00 $ 88,000.00 R00100) 00.00 $ 188,400.00 $ 201,800.00 $ 188,000.00 $ 174,400.00 $ 169,500.00 $ 172,600.00 $ 187,300.00 $ 200,000.00 $ 209,800.00 Budget Cash Disbursement Budget Display Settings F El Cash D For the year Month's Of The Year Purchases Required: January February March April $ 88,800.00 $ 110,400.00 $ 124,200.00 $ 120,600.00 $ $ 80,000.00 $ 26,640.00 $ 62,160.00 $ 33,120.00 $ 77,280.00 $ 37,260.00 $ 86,940.00 $ 36,180.00 Beginning Accounts Payable: January: February March: April: May: June: July: August: September: October: November: December: Total Cash Disbursement: Account Payable Ending Balance: $ 106,640.00 $ 95,280.00 $ 114,540.00 $123,120.00 $ 96,180.00 Purchase Budget Budgeting Assumptions Cash Receipts Budget Cash D Awesome, LLC. Cash Disbursement Budget For the year ending December 31, 2021 Month April May July August September October Novem! 20 $ 120,600.00 $ 109,200.00 $ 105,300.00 $ 102,300.00 $ 110,100.00 $ 119,100.00 $ 125,400.00 $ 131,400 June DO DO $ 86,940.00 $ 36,180.00 $ 84,420.00 $ 32,760.00 $ 76,440.00 $ 31,590.00 $ 73,710.00 $ 30,690.00 $ 71,610.00 $ 33,030.00 $ 77,070.00 $ 35,730.00 $ 83,370.00 $ 37,620.00 $ 87,780.C $ 39,420.0 -00 $ 123,120.00 $ 117,180.00 $ 108,030.00 $ 104,400.00 $ 104,640.00 $ 112,800.00 $ 120,990.00 $127,200.0 udget Cash Disbursement Budget . 2021 July August September October November December Year Total 102,300.00 $ 110,100.00 $ 119,100.00 $125,400.00 $ 131,400.00 $137,400.00 $1,384,200.00 73,710.00 30,690.00 $ 71,610.00 $ 33,030.00 $ 77,070.00 $ 35,730.00 $ 83,370.00 $ 37,620.00 $ 87,780.00 $ 39,420.00 $ 91,980.00 $ 41,220.00 5104,400.00 $ 104,640.00 $ 112,800.00 $ 120,990.00 $127,200.00 $ 91,980.00 $1,326,800.00 January 20,000.00 $ $ Particulars Beginning Balance: Add: Cash Receipts: Cash Receipts from Customers: $ 186,333.33 Total Cash Available: $ 206,333.33 Less: Cash Disbursements: D0,000.00 Inventory Purchases: Sales Commisions: Operating Expenses: Dividends: Total Cash Disbursement: (66,640,00 Total Operating Cash Flows: $ 39,693.33 Financing Line of Credit Used: Repayments: Interest Accumulated: $ $ $ Total Financing Cash Flows: $ Ending Cash Balance: Purchase Budget eady EO $ 39,693.33 Cash Receipts Budget Awesome, LLC. Cash Budget For the year ending December, M March April May Jun 86,413.33 $ 113,473.33 $ 117,753.33 $ 142, February 39,693.33 $ $ $ 204,000.00 $ 199,600.00 $ 188,400.00 $ 201,800.00 $ 188, $ 243,693.33 $ 286,013.33 $ 301,873.33 $319,553.33 $ 330, 118,000100YS (21,000,00) S (20,000.00 $ DIGDOGO S (157,280.00) S (172,510,00) S (184, 120.00) S (17, 180:00) S 170,0 $ 86,413.33 $ 113,473.33 $ 117,753.33 $ 142,373.33 $ 160,3- - $ - $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ - - $ $ $ $ $ $ $ 86,413.33 $ 113,473.33 $ 117,753.33 $ 142,373.33 $ 160,34 Cash Disbursement Budget Cash Budget Income H 1 K C. mber, 31 2021 Month Schedule June July August September October $ 142,373.33 $ 160,343.33 $ 172,843.33 $ 180,703.33 $ 182,003.33 $ $ 188,000.00 $ 174,400.00 $ 169,500.00 $ 172,600.00 $ 187,300.00 $ $ 330,373.33 $ 334,743.33 $ 342,343.33 $353,303.33 $ 369,303.33 $ 27,500.00 0,000.00 TARDE 160,420.00 3 S 160,343.33 172,843.33 S 180,703.33 182,003.33 $ 188,313.33 $ $ $ $ $ $ $ $ $ 33 172,843.33 $ 180,703.33 182,003.33 188,313.33 2 u 160,343.33 S Income Sta ... K L M N September October November December Year Totals $ 180,703.33 $ 182,003.33 $ 188,313.33 $ 200,113.33 $ 1,604,026.67 $ 172,600.00 $ 187,300.00 $ 200,000.00 $ 209,800.00 $ 2,281,733.33 $ 353,303.33 $ 369,303.33 $ 388,313.33 $ 409,913.33 $ 3,885,760.00 PODOU ROD ALES 120,000 ALLED 000 200 000 CASH AMOUNT SIDO) 191.000,00 180,090,00 183.200.00 80.0 12,046 $ 182,063.33 $ 188,313.33 $200,113,33 S 254.933.33 $ 1,838,960.00 $ $ $ $ $ $ $ $ $ $ $ $ $ $ !! $ $ $ $ $ 182,003.33 $ 188,313.33 $ 200,113.33 $ 254,933.33 $ 1,838,960 Zoom Awesome, LLC Projected Balance Sheet at December 31, 2020 Assets Current Assets: Cash Accounts Receivable Inventory Liabilities & Stockholder's Equity Current Liabilities: 20,000.00 Accounts Payable 110,000.00 150,000.00 Total Liabilities $ $ 80,000.00 $ 80,000.00 Long Term Assets: Building & Equiptment, net Stockholder's Equity 200,000.00 Common Stock Retained Earnings 300,000.00 100,000.00 Total Stockholder's Equity 400,000.00 Total Assets 480,000.00 Total Liabilities & Stockholder's Equity $ 480,000.00 3 Beginning Balance Sheet Budgeting Assumptions Purchase Budget Cash Receipts Budget Awesome, LLE Ruting Assumptions for the year ending December 11, 2021 wand Beginning Balance Sheet Budgeting Assumption Purchase Budget Cash Receipts Budget Awesome, LLC Purchase Budget For the year ending December 31, 2021 Month Me Semely $ 200.000.00 $ 220,000.00 SIA, 3230.000 DO 500 $3,000.00 $175.000,00 1.000,00 51.00.00 201,00 3200,000.00 $ 200.000 200 23000 S.M.000.00 $13,000.00 $100,000.00 $126.000.000.000,00 BOLO DO 5105.000.00 $100,000.00 $11,000.00 $ 12,000.00 $ 125,000.00 $12.000 $112.000 60 SACO BE LOODOWE SOTO 0056 DI SOFAS DE $ COVERS AS OS RES. COMO SE S MESOS DOS 0000S 0S 0S CORTS 5.88.00.00 $11.400.00 $134,200.00 $. 120 000 SO.700.00 $0.00 $0.00 $100.00 $168.100.400.00 $ 131 400.00 $ 15000 TELS Cash Reciepts Budget For the year ending December 31, 2021 Month - January bruary - March Mey A September October November December $ 200,000.00 $ 220,000.00 10,000.00 $ 210,000.00 $ 200,000.00 $100,000.00 $175,000.00 $ 170,000.00 15.000 $200,000.00 $ 210,000.00 5 220.000 of The Year tuled Sales Dollar Schedule of pested Cush Collections Beginning Account Receivable 10 Seles 11 February Sales 12 March Sales Art Sales 14 May Sales ser 15 My Sales MS 10 September Sales 19 October Ses 20 November Ses December Sales 22 Bad Oet: Total Cash Collection Accounts Beceivable Ending Balance $ 110,000.00 $BLOOD.00 116,000.00 $ 35,000.00 $ 127,600.00 72.000.00 $ 101.400.00 84.000,00 5 121,000.00 00 31000.00 5 72.000.00 $ 104,400.00 JUDO, DO $ 101,500.00 58,000.00 5.00 5.24.000.00 $ 101,100.00 $ 10,000.00 $ 116,000.00 S 84,000,00 5 121,000.00 $ 18,000.00 $106.13.3 $ 204,000.00 $ 199,900.00 $ 188,400.00 $ 20.000.00 $ 184,000.00 $ 17,900.00 $ 100.00.00 S 172.600.00 $ 187,300.00 $ 200,000.00 $ 200,000.00 $933.000.00 Beginning Salance Sheet Budgeting Assumption Purchase Budget Cash Receipts Budget C Awesome, LLC. Cash Disbursement Budget For the year ending December 31, 2021 Month January februar Sear Duo De Verona BLOOD $110,400 DO 5134.200.00 $120.400 005 10.200.00 5105,100.00 $ 102,00.00 $110,100.00 $119.100.00 $125,400.00 $131,400.00 $137,400.00 $1,14,200.00 Months of the Year Pemred m tayaler A6 10 February 1 Man 12 ore Mer 5.00 5.26.0.0 S 49,160.00 13.120.00 $ 77,280.00 37,200.00 M.940,00 $ 10.00 $ 14,420.00 12.760,00 S 7,448.00 $ 3.500.00 $ 73.710.00 S 0.00 72.410.00 $ 13,050.00 S 72.470.00 $ 3.730.00 $0.00 S. 37.620.00 $ 87,760.00 5.420.00 1.500.00 3.41,220,00 My 16 1 Deter 19 30 December 22 Total Car Diamet $100.640.00 95.260.00 5394340.00 $123320.00 $117,180.00 $10,080.00 $14.400.00 S04660.00 $112.800.00 $120,990.00 $127.200,00 91,300.00 1.226.000.00 596.150.00 Hudgeting Assumptions Purchase Budget Cash Recepts Budget Cash Disbursement Budget Castle for the year ending December 11 201 AC 2000 SERRES FUERTES, SI 264.000 100.000 $20 OOOD STAR 0000011440000 5 16.00 177.000 DO 10.000 200.000,00 200.000,00 520 530023333333333333333333333333 SED SOOJUSES, LES 3.3 4011330 La Chiaramente SC Opera Total Cash Doment read Credit Rets Mercumded Tel Financing Cow |tarterCallets S ELS TETETTELEILLS BESOCRES DE IDENTITEZE SITEEREITOS BEBES ELETINES . ut Gecepts Sort Cathed Cath Budget ht 12 Align Font 2 ipboard 5 B C A Budgeted Income Statement Awesome, LLC For the year ended December 31, 2021 $ 2,350,000.00 Sales: Cost of Good Sold: Gross Profit: $ (1,410,000.00) $ 940,000.00 Operating Expenses: Selling Expenses: Depreciation Expense: Other Operating Expenses: Total Operating Expenses: $ $ $ (236,000.00) (24,000.00) (480,000.00) (740,000.00) 5 Total Operating Income: $ 200,000.00 7 3. Interest Expense: Bad Debt Expense: $ $ (46,266.67) $ 153,733.33 1 Net Income: 2 3 otions Purchase Budget Cash Receipts Budget Ca Ready EO 1 Awesome, LLC. Pro Forma Balance Sheet at December 31, 2021 Liabilities & Stockholder's Equity Liabilities: | 254,933.33 Accounts Payable: 132,000.00 124,200.00 Total Liabilities: 96,180.00 $ $ 96,180.00 2 3 4 5 Assets 6 Current Assets: 7 Cash: 8 Account's Receivable: 9 Inventory: 10 11 Long-Term Assets: 12 Building & Equipment: 13 14 15 16 17 Total Assets: 18 19 20 Cash Disbursement Budget Stockholder's Equity: 176,000.00 Common Stock: Retained Earnings: 300,000.00 249,733.33 Total Stockholder's Equity: 549,733.33 687,133.33 Total Liabilities & Stockholder's Equity: $ 645,913,33 Cash Budget Income Statement Balance Sheet 8 Statement of Sto Awesome, LLC. Statement of Stockholder's Equity at December 31, 2021 Common Stock 300000 0 5 Beginning Balance: Add: Issuance of Common Stock: 3 Add: Net Income: 9 Less: Dividends Paid 0 Ending Balance Retained Earnings Total Stockholder's Equity 100000 400000 400000 153733 553733 -4000 549733 249733 549733 300000 12 13 14 15 16 17 18 19 ho Cash Budget Income Statement Balance Sheet Statement of Stockholder's Equi Awesome, LLC. is a wholesale electrical distributor. The following projections have been made for the 2021 budget: Sales Forecast January, 2021 February March April May June July August September October November December $200,000 $220,000 $180,000 $210,000 $200,000 $180,000 $175,000 $170,000 $185,000 $200,000 $210,000 $220,000 Cost Data: Average Purchase Price of Product: 60% of selling price Commission to Sales People: 10% of sales Other Operating Expenses: $42,000 per month, including $2,000 depreciation, not including interest expense (if any.) January, 2022 $230,000 Awesome, LLC Projected Balance Sheet at December 31, 2020 Assets Equities Cash $ 20,000 Accounts Payable Accounts Receivable 110,000 (for merchandise) $ 80,000 Inventory 150,000 Common Stock 300,000 Building & Equipment, net 200,000 Retained Earnings 100,000 Total $480.000 Total $480.000 Other Information: a. Awesome maintains inventory at 90% of the coming month's sales requirements. b. Sales are collected 40% in the month of sale, 58% in the following month. c. Bad debts are written off immediately, if not collected in the month following the sale. d. Purchases are paid 30% in the month of purchase, 70% in the following month. e. All other expenses requiring cash are paid in the month incurred. f. The board of directors plans to declare a $4,000 dividend on May 10, 2021, payable June 2, 2021. g. The company wishes to maintain a minimum cash balance of $15,000. A line of credit has been negotiated, to be used only when necessary, at an interest rate of 12%. h. No capital purchases are planned. Required: In Excel, prepare a monthly budget for January to December, 2021, with totals for the year, to include: 1. A budgeted income statement 2. A purchase budget 3. A cash receipts budget 4. A cash disbursements budget 5. A cash budget and 6. A pro forma balance sheet as of December 31, 2021. Notes & Hints... 1. If you feel that some critical piece of information is missing, make and highlight an appropriate assumption. If you wish, you can check with me about the need for and validity of a particular assumption. 2. I recommend constructing the statements for one month and then copying the formulas over to the remaining months. I will look at the copy of your spreadsheet on the disk to see what formulas you have used to develop your model. 3. Make sure you provide instructions for other users, if the loan calculation isn't automatic. 4. Ignore taxes. B 1 2 3 4 All 12 Months $ $ $ 0.40 0.58 0.60 0.02 $ $ $ $ 0.10 40,000.00 2,000.00 5 6 Sales Budget: 7 Budgetes Sales in Dollars ($) 8 Percentage of Sales Collected in Same Month as Sale 9 Percentage of Sales Collected in Following Month as Sale 10 11 Expenses: 12 Cost of Good Sold: 13 Bad Debt Expense: 14 Sales Commision Expense: 15 Operating Expense: 16 Depreciation Expense: 17 18 Purchases: 19 Percentage of Purchase Paid in Sames Month as Purchase: 20 Percentage of Purchase Paid in Following Month as Purchase: 21 Accounts Payable Beginning Balance: 22 23 Dividend: 24 Declared: 25 Disbursed: 26 27 Cash Budget: 28 Minimum Cash Balance 29 Simple Interest Rate Per Month: $ $ 0.30 0.70 15,000.00 0.01 $ Budgeting Assumptions Purchase Budget Ready 0 Awesome, LLC Budgeting Assumptions For the year ending December 31, 2021 Months Lamary February March April Mary uly Aut September October November December January 2023 $ 200,000.00 $ 220,000.00 $ 190,00000 $ 210,000.00 $ 200,000.00 $ 180,000,00 $ 175,000.00 $ 12,000.00 $ 15,000.00 $ 200,000.00 $ 210,000.00 $ 220,000.00 $ 200,000.00 20 -10 10 DO 30 10 $10,000.00 4,000.00 $ 4.000.00 =NS X 29 C B A 1 2 3 4 January February $ 200,000.00 $ 220,000 $ 120,000.00 $ 132,000 $ 118,800.00 $ 97,200 $ 238,800.00 $ 229,200 $6150 DD BOSS 5 5 Budgeted Sales in Dollars 7 8 Budgeted Cost of Goods Sold: 9 Add: Desired Ending Inventory: 10 Total Needs: 11 12 Less: Beginning Balance: 13 14 Required Purchases: 15 16 17 18 19 20 Budgeting Assumptions Ready $ 88,800.00 $ 110,40 Purchase Budget Cash Receipts Bud B D E F G Awesome, LLC. Purchase Budget For the year ending December 31 Months January February March April June July $ 200,000.00 $ 220,000.00 $180,000.00 $ 210,000.00 $ 200,000.00 $180,000.00 $175,0 May Y $ 120,000.00 $ 132,000.00 $ 108,000.00 $ 126,000.00 $ 120,000.00 $ 108,000.00 $105,0 $ 118,800.00 $ 97,200.00 $113,400.00 $ 108,000.00 $ 97,200.00 $ 94,500.00 $ 91,8 $ 238,800.00 $ 229,200.00 $221,400.00 $ 234,000.00 $ 217,200.00 $ 202,500.00 $196,8 CADEAU $ 88,800.00 $ 110,400.00 $124,200.00 $ 120,600.00 $ 109,200.00 $ 105,300.00 $ 102,3 se Budget Cash Receipts Budget Cash Disbursement Budget + IVI Awesome, LLC. Purchase Budget e year ending December 31, 2021 Months May June July August September October November December January 201 $ 200,000.00 $180,000.00 $175,000.00 $170,000.00 $185,000.00 $ 200,000.00 $ 210,000.00 $ 220,000.00 $230,000.00 $ 120,000.00 $ 108,000.00 $ 105,000.00 $102,000.00 $111,000.00 $ 120,000.00 $ 126,000.00 $ 132,000.00 $ 138,000.00 $ 97,200.00 $ 94,500.00 $ 91,800.00 $ 99,900.00 $ 108,000.00 $ 113,400.00 $ 118,800.00 $ 124,200.00 $ 217,200.00 $ 202,500.00 $196,800.00 $ 201,900.00 $ 219,000.00 $ 233,400.00 $ 244,800.00 $ 256,200.00 $ 109,200.00 $105,300.00 $102,300.00 $110,100.00 $ 119,100.00 $ 125,400.00 $ 131,400.00 $ 137,400.00 A B D E Cash Reciepts For the year ending Dec January February March April Ju May $ 200,000.00 $ 220,000.00 $ 180,000.00 $ 210,000.00 $ 200,000.00 $ $ 110,000.00 $ 80,000.00 $ 116,000.00 $ 88,000.00 $ 127,600.00 $ 72,000.00 $ 104,400.00 $ 84,000.00 $ 121,800.00 $ 80,000.00 $ Month's Of The Year Budgeted Sales in Dollars Schedule of Expected Cash Collections: Beginning Account Receivable: January Sales: February Sales: March Sales: April Sales: May Sales: June Sales: July Sales: August Sales: September Sales: October Sales: November Sales: December Sales: Bad Debt: Total Cash Collections: Accounts Receivable Ending Balance: (DOC) 14,400.00) SGOL S00.00 $ 186,333.33 $ 204,000.00 $ 199,600.00 $ 188,400.00 $ 201,800.00 $ 132,000.00 Budgeting Assumptions Purchase Budget Cash Receipts Budget Cash Disbursement Bu H Cash Reciepts Budget For the year ending December 31, 2021 Month April May June July August September October November - December 000.00 $ 210,000.00 $ 200,000.00 $ 180,000.00 $ 175,000.00 $ 170,000.00 $ 185,000.00 $ 200,000.00 $ 210,000.00 $ 220,000.00 600.00 000.00 $ 104,400.00 $ 84,000.00 121,800.00 $ 80,000.00 $ 116,000.00 $ 72,000.00 $ 104,400.00 $ 70,000.00 $ 101,500,00 $ 68,000.00 $ 98,600.00 $ 74,000.00 $ 107,300.00 $ 80,000.00 $ 116,000.00 $ 84,000.00 $ 121,800.00 $ 88,000.00 R00100) 00.00 $ 188,400.00 $ 201,800.00 $ 188,000.00 $ 174,400.00 $ 169,500.00 $ 172,600.00 $ 187,300.00 $ 200,000.00 $ 209,800.00 Budget Cash Disbursement Budget Display Settings F El Cash D For the year Month's Of The Year Purchases Required: January February March April $ 88,800.00 $ 110,400.00 $ 124,200.00 $ 120,600.00 $ $ 80,000.00 $ 26,640.00 $ 62,160.00 $ 33,120.00 $ 77,280.00 $ 37,260.00 $ 86,940.00 $ 36,180.00 Beginning Accounts Payable: January: February March: April: May: June: July: August: September: October: November: December: Total Cash Disbursement: Account Payable Ending Balance: $ 106,640.00 $ 95,280.00 $ 114,540.00 $123,120.00 $ 96,180.00 Purchase Budget Budgeting Assumptions Cash Receipts Budget Cash D Awesome, LLC. Cash Disbursement Budget For the year ending December 31, 2021 Month April May July August September October Novem! 20 $ 120,600.00 $ 109,200.00 $ 105,300.00 $ 102,300.00 $ 110,100.00 $ 119,100.00 $ 125,400.00 $ 131,400 June DO DO $ 86,940.00 $ 36,180.00 $ 84,420.00 $ 32,760.00 $ 76,440.00 $ 31,590.00 $ 73,710.00 $ 30,690.00 $ 71,610.00 $ 33,030.00 $ 77,070.00 $ 35,730.00 $ 83,370.00 $ 37,620.00 $ 87,780.C $ 39,420.0 -00 $ 123,120.00 $ 117,180.00 $ 108,030.00 $ 104,400.00 $ 104,640.00 $ 112,800.00 $ 120,990.00 $127,200.0 udget Cash Disbursement Budget . 2021 July August September October November December Year Total 102,300.00 $ 110,100.00 $ 119,100.00 $125,400.00 $ 131,400.00 $137,400.00 $1,384,200.00 73,710.00 30,690.00 $ 71,610.00 $ 33,030.00 $ 77,070.00 $ 35,730.00 $ 83,370.00 $ 37,620.00 $ 87,780.00 $ 39,420.00 $ 91,980.00 $ 41,220.00 5104,400.00 $ 104,640.00 $ 112,800.00 $ 120,990.00 $127,200.00 $ 91,980.00 $1,326,800.00 January 20,000.00 $ $ Particulars Beginning Balance: Add: Cash Receipts: Cash Receipts from Customers: $ 186,333.33 Total Cash Available: $ 206,333.33 Less: Cash Disbursements: D0,000.00 Inventory Purchases: Sales Commisions: Operating Expenses: Dividends: Total Cash Disbursement: (66,640,00 Total Operating Cash Flows: $ 39,693.33 Financing Line of Credit Used: Repayments: Interest Accumulated: $ $ $ Total Financing Cash Flows: $ Ending Cash Balance: Purchase Budget eady EO $ 39,693.33 Cash Receipts Budget Awesome, LLC. Cash Budget For the year ending December, M March April May Jun 86,413.33 $ 113,473.33 $ 117,753.33 $ 142, February 39,693.33 $ $ $ 204,000.00 $ 199,600.00 $ 188,400.00 $ 201,800.00 $ 188, $ 243,693.33 $ 286,013.33 $ 301,873.33 $319,553.33 $ 330, 118,000100YS (21,000,00) S (20,000.00 $ DIGDOGO S (157,280.00) S (172,510,00) S (184, 120.00) S (17, 180:00) S 170,0 $ 86,413.33 $ 113,473.33 $ 117,753.33 $ 142,373.33 $ 160,3- - $ - $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ - - $ $ $ $ $ $ $ 86,413.33 $ 113,473.33 $ 117,753.33 $ 142,373.33 $ 160,34 Cash Disbursement Budget Cash Budget Income H 1 K C. mber, 31 2021 Month Schedule June July August September October $ 142,373.33 $ 160,343.33 $ 172,843.33 $ 180,703.33 $ 182,003.33 $ $ 188,000.00 $ 174,400.00 $ 169,500.00 $ 172,600.00 $ 187,300.00 $ $ 330,373.33 $ 334,743.33 $ 342,343.33 $353,303.33 $ 369,303.33 $ 27,500.00 0,000.00 TARDE 160,420.00 3 S 160,343.33 172,843.33 S 180,703.33 182,003.33 $ 188,313.33 $ $ $ $ $ $ $ $ $ 33 172,843.33 $ 180,703.33 182,003.33 188,313.33 2 u 160,343.33 S Income Sta ... K L M N September October November December Year Totals $ 180,703.33 $ 182,003.33 $ 188,313.33 $ 200,113.33 $ 1,604,026.67 $ 172,600.00 $ 187,300.00 $ 200,000.00 $ 209,800.00 $ 2,281,733.33 $ 353,303.33 $ 369,303.33 $ 388,313.33 $ 409,913.33 $ 3,885,760.00 PODOU ROD ALES 120,000 ALLED 000 200 000 CASH AMOUNT SIDO) 191.000,00 180,090,00 183.200.00 80.0 12,046 $ 182,063.33 $ 188,313.33 $200,113,33 S 254.933.33 $ 1,838,960.00 $ $ $ $ $ $ $ $ $ $ $ $ $ $ !! $ $ $ $ $ 182,003.33 $ 188,313.33 $ 200,113.33 $ 254,933.33 $ 1,838,960 Zoom
