Could anyone help me out with the monthly budget on the 2017 tab? Please and thank you.

EARRINGS 2017 BUDGET Jan Sales forecast Sales Price Pairs Price Feb March 15,000 11 40,000 11 20,000 11 15,000 40,000 20,000 11 165,000 11 440,000 11 220,000 Schedule 1A Budget in Units Selling Price /Unit Total Sales Schedule 1B BB AR Jan Feb March April May June July August September October November December January February Total Cash Collected Ending AR 630,000 16,500 38,500 44,000 102,667 22,000 646,500 38,500 82,500 102,667 124,667 51,333 Budgeted Sales 15,000 40,000 20,000 + Des. EI =Required -BI =Purchasing Plan 4,000 19,000 2,000 42,000 4,000 46,000 2,000 22,000 2,000 24,000 46,000 184,000 24,000 96,000 Schedule 2 Schedule 3A Purchsing Plan =dollar purchases Schedule 3 B AP- Beg. Bal Q3 2003 Q4 2003 Q1 2004 Q2 2004 Q3 2004 Q4 2004 Cash Payments Ending AP Schedule 4 Sales in Dollars Variable S&A %: Sales Commissions Fixed S&A: Advertising Rent Salaries Utilities Insurance 200,000 18,000 106,000 7,000 3,000 66,667 6,000 35,333 2,333 1,000 66,667 6,000 35,333 2,333 1,000 66,667 6,000 35,333 2,333 1,000 Depreciation Total Fixed S&A Less: Expired Ins. 14,000 348,000 3,000 4,667 116,000 1,000 4,667 116,000 1,000 4,667 116,000 1,000 Less: Deprec. Cash Disb. For OE 14,000 331,000 4,667 121,667 4,667 121,667 4,667 121,667 Schedule 5 Beginning Balance Add Receipts: Collections from Cust Issuance of Stock Other CR Total Available Less Disbursements: Purchases Variable OE Fixed OE Equipment Purch. Dividends Repurchase of Stock Purchase Insurance Total Disbursements Cash + or (-) Financing: Borrowings - BOQ Repayments - EOQ Interest Paid Total Financing Ending Balance Schedule 6 Sales Less Variable Costs Contribution Margin Less Fixed Costs Pre Tax OI Less: Int. Ex. Pre Tax NI Schedule 7 Assets Current Assets Cash AR Inventory Prepaid Insurance Total Current Assets Plant & equipment PP&E-Net Total Assets Liabilities & S.E. Current Liabilites AP Dividends Payable Notes Payable Interest Payable Total Current Liab. Non Current Liabilities Term Loan Total Non Current liabilities Total Liabilities Stock. Equity Common Stock Retained Earnings Total S.E. Total Liab. & S.E. April May June July August Sept Oct 20,000 11 20,000 11 40,000 11 20,000 11 30,000 13 40,000 13 20,000 13 20,000 20,000 40,000 20,000 30,000 40,000 20,000 11 220,000 11 220,000 11 440,000 11 220,000 13 390,000 13 520,000 13 260,000 51,333 22,000 51,333 22,000 51,333 44,000 102,667 22,000 51,333 39,000 91,000 52,000 121,333 26,000 73,333 51,333 73,333 51,333 95,333 102,667 124,667 51,333 90,333 91,000 143,000 121,333 147,333 60,667 20,000 20,000 40,000 20,000 30,000 40,000 20,000 2,000 22,000 2,000 24,000 4,000 24,000 2,000 26,000 2,000 42,000 4,000 46,000 3,000 23,000 2,000 25,000 4,000 34,000 3,000 37,000 2,000 42,000 4,000 46,000 2,000 22,000 2,000 24,000 24,000 96,000 26,000 104,000 46,000 184,000 25,000 100,000 37,000 148,000 46,000 184,000 24,000 96,000 66,667 6,000 35,333 2,333 1,000 66,667 6,000 35,333 2,333 1,000 66,667 6,000 35,333 2,333 1,000 66,667 6,000 35,333 2,333 1,000 66,667 6,000 35,333 2,333 1,000 66,667 6,000 35,333 2,333 1,000 66,667 6,000 35,333 2,333 1,000 4,667 116,000 1,000 4,667 116,000 1,000 4,667 116,000 1,000 4,667 116,000 1,000 4,667 116,000 1,000 4,667 116,000 1,000 4,667 116,000 1,000 4,667 121,667 4,667 121,667 4,667 121,667 4,667 121,667 4,667 121,667 4,667 121,667 4,667 121,667 Nov Dec Jan Feb 20,000 13 35,000 13 20,000 13 35,000 13 20,000 35,000 20,000 35,000 13 260,000 13 455,000 13 260,000 13 455,000 60,667 26,000 60,667 45,500 106,167 26,000 86,667 60,667 106,167 106,167 132,167 60,667 60,667 45,500 106,167 106,167 20,000 35,000 20,000 35,000 3,500 23,500 2,000 25,500 2,000 37,000 3,500 40,500 3,500 23,500 2,000 25,500 0 35,000 3,500 38,500 25,500 102,000 40,500 162,000 25,500 102,000 38,500 154,000 66,667 6,000 35,333 2,333 1,000 66,667 6,000 35,333 2,333 1,000 66,667 6,000 35,333 2,333 1,000 66,667 6,000 35,333 2,333 1,000 4,667 116,000 1,000 4,667 116,000 1,000 4,667 116,000 1,000 4,667 116,000 1,000 4,667 121,667 4,667 121,667 4,667 121,667 4,667 121,667 1 2 3 4 5 6 7 8 9 10 11 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 45 A EARRINGS UNLIMITED B C Earrings Unlimited is a start up company that designs Earrings, contracts out the manufacturing, and distributes them through a variety o Earrings is halfway through its second year . It had a very successful first year because of several large contracts with chain stores. The company lost some of the contracts. The company is expecting a large increase in sales during 2016 as additional contracts have been si geographically and will have a larger customer base. The company has made a six quarter comprehensive budget. Please examine the b "Questions" tab. Next, complete the 2017 monthly budget tab. Complete the requirements on the 2017 monthly budget tab. Submit the copy and an electronic copy. Please print in landscape mode and be sure all formatting and page breaks are proper. Budget Template Sales forecast 2015 3rd Quarter Pairs Schedule 1A 22,000 Sales by Quarter 3rd Quarter Budget in Units Selling Price /Unit Total Sales 22,000 12 264,000 Schedule 1B BB AR Q3 2008 Q4 2003 Q1 2004 Q2 2004 Q3 2004 Q4 2004 Total Cash Collected Ending AR Sched. Cash Collections 3rd Quarter See B 149 307,200 184,800 2015 Purchases Budget - Units 3rd Quarter Schedule 2 Budgeted Sales 46 + Des. EI 47 =Required 48 -BI 49 =Purchasing Plan 50 228,000 79,200 22,000 See B 150 and K55 6,000 28,000 8,800 19,200 A 51 B C 2015 A B 52 Schedule 3A 53 C Purchases Budget in $ 3rd Quarter 54 Purchsing Plan 55 =dollar purchases 56 57 Schedule 3 B 58 59 AP- Beg. Bal 60 Q3 2003 61 Q4 2003 62 Q1 2004 63 Q2 2004 64 Q3 2004 65 Q4 2004 66 Cash Payments 67 Ending AP 68 69 Schedule 4 70 71 Sales in Dollars 72 Variable S&A %: 73 Sales Commissions 74 75 Fixed S&A: 76 Advertising 77 Rent 78 Salaries 79 Utilities 80 Insurance 81 Depreciation 82 Total Fixed S&A 83 Less: Expired Ins. 84 Less: Deprec. 85 Cash Disb. For OE 86 87 19,200 $ 76,800 Scheduled Cash Payments for Purchases 3rd Quarter See B 161 $ $ 47,600 38,400 $ $ 86,000 38,400 Quarterly Operating Exp. 3rd Quarter 264,000 0.05 13,200 $ 200,000 18,000 106,000 7,000 3,000 14,000 348,000 3,000 $ 14,000 331,000 $ 2015 A 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 B Schedule 5 C Cash Budget 3rd Quarter Beginning Balance See B 148 for BB $ 50,900 Add Receipts: Collections from Cust Issuance of Stock Other CR Total Available 307,200 358,100 Less Disbursements: Purchases Variable OE Fixed OE Equipment Purch. Dividends Repurchase of Stock Purchase Insurance Total Disbursements 86,000 13,200 331,000 15,000 24,000 469,200 Cash + or (-) (111,100) Financing: Borrowings - BOQ Repayments - EOQ Interest Paid Total Financing 177,000 177,000 Ending Balance 65,900 2015 Income Statements - Contribution Format Schedule 6 3rd Quarter Sales 264,000 Less Variable Costs COGS S&A Total VC Contribution Margin 88,000 13,200 101,200 162,800 61.67% Less Fixed Costs S&A Total FC Pre Tax OI Less: Int. Ex. 348,000 348,000 (185,200) 5,310 A 138 139 Pre Tax NI 140 B C (190,510) A 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 B C 2015 Schedule 7 Beginning Bal. Sheet 30-Jun-15 Budgeted Bal. Sheets 3rd Quarter Assets Current Assets Cash AR Inventory Prepaid Insurance Total Current Assets 50,900 228,000 35,200 3,000 317,100 65,900 184,800 24,000 24,000 298,700 Plant & equipment PP&E-Net 922,000 908,000 1,239,100 1,206,700 47,600 15,000 - 38,400 177,000 62,600 5,310 220,710 168 Term Loan 251,000 251,000 169 Total Non Current liabilities 170 Total Liabilities 171 172 Stock. Equity 173 Common Stock 251,000 313,600 251,000 471,710 800,000 800,000 174 Retained Earnings 175 Total S.E. 176 125,500 925,500 (65,010) 734,990 1,239,100 1,206,700 157 Total Assets 158 159 Liabilities & S.E. 160 Current Liabilites 161 AP 162 Dividends Payable 163 Notes Payable 164 Interest Payable 165 Total Current Liab. 166 167 Non Current Liabilities 177 Total Liab. & S.E. 178 179 180 181 182 183 184 Cool Cool D E 1 Date Submitted: 2 3 4 cturing, and distributes them through a variety of retail locations that cater to les jeune filles. f several5large contracts with chain stores. The ending quarters of 2015 were very weak as the uring 2016 6 as additional contracts have been signed and the company is more diversified r comprehensive budget. Please examine the budget and answer the questions on the 7 nts on the 2017 monthly budget tab. Submit the completed spreadsheet analysis as a hard nd page 8breaks are proper. 9 10 11 12 13 14 15 16 17 18 19 20 2016 21 4th Quarter 1st Quarter 22 20,000 90,000 23 24 25 4th Quarter 1st Quarter 26 20,000 90,000 27 12 12 28 240,000 1,080,000 29 30 31 4th Quarter 1st Quarter 32 33 184,800 34 72,000 168,000 35 324,000 36 37 38 39 256,800 492,000 40 168,000 756,000 41 42 2016 43 44 4th Quarter 1st Quarter 45 20,000 90,000 46 47 48 49 50 27,000 47,000 6,000 41,000 22,500 112,500 27,000 85,500 F 2nd Quarter 75,000 2nd Quarter 75,000 12 900,000 2nd Quarter 756,000 270,000 1,026,000 630,000 2nd Quarter 75,000 22,500 97,500 22,500 75,000 D 51 E 2016 F 52 53 D E F 4th Quarter 1st Quarter 2nd Quarter 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 41,000 $ $ $ $ 84 85 86 87 1st Quarter 120,400 $ 82,000 $ 4th Quarter $ 75,000 342,000 $ 38,400 82,000 $ $ $ $ 81 82 83 164,000 $ 4th Quarter $ 85,500 2nd Quarter 82,000 171,000 $ $ 171,000 150,000 253,000 $ 171,000 $ 321,000 150,000 1st Quarter 240,000 0.05 12,000 $ 300,000 2nd Quarter 1,080,000 0.05 54,000 $ 900,000 0.05 45,000 200,000 18,000 106,000 7,000 3,000 200,000 18,000 106,000 7,000 3,000 200,000 18,000 106,000 7,000 3,000 14,000 348,000 $ 3,000 14,000 348,000 $ 3,000 14,000 348,000 3,000 14,000 331,000 $ 14,000 331,000 $ 14,000 331,000 2016 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 ibution Format 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 D E 4th Quarter F 1st Quarter 2nd Quarter 65,900 40,300 40,300 256,800 492,000 1,026,000 322,700 532,300 1,066,300 120,400 12,000 331,000 - 253,000 54,000 331,000 50,000 - 321,000 45,000 331,000 15,000 463,400 688,000 712,000 (140,700) (155,700) 354,300 181,000 196,000 181,000 196,000 (283,000) (30,780) (313,780) 40,300 40,300 40,520 2016 4th Quarter 1st Quarter 2nd Quarter 240,000 1,080,000 900,000 80,000 12,000 92,000 360,000 54,000 414,000 300,000 45,000 345,000 148,000 61.67% 666,000 61.67% 555,000 61.67% 348,000 348,000 348,000 348,000 348,000 348,000 (200,000) 318,000 207,000 10,740 16,620 9,250 D 138 139 140 E (210,740) F 301,380 197,750 D 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 E F 2016 4th Quarter 1st Quarter 2nd Quarter 40,300 168,000 108,000 21,000 337,300 40,300 756,000 90,000 18,000 904,300 40,520 630,000 90,000 15,000 775,520 894,000 930,000 916,000 157 1,231,300 1,834,300 1,691,520 158 159 160 161 162 163 82,000 358,000 171,000 15,000 554,000 150,000 271,000 16,050 456,050 32,670 772,670 11,140 432,140 168 251,000 251,000 251,000 169 251,000 707,050 251,000 1,023,670 251,000 683,140 800,000 800,000 800,000 (275,750) 524,250 10,630 810,630 208,380 1,008,380 1,231,300 1,834,300 1,691,520 164 165 166 167 170 171 172 173 174 175 176 177 178 179 Cool 180 181 182 183 184 Cool Cool Don't Lose your Cools! G 1 2 3 4 5 6 7 8 9 10 11 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 45 46 47 48 49 50 H I 4th Quarter 2017 1st Quarter Team: Team Name Team members: Marlena Penn Victoria Thalassinos Briana Striegel Danae Corcoran 3rd Quarter 75,000 3rd Quarter 75,000 Quarterly Totals 4th Quarter 75,000 12 900,000 3rd Quarter 75,000 12 900,000 630,000 270,000 357,000 4,284,000 Quarterly Totals 4th Quarter 228,000 264,000 240,000 1,080,000 900,000 900,000 270,000 3,882,000 630,000 270,000 900,000 630,000 900,000 630,000 3rd Quarter 75,000 2015-16 Quarterly Totals 4th Quarter 75,000 75,000 357,000 22,500 97,500 22,500 75,000 22,500 97,500 22,500 75,000 22,500 379,500 8,800 370,700 G 51 H I 2015-16 52 53 G H 3rd Quarter 4th Quarter 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 75,000 $ 3rd Quarter $ $ 3rd Quarter $ $ 84 85 86 87 $ 75,000 370,700 300,000 1,482,800 Quarterly Totals 4th Quarter 150,000 150,000 $ $ 300,000 $ 150,000 $ $ $ 81 82 83 300,000 $ I Quarterly Totals 47,600 76,800 164,000 342,000 300,000 300,000 150,000 1,380,400 150,000 150,000 300,000 $ 150,000 Quarterly Totals 4th Quarter 900,000 0.05 45,000 $ 900,000 0.05 45,000 $ 4,284,000 200,000 18,000 106,000 7,000 3,000 200,000 18,000 106,000 7,000 3,000 1,200,000 108,000 636,000 42,000 18,000 14,000 348,000 $ 3,000 14,000 348,000 $ 3,000 84,000 2,088,000 18,000 14,000 331,000 $ 14,000 331,000 $ 84,000 1,986,000 214,200 2015-16 G 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 H 3rd Quarter I Quarterly Totals 4th Quarter 40,520 40,240 $ 50,900 900,000 900,000 3,882,000 3,932,900 940,520 940,240 300,000 45,000 331,000 40,000 300,000 45,000 331,000 716,000 676,000 1,380,400 214,200 1,986,000 90,000 30,000 24,000 3,724,600 224,520 264,240 208,300 (167,000) (17,280) (184,280) (104,000) (12,480) (116,480) 554,000 (554,000) (60,540) (60,540) 40,240 147,760 147,760 2016 3rd Quarter Quarterly Totals 4th Quarter 900,000 900,000 4,284,000 300,000 45,000 345,000 300,000 45,000 345,000 1,428,000 214,200 1,642,200 555,000 61.67% 555,000 61.67% 2,641,800 61.67% 348,000 348,000 348,000 348,000 2,088,000 2,088,000 207,000 207,000 553,800 14,400 4,220 60,540 G 138 139 140 H 192,600 I 202,780 493,260 G 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 H I 2017 3rd Quarter 4th Quarter 40,240 630,000 90,000 12,000 772,240 147,760 630,000 90,000 9,000 876,760 942,000 928,000 157 1,714,240 1,804,760 158 159 160 161 162 163 150,000 104,000 150,000 15,000 - 8,260 262,260 165,000 168 251,000 251,000 169 251,000 513,260 251,000 416,000 800,000 800,000 400,980 1,200,980 588,760 1,388,760 1,714,240 1,804,760 164 165 166 167 170 171 172 173 174 175 176 177 178 179 Cool 180 181 182 183 184 Cool J 1 2 3 4 5 6 7 8 9 10 11 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 45 46 47 48 49 50 K L Supporting Data & Notes 2nd Quarter 80,000 Selling price $12.00 Collection Pattern: Quarter of Next Quarter 0.3 0.3 0.3 0.3 0.3 0.3 Desired EI as % of next Quarter's sales 0.3 0.7 0.7 0.7 0.7 0.7 0.7 J 51 K L J K L 52 53 54 Purchse Price per Unit 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 $4 % of Purchase paid in Quarter Next Quart. 0.5 0.5 0.5 0.5 0.5 0.5 0.05 Var S&Admin/case 0.5 0.5 0.5 0.5 0.5 0.5 J 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 K L Planned capital expenditures. Payment of declared dividends Renewal of insurance policy for 1 year Minimum Cash Balance = $40,000 Borrow and repay in increments of $1,000 Interest rate = 12% Interest is paid when principal is paid prin Please prove these numbers. rate J 138 139 140 K Note: We have ignored taxes in this problem L J 141 142 143 144 145 146 147 148 Changes in AR 149 150 151 Change in Inv. 152 153 154 155 156 K L 3rd Q 08 4th Q 08 -43,200 -16,800 3rd Q 08 4th Q 08 -11,200 84,000 157 158 159 160 Changes in AP 161 162 163 Changes in Int. payable 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 3rd Q 08 4th Q 08 -9200 3rd Q 08 43,600 4th Q 08 5,310 10,740 M 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 per pair 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 M 51 M 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 M 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 M 138 139 140 M 141 142 143 144 145 146 147 148 1st Q 09 149 150 151 1st Q 09 152 153 154 155 156 588,000 -18,000 157 158 159 160 1st Q 09 161 162 163 1st Q 09 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 89,000 16,620 A 1 B C D E F G H I a 2 Graph 1 3 4 5 350,000 6 7 8 300,000 9 10 11 250,000 12 13 200,000 14 15 16 150,000 17 18 19 100,000 20 21 22 50,000 23 24 25 3rd Q 15 26 27 28 29 30 31 32 NCFOA 4th Q 15 1st Q 16 2nd Q 16 3rd Q 16 4th Q 16 A 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 B C D E F G H Q2: Describe the inventory policy of Earrings. What impact on cash flow would there be if the company could reduce inventory by 1/3? Graph 2 12 10 8 Cash flow at (0.3) New Cash flow at new target 6 4 2 3rd Q 15 4th Q 15 1st Q 16 2nd Q 16 3rd Q 16 4th Q 16 I A B C D E F G H I 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 Q3. Create a graph of pre tax income vs quarterly change in cash (row 107). Explain why the two differ. Graph 3 12 10 8 NI Change in cash 6 4 2 3rd Q 15 4th Q 15 1st Q 16 2nd Q 16 3rd Q 16 4th Q 16 2 A 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 B 3rd Q 15 C 4th Q 15 D 1st Q 16 E F 2nd Q 16 G 3rd Q 16 H 4th Q 16 Graph 4 I Q4. Create a graph of interest expense (income Explain why they differ. 12 10 8 Int. Exp 6 Int. Paid Int. Payable 4 2 0 3rd Q 15 4th Q 15 1st Q 16 2nd Q 16 3rd Q 16 4th Q 16 A 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 B C Graph 5 D E F G H I Q5. Create a graph of sales, variable costs, and fixed costs for the 6 quarters. of the forecast Summarize the relationships. 12 10 8 Sal es 6 VC FC 4 2 3rd Q 15 4th Q 15 1st Q 16 2nd Q 16 3rd Q 16 4th Q 16 Q 6. Earrings is concerned effect on net income and ca quarter of sale and 80% in t Please graph the present an A 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 B C D E F G H I Q 6. Earrings is concerned effect on net income and ca quarter of sale and 80% in t Please graph the present an Graph 6 12 10 8 Present NI Potential NI 6 4 2 3rd Q 15 4th Q 15 1st Q 16 2nd Q 16 3rd Q 16 4th Q 16 12 10 8 Present CF 6 Potential CF 4 2 3rd Q 15 4th Q 15 1st Q 16 2nd Q 16 3rd Q 16 4th Q 16 J 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 h Q 16 26 27 28 29 30 31 32 K L M N O Q1: Graph cash flow from operations and pre tax income. What drove Earrings greatly improved cash position in Quarter 4? Why didn't the improvement happen in Q3? P NCFOA 3rd Q 15 NCFOA PT Income 4th Q 15 1st Q 16 301,380 2nd Q 16 3rd Q 16 J 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 Cash flow at (0.3) K 3rd Q 15 L M 4th Q 15 1st Q 16 N 2nd Q 16 O P 3rd Q 16 4th Q 16 J 67 K L M N 3rd Q 15 4th Q 15 1st Q 16 O P New Cash flow at new target 68 69 e two differ. 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 NI 87 Change in 88 cash 89 90 91 92 93 94 95 96 NI 2nd Q 16 3rd Q 16 J 97 K L M N O P Change in cash 98 99of interest expense (income statement), interest paid (cash budget) and accrued interest payable (balance sheet)? te a graph hy they100 differ. 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 3rd Q 15 4th Q 15 1st Q 16 2nd Q 16 3rd Q 16 121 Int. Exp 122 Int. Paid 123 Int. Payable 124 125 J K 126 127 for the 6 quarters. of the d fixed costs 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143Sal es 144 145VC 146FC 147 148 149 150 151 152 Sales 153 VC 6 154 FC 155 L M N 3rd Q 15 4th Q 15 1st Q 16 O 2nd Q 16 P 3rd Q 16 Q 6. Earrings is concerned about a potential for a slowdown in collections from customers. What would be the effect on net income and cash available before financing if Earring's customers payment pattern changed to 20% in quarter of sale and 80% in the next quarter? Please graph the present and potential pre tax income and cash collections in the space provided to the left. J K L M N O P 156Q 6. Earrings is concerned about a potential for a slowdown in collections from customers. What would be the 157effect on net income and cash available before financing if Earring's customers payment pattern changed to 20% in 158quarter of sale and 80% in the next quarter? Please graph the present and potential pre tax income and cash collections in the space provided to the left. 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 3rd Q 15 4th Q 15 1st Q 16 2nd Q 16 3rd Q 16 4th Q 16 188 Present NI 189 Potential NI 190 191 3rd Q 15 4th Q 15 1st Q 16 2nd Q 16 3rd Q 16 4th Q 16 192 Present CF Q 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 4th Q 16 28 29 30 31 32 R S T U V W Q 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 R S T U V W Q 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 4th Q 16 96 R S T U V W Q 97 98 99 ce sheet)? 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 4th Q 16 121 122 123 124 125 R S T U V W Q 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 4th Q 16 152 153 154 155 d be the d to 20% in e left. R S T U V W 156 d be the d to 20% in 157 158 e left. 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 Q R S T U V W Requirements for Monthly Budget 1. Complete the monthly budget tab for 2017 using the quarterly logic and guidelines. All budgets should be completed. Don't lose your "Cools". 2. Apply the same monthly targets for ending inventory, collection periods, etc. for the quarterly budget. 3. Insert "If" statements for logic checks as needed. 3. Assume that fixed expenses per quarter from the quarterly budget for 2016 are the same for 2017 and incurred evenly over the months of the quarter. 4. For rows 102-105 in the cash budget. Assume that the same expenditures occurred in 2017 and in the first month of the quarter. 5. Prepare a graph of monthly operating cash flows, free cash flow, and monthly pre-tax income. Insert a comment box and describe why the amounts differ, and why the relationships change over the year. (Note: you must compute operating cash flows, and free cash flow.)