Book1 - Microsoft Excel - X Home Insert Page Layout Formulas Data Review View = = = General Calibri BI U - - 12 - A À - >- A Wrap Text Merge & Center 1 1 - * Cut La Copy Paste - Format Painter Clipboard 3 Autosum * Conditional Format Formatting as Table Styles Cell Styles Insert Delete Format 2 Clear Sort & Find & Filter Select Editing Font Alignment Number Cells A3 - For Sales Budget F G H I J 2 June 4 Months 5 Budgeted unit sales 6 Selling Price Per unit 7 Total sales Cravet Sales Company Sales Budget April |May 35000 $ 8.00 $ $ 2,80,000.00 $ 45000 8.00 $ 3,60,000.00 $ Quarter 60000 8.00 $ 4,80,000.00 $ 140000 8.00 11,20,000.00 collection From sales 25% In the month of sales 50% In the month following sales 25% In the Second month following sales Schedule of expected cash collections Months Sales April May $ 1,92,000.00 $ 48,000.00 13 June Quarter $ 48,000.00 $ 2,24,000.00 $ 1,12,000.00 $ $ 1,68,000.00 $ 2,80,000.00 $ 70,000.00 $ 1,40,000.00 $ 70,000.00 $ 2,80,000.00 15 February sales=(24000*$8)*25% in April March Sales=(28000*$8)*50% in April and 16 (28000*$8)*25% in May April sales=($280000*25%) in April,($280000*50%) 17 in May and ($280000*25%) in June May sales=($360000)*25% in May and 18 ($360000*50%) in June 19 June Sales=($4800000*25%) in June Sheet1 Sheet2 Sheet3 Ready $ 90,000.00 $ $ $ 3,60,000.00 4,80,000.00 1,80,000.00 $ 1,20,000.00 $ 270.000 onlc 2,70,000.00 1,20,000.00 ooc ooo on I 20 TALLIC-L--Il- + 1° Et See BOU 100% 0 - PADENG 121 12:35 13-11-2019 0
Book1 - Microsoft Excel - X Home Insert Page Layout Formulas Data Review View = = = * Cut La Copy Paste - Format Painter Clipboard Calibri BI U - - 12 - A À - >- A Wrap Text Merge & Center General 1 1 % 9 18 23 conditional a forme - 3 insert Delete Format Autosum * 2 Clear so Conditional Format Formatting as Table Styles Cell Styles Insert Delete Format 2 Clear Sort & Find & Filter Select Editing se Font Alignment Number Cells A3 - Fax Sales Budget F G H I J Schedule of expected cash collections Months Sales April [May $ 1,92,000.00 $ 48,000.00 Quarter $ 48,000.00 $ 2,24,000.00 $ 1,12,000.00 $ 56,000.00 $ 1,68,000.00 13 14 15 February sales=(24000*$8)*25% in April March Sales=(28000*$8)*50% in April and 16 (28000*$8)*25% in May April sales=($280000*25%) in April,($280000*50%) 17 in May and ($280000*25%) in June May sales=($360000)*25% in May and 18 ($360000*50%) in June 19 June Sales=($4800000*25%) in June 20 Total Cash collections $ 2,80,000.00 $ 70,000.00 $ 1,40,000.00 $ 70,000.00 $ 2,80,000.00 90,000.00 $ 1,80,000.00 $ 1,20,000.00 $ 3,70,000.00 $ 2,70,000.00 1,20,000.00 8,86,000.00 2,86,000.00 $ 21 $ 3,60,000.00 $ $ 4,80,000.00 $ 15,36,000.00 $ 2,30,000.00 $ Cravet Sales Company Mercendise Purchase Budget April May June 35000 45000 Quarter 60000 140000 24 Budgeted Unit Sales=(A) Add:Ending Inventory=(450000*90%) in 25 April (60000*90% in May),(40000*90%) in June=(B) 26 Total Needs=(C)=(A)+(B) 27 Less: Opening Inventory=(D) 28 Required Purchases=(E)=(C)-(D) 29 Cost of Purchase Per Unit=(F) 30 Total Cost of Purchase=(E)*(F) n " Sheeti Sheet2 Sheet3 - Ready 40500 75500 31500) 44000 5.00 2,20,000.00 54000 99000 40500 58500 5.00 2,92,500.00 36000 96000 54000 42000 5.00 2,10,000.00 36000 176000 31500 144500 5.00 7,22,500.00 $ $ $ $ $ $ $ ä I + 1 e B e 9 BOJ 100% 0 op - ENG 13-1-2019
Book1 - Microsoft Excel - X Home Insert Page Layout Formulas Data Review View * Cut Copy = = = Calibri BI U - - 12 - A À - >- A Wrap Text Merge & Center General 9.% , o -. 1 1 conditional Fore - 3 Insert Delete Format Autosum * Ž clear that Paste Insert Delete Format Format Painter Clipboard Conditional Format Formatting as Table Styles Cell Styles 2 Clear Sort & Find & Filter Select Editing there Font Alignment Number Cells A3 fx Sales Budget F G H I J 31 Required Purchases=Budgeted Sales+Ending Inventory-Opening Inventory 32 Total cost of Purchae=Required Purchases* cost of purchase per unit 33 June Quarter $ 85,750.00 34 Cravet Sales Company 35 Budgeted Cash disbursement of Mercendise Purchase 36 Months April May 37 Accounts Payable $ 85,750.00 April=($2200000*50%) in April,($220000*50%) in 38 May $ 1,10,000.00 $ 1,10,000.00 May=($292500*50%) in May an ($292500*50%) in 39 June $ 1,46,250.00 $ 40 June=($210000*50%) in June $ 41 Total Cash Payments $ 1,95,750.00 $ 2,56,250.00 $ 2,20,000.00 1,46,250.00 $ 1,05,000.00 $ 2,51,250.00 $ 2,92,500.00 1,05,000.00 7,03,250.00 43 44 45 Months 46 Cash Balance 47 Add: Collections from customer 48 Total Cash available 49 Less: Disbursement 50 Mercendise Purchase 61 Nazoandcalacios Sheet1 Sheet2 Sheet3 Ready Cravet Sales Company For the three months ending June 30th April May June $ 14,000.00 $ 10,250.00 $ $ 2,30,000.00 | $ 2,86,000.00 $ $ 2,44,000.00 $ 2,96,250.00 $ Quarter 10,000.00 $ 3,70,000.00 $ 3,80,000.00 $ 14,000.00 8,86,000.00 9,00,000.00 $ 1,95,750.00 $ 27 non no lo 2,56,250.00 $ 22 nnnnnlo 2,51,250.00 $ 22. nnn no lo 7,03,250.00 cc non nn 1 Đ 1 2 • Ô e $ 90 OD op - 100% 0 ENG 13-1-2019
Book1 - Microsoft Excel - X Home Insert Page Layout Formulas Data Review View * Cut Copy = = = General Calibri BI U - - 12 - A À - >- A Wrap Text Merge & Center Fill Paste Format Painter Clipboard Insert Delete Format Conditional Format Formatting as Table Styles Cell Styles 2 Clear Z Et Sort & Find & Filter Select Editing Font Alignment Number Cells A3 for Sales Budget D E F G H I J в с Cravet Sales Company For the three months ending June 30th April May June $ 14,000.00 $ 10,250.00 $ $ 2,30,000.00 | $ 2,86,000.00 $ $ 2,44,000.00 $ 2,96,250.00 $ Quarter 10,000.00 $ 3,70,000.00 $ 3,80,000.00 $ 14,000.00 8,86,000.00 9,00,000.00 $ $ $ $ $ 1,95,750.00 $ 22,000.00 $ 14,000.00 35,000.00 3,000.00 43 44 45 Months 46 Cash Balance 47 Add: Collections from customer 48 Total Cash available 49 Less: Disbursement 50 Mercendise Purchase 51 Wages and Salaries 52 Utilities 53 Commissions(35000*$1),(45000*$1),(60000*$1) 54 Miscellaneous Expenses 55 Land 56 Dividend Paid 57 Total Disbursemnts 58 Excess/Deficiency of receipts over 59 disbursements 60 Financing: 61 Borrowings 62 Repayments 63 Interest=(48000*1%*3)+($79000*1%*2) 64 Total Financing 65 Cach Balance anding Sheet1 Sheet2 Sheet3 Ready 2,56,250.00 $ 22,000.00 $ 14,000.00 $ 45,000.00 $ 3,000.00 $ 25,000.00 2,51,250.00 $ 22,000.00 $ 14,000.00 60,000.00 $ 3,000.00 7,03,250.00 66,000.00 42,000.00 1,40,000.00 9,000.00 25,000.00 12,000.00 9,97,250.00 -97,250.00 $ $ $ 12,000.00 2,81,750.00 $ 37,750.00 $ 3,65,250.00 $ -69,000.00 $ 3,50,250.00 29,750.00 $ 48,000.00 $ 79,000.00 $ $ - 16,000.00 $ -3,020.00 $ - 19,020.00 $ 10 730 001 1,27,000.00 -16,000.00 -3,020.00 1,07,980.00 10 730.00 $ I$ 48,000.00 $ 10.250.000 $ 79,000.00 $ 10.000.000 BOJ 100% 0 ve 12:36 g A O ENG 12 13-11-2019 + .
Book1 - Microsoft Excel - X Home Insert Page Layout Formulas Data Review View * Cut Copy = = = General Calibri BI U - - 12 - A À - >- A Wrap Text Merge & Center Fill Paste Insert Delete Format Conditional Format Formatting as Table Styles Format Painter Clipboard Cell Styles 2 Clear Z Et Sort & Find & Filter Select Editing Font Alignment Number Cells for Sales Budget B 63 Interest=(48000*1%*3)+($79000*1%*2) 64 Total Financing 65 Cash Balance ,ending $ $ 48,000.00 $ 10,250.00 $ C $ 79,000.00 $ 10,000.00 $ D -3,020.00 $ - 19,020.00 $ 10,730.00 $ -3,020.00 1,07,980.00 10,730.00 Cravet Sales Company Budgeted Income Statement For the three months ended June 30th $ 11,20,000.00 $ 11,20,000.00 $ 7,00,000.00 1,40,000.00 $ $ 8,40,000.00 2,80,000.00 71 Sales 72 Variable Expenes 73 Cost of goods sold 74 Commission 75 Contribution Margin 76 Fixed Expenses 77 Wages and Salaries 78 Utilities 79 Insurance($1200*3) 80 Miscellaneous Expenses 81 Depreciation=($1500*3) 82 Net Operating Income 83 Interest Expense 84 Net Income Sheet1 Sheet2 Sheet3 Ready 66,000.00 42,000.00 3,600.00 9,000.00 4,500.00 $ $ $ $ 1,25,100.00 1,54,900.00 3,020.00 1,51,880.00 Os 1 e 2: * e $ 9 OO op - 100% 0 ENG 13-1-2019
Book1 - Microsoft Excel - X Home Insert Page Layout Formulas Data Review View BE * Cut Copy = = = General Calibri BI U - - 12 - A À - >- A Wrap Text Merge & Center N Z 2222 1 1 - 3 Autosum * Paste Insert Delete Format Conditional Format Formatting as Table Styles Format Painter Clipboard Cell Styles 2 Clear Sort & Find & Filter Select Editing Font Alignment Number Cells D F G H I J 89 - For Sales Budget B C 85 86 Cost of goods sold=Opening Stock+Purchases-Closing stock=($157500+$722500-(36000* $5) 87 88 Cravet Sales Company Budgeted Balance Sheet 90 June 30th 91 Assets 92 Cash $ 10,730.00 93 Accounts Receivable $ 4,50,000.00 94 Inventory(36000*$5) $ 1,80,000.00 95 Prepaid insurance=($14400-3600) $ 10,800.00 96 Property & Equipment ,net=(172700+25000-4500) $ 1,93,200.00 97 Total Assets $ 8,44,730.00 98 99 Liabilities and stockholder's equity 100 Accounts Payable=($183180*50%) $ 1,05,000.00 101 Borrowings $ 1,11,000.00 102 Dividend Payable $ 12,000.00 103 Common Stock $ 3,00,000.00 104 Retained Earning $ 3,16,730.00 105 Total Liabilities and stockholder's equity $ 8,44,730.00 106 107 Pranorty Plant & Fruinment=Beginning halance+Addition. Denroristinn ► Sheet1 Sheet2 Sheet3 Ready + 1 e B ê e 9 BOJ 100% of ^ ENG 0 13-11-2019 Q
Book1 - Microsoft Excel - X Home Insert Page Layout Formulas Data Review View * Cut Copy = = = General Calibri BI U - - 12 - A À - >- A Wrap Text Merge & Center 1 1 - 3 Autosum * Paste - % 8.09 Insert Delete Format Format Painter Clipboard Conditional Format Formatting as Table Styles Cell Styles 2 Clear Sort & Find & Filter Select Editing Font Alignment Number Cells F G H I J fax Sales Budget А 102 Dividend Payable $ 12,000.00 103 Common Stock $ 3,00,000.00 104 Retained Earning $ 3,16,730.00 105 Total Liabilities and stockholder's equity $ 8,44,730.00 106 107 Property,Plant & Equipment=Beginning balance+Addition-Depreciation 108 109 Accounts Receivable 110 May sales(May Sales*25%) $ 90,000.00 111 June sales=(June Sales 75%) $ 3,60,000.00 112 Total $ 4,50,000.00 113 114 Retained Earnings at june 30th 116 Balance ,March 31st $ 1,76,850.00 117 Add: Net Income $ 1,51,880.00 118 Total $ 3,28,730.00 119 Less: Dividend declared $ 12,000.00 120 Balance ,June 30th $ 3,16,730.00 121 122 115 123 124 Sheet1 Sheet2 Sheet3 Ready BOU 100% 0 ve 12:36 g A O ENG 12 13-11-2019 + .