AutoSave OFF 1 Course Budget Project Template v.1 Fall 2022 Home Insert Draw Page Layout Formulas Data Review View ? Tell me Share Calibri (Body) * 11 AA ag v Custom i Conditional Formatting Insert v E Format as Table v x Delete v Paste BIUV OvAv $ ~ % " $68 398 Editing Analyze Sensi 27 v Cell Styles Format Data N8 X V fx =M8 D H M N 0 Q R S T U See instructions and additiona formation required at the bottom of the page (starting on line 50) TUAL BUDGET BUDGET BUDGET BUDGET BUDGET BUDGET BUDGET BUDGET BUDGET BUDGET BUDGET BUDGET 20XX Jan 20XX Feb 20XX Mar 20XX Apr 20XX May 20XX Jun 20XX Jul 20XX Aug 20XX Sep 20XX Oct 20XX Nov 20XX Dec 20XX 4 ASSETS 5 Cash 100,000 49,477 (15,329) (35,212) (30,537) (10,418 246 19,310 46,920 67,145 117,176 147 287 178,637 6 Accounts Receivable 150,000 279,150 364,725 483,863 543,431 48,516 01,058 700,029 699,514 747 257 599.829 423,514 308,057 7 Finished Goods Inventory 275,000 258,777 242,555 96,708 150,862 0,204 7,759 40,125 22,492 4,859 2,038 28,840 352.665 3 Raw Materials 75,000 75,000 75,000 75,000 75,000 75,000 75,000 75,000 75,000 75,000 75,000 75,00 75,000 9 Prepaid Expenses 25,000 25,000 25,000 25,000 25,000 25,000 25,000 25,000 25,000 25,000 25,000 25,000 25,000 10 Total Current Assets 25,000 87 404 91,951 45,359 763,756 28,301 859,063 859,464 868,926 919,261 819,042 699,641 939,359 11 12 Machinery & Equipment 500,000 500,000 500,000 750,000 750,000 750,000 1,050,000 1,050,000 1,050,000 1,050,000 1,050,000 1,050,000 1,050,000 13 Buildings 500,000 1,500,000 1,500,000 1,500,000 1,500,000 1,500,000 1,500,000 1,500,000 1,500,000 1,500,000 1,500,000 1,500,000 1,500,000 14 Vehicles 300,000 300,000 300,000 300,000 375,000 375,000 375,000 375,000 375,000 450,000 450,000 450,000 450,000 15 Accumulated Depreciation (750,000) (760,833) (771,666) (784,582) (798,124) (811,665) (827,706) (843,748) (859,789) (876,455) (893,122) (909,788) (926,454) 16 Total Long-Term Assets 550,000 1,539,167 1,528,334 1,765,418 1,826,876 1,813,335 2,097,294 2,081,252 2,065,211 2,123,545 2,106,878 2,090,212 2,073,546 17 18 Total Assets 2175,000 2,226,571 2,220,285 2,510,777 2,590,632 2,641,636 2,956,356 2,940,717 2,934,137 3,042,806 2,925,920 2,789,853 3,012,905 19 20 LIABILITIES & EQUITY 21 Accounts Payable 60,000 139,839 156,034 201,343 212,670 255,994 266,825 254,721 251,695 276,618 190.997 114,288 80,299 22 Accrued Expenses 40 000 40,000 40,000 40,000 40,000 40,000 40,000 40,000 40,000 40,000 40,000 DDO'OF 40,000 23 Total Current Liabilities 100,000 179,839 196,034 241,343 252,670 295,994 306,825 294,721 291,695 316,618 154,288 24 25 Long-Term Debt 725,000 1 713 286 1,701,533 1,938,491 2,000,030 1,986,524 2,271,474 2,256,373 2,241,222 2,300,645 2,285,017 2,269,336 2,253,603 26 27 Paid in Capita 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 28 Retained Earnings 100,000 83,445 72,718 80,943 87,932 109,117 128,057 139,621 151,220 175,542 159,906 116,229 65,178 29 Total Equity 350,000 333/445 322,718 330,943 337,932 359,117 378,057 389,623 401,220 425,542 409,906 366,229 315,178 30 31 Total Liabilities and Equity 175,000 2,226,571 2,220,285 2,510,777 2 2,590,632 2,641,636 2, 2,956,356 2,940,717 2,934,137 3,042,806 2,925,920 2,789,853 2,689,080 32 Check (must be zero) 323,825 33 The basic accounting equationAssets = Liabilities + Equity. This must equal each other or the balance sheet is not in balance. 34 35 36 37 Debt Amortization 38 1 725,000 1,725,000 1,725,000 1,713 286 1,701,533 1,689,741 1,677,909 1,666,038 1,654,128 1,642,177 1,630,187 1,618,157 1,606,087 39 Interest Expense (4% annual) 5,750 5,711 5.672 5,632 5,593 5,553 5,514 5,474 5,434 5,394 5,354 40 Principal Payment 11,714 11,753 11,792 11,832 11,871 11,911 11,950 11,990 12,030 12,070 12,110 41 Ending Base Balance 1,725,000 1,725,000 1,713,286 1,701,533 1,689,741 1,677,909 1,666,038 1,654,128 1,642,177 1,630,187 1,618,157 1,606,087 1,593,977 42 Effect of New Purchases 250,000 75,000 300,000 75,000 43 New Balance 1,725,000 1,713,286 1,951,533 2,013 491 2,000,030 2,286,524 2,271,474 2,256,373 2,316,222 2,300,645 2,285,017 2,269,336 44 Interest Expense (4% annual 5,750 5,711 6,505 6,712 6,667 7,622 7,572 7,521 7,721 7,669 7,617 7.564 45 Principal Payment 11,714 11,753 13,042 13 461 13,506 15,051 15,101 15,151 15,577 15,629 15,681 15,733 46 Ending Balance 1,713,286 1,701,533 1,938,491 2,000,030 1,986,524 2,271,474 2,256,373 2,241,222 2,300,645 2,285,017 2,269,336 2,253,603 47 Note: there are generalizations the debt assumptions based on principal balances and interest rates; this is not based on an actual amortization (for simplicity in the budget model) 48 49 50 Instructions The Balance Sheet is mainly filled in. Please take note of the following: A Comp. 1 Income Stmt A Comp. 1a Sales&Production Budget COGS Comp. 2 Balance Sheet A Comp. 2.a Capital Purchases A Comp. 3 Cash Flows Ready x Accessibility: Investigate. . AutoSave . OFF MAP ? C ... 1 Course Budget Project Template v.1 Fall 2022 Home Insert Draw Page Layout Formulas Data Review View Tell me Share Commen Calibri (Body) 11 AA = ab v Custom Conditional Formatting Insert v E Format as Table v Delete Paste BIU BY OVAv $ ~ % " $68 398 Editing Analyze Sensitivity Cell Styles v Format v Data N8 X V fx =M8 D G H M N 0 P R S 1 See instructions and additional ination required at the bottom of the page (starting on line 50) UA BUDGET BUDGET BUDGET BUDGET BUDGET BUDGET BUDGET BUDGET BUDGET BUDGET BUDGET BUDGET 3 20XX an 20XX Feb 20XX Mar 20XX Apr 20Xx May 20XX Jun 20XX Jul 20XX Aug 20XX Sep 20XX Oct 20XX Nov 20XX Dec 20XX 45 Principal Payment 11,714 11,753 13,042 13,461 13,506 15,051 15,101 15, 151 15,577 15,629 15,681 15,733 46 Ending Balance 1,713,286 1,701,533 1,938,491 2,000,030 1,986,524 2,271,474 2,256,373 2,241,222 2,300,645 2,285,017 2,269,336 2,253,603 47 Note: there are generalizations indebt assumptions based on principal balances and interest rates; this is not based on an actual amortization (for simplicity in the budget model) 48 49 EO Instructions The Balance Sheet is mainly filled in. Please take note of the following: -The balance sheet must be in balance (see line 32 and the note on line 33) (note the balance sheet will not be in balance until all three components are complete) -The Balance Sheet is also affected by the capital purchased budget (see the next tab; Comp. 2a Capital Purchases) -Also notice the effect of capital purchases on lines 12-14 You will need to complete the capital purchases tab on Comp. 2a You will notice that the December balance sheet is not in balance (see line 32) but it should be after Components 2 & 3 are complete. Please complete the following 1). Enter the value of the desired ending inventory from the Comp la Sales and Production budget in cell N7 2). Make sure that your balance sheet is in balance for December (line 33) 3). In the yellow box below, describe how you arrived at the dollar value of ending inventory in cell N7 4). In the yellow box, des heet not in balance until you added the inventory value in cell N7 5). In the yellow box, describe what impact that capital purchases had on the Balance Sheet 6J. In the yellow box, describe anything else that you find interesting about the balance sheet. Commentary In the yellow box below, please describe what is asked above in the instructions (items 3,4,5] Comp. 1 Income Stmt A Comp.1a Sales&Production Budget COGS A Comp. 2 Balance Sheet A Comp. 2.a Capital Purchases A Comp. 3 Cash Flows + Ready x Accessibility: Investigate " - + 909