WEBVTT 1 00:00:00.500 --> 00:00:03.600 In February, I described the 12 steps, which you 2 00:00:03.600 --> 00:00:06.900 have to go through in order to be able to build the accounting 3 00:00:06.900 --> 00:00:10.000 statements. But I did the job now in 4 00:00:09.200 --> 00:00:12.800 March you are going to do the job with 5 00:00:12.800 --> 00:00:16.200 a kind of support obviously, but March 6 00:00:15.200 --> 00:00:18.300 is quite interesting because at the end of the month, you 7 00:00:18.300 --> 00:00:21.800 have to be now ready for the investment which will 8 00:00:21.800 --> 00:00:24.500 consist in buying machines so 9 00:00:24.500 --> 00:00:27.500 that you can do the job by yourself in sourcing 10 00:00:27.500 --> 00:00:28.300 the manufacturing. 11 00:00:29.200 --> 00:00:33.200 Now to build the financial statements you need some parameters you 12 00:00:32.200 --> 00:00:35.800 remember we start with sales the 13 00:00:35.800 --> 00:00:38.400 good news. Is that actual are going 14 00:00:38.400 --> 00:00:39.900 to match with forecast? 15 00:00:40.800 --> 00:00:43.600 700 units for b2c sold 16 00:00:43.400 --> 00:00:46.900 $30 each 700 B2B 17 00:00:46.600 --> 00:00:48.400 $25 each 18 00:00:49.200 --> 00:00:52.600 and you want also to make some purchases. The purchases are 19 00:00:52.600 --> 00:00:55.700 made at $20 each and you anticipate that 20 00:00:55.700 --> 00:00:58.700 you're going to purchase 1,350 units. 21 00:00:59.300 --> 00:01:03.000 But now to prepare the company for roles you have to hire human 22 00:01:02.000 --> 00:01:03.600 capital. 23 00:01:04.400 --> 00:01:07.700 Somebody to help you in administration. It's going 24 00:01:07.700 --> 00:01:10.100 to cost you $1,000. You have 25 00:01:10.100 --> 00:01:14.700 to hire a second sales person 1,300 and 26 00:01:14.700 --> 00:01:17.200 you have to hire a second engineer for 27 00:01:17.200 --> 00:01:20.800 technical feasibility and R&D 1,500. 28 00:01:21.900 --> 00:01:24.100 You are going to consider as an assumption that 29 00:01:24.100 --> 00:01:27.200 100% of the net earnings are going to be retained, which 30 00:01:27.200 --> 00:01:30.300 is quite cautious because you are going to grow and you 31 00:01:30.300 --> 00:01:32.600 need to reinvest 100% of your profit. 32 00:01:33.500 --> 00:01:34.700 What about the process? 33 00:01:35.500 --> 00:01:38.200 You remember that you have a Sprite sheet 34 00:01:38.200 --> 00:01:42.200 at your disposal and you go to the tab March frame 35 00:01:41.200 --> 00:01:45.100 where you are going to find empty cells. 36 00:01:44.100 --> 00:01:47.700 You will have to do the job in some 37 00:01:47.700 --> 00:01:50.500 cases and the spreadshe is going to do the job. In 38 00:01:50.500 --> 00:01:53.700 other cases when a cell is empty. You 39 00:01:53.700 --> 00:01:56.800 have to write the formula or you 40 00:01:56.800 --> 00:01:59.400 have to enter a figure I will 41 00:01:59.400 --> 00:02:02.600 give you an example quite soon when there is already a 42 00:02:02.600 --> 00:02:05.200 zero in the cell. It means that the formula has 43 00:02:05.200 --> 00:02:08.700 been introduced and the spreadsheet is going to do the calculation 44 00:02:08.700 --> 00:02:11.400 by itself, which is absolutely great. 45 00:02:11.400 --> 00:02:14.500 You remember that. There are 12 steps. I will 46 00:02:14.500 --> 00:02:17.100 propose you to do some pose at the 47 00:02:17.100 --> 00:02:20.800 end of each and every step so that you have enough time to 48 00:02:20.800 --> 00:02:23.500 do the job. Then I will provide you 49 00:02:23.500 --> 00:02:26.200 the solution for each and every step. 50 00:02:27.100 --> 00:02:30.300 There are a few difficulties obviously, but if you 51 00:02:30.300 --> 00:02:33.500 do the job methodically step by 52 00:02:33.500 --> 00:02:34.600 step it works. 53 00:02:35.600 --> 00:02:38.800 Just as a reminder. There will be positive figures 54 00:02:38.800 --> 00:02:42.200 sales are positive inventories are 55 00:02:41.200 --> 00:02:44.600 positive accounts receivable and accounts payable 56 00:02:44.600 --> 00:02:47.700 are all positive figures but there 57 00:02:47.700 --> 00:02:50.600 will be negative figures when it's about cash 58 00:02:50.600 --> 00:02:54.200 out disbursements. And of course cost usages 59 00:02:53.200 --> 00:02:56.000 and expenses are going to be 60 00:02:56.800 --> 00:02:57.500 negative figures. 61 00:02:58.600 --> 00:03:01.100 Now wish you good luck. We start with 62 00:03:01.100 --> 00:03:04.300 the p&l obviously and we start with a Top 63 00:03:04.300 --> 00:03:06.000 Line Sales revenues. 64 00:03:06.700 --> 00:03:09.800 What do You observe on the slide? Well, basically 65 00:03:09.800 --> 00:03:12.500 you have to enter the figures of the number 66 00:03:12.500 --> 00:03:15.400 of units for b2c. And the number 67 00:03:15.400 --> 00:03:17.300 of units for B2B. 68 00:03:18.100 --> 00:03:22.100 I have already introduced selling price solidars for 69 00:03:22.100 --> 00:03:26.000 one 25 dollars for the other one and you can see your zero 70 00:03:25.200 --> 00:03:28.300 in three cells the number 71 00:03:28.300 --> 00:03:31.100 of units which you have to provide to your 72 00:03:31.100 --> 00:03:34.600 customers that you sell in b2c. And a number of units for 73 00:03:34.600 --> 00:03:37.400 B2B. The sales figure is already 74 00:03:37.400 --> 00:03:40.700 calculated by the spreadsheet. It's going to be obviously 75 00:03:40.700 --> 00:03:43.700 the number of units b2c time 30 plus 76 00:03:43.700 --> 00:03:46.800 the number of units B2B times 25. It's 77 00:03:46.800 --> 00:03:49.300 not very complex. It's just a starting 78 00:03:49.300 --> 00:03:52.300 point. Then you press on pose you go 79 00:03:52.300 --> 00:03:54.300 to the spray cheat you do your job. 80 00:03:56.300 --> 00:03:59.600 Now job done for the revenues. It was not that difficult. 81 00:03:59.600 --> 00:04:02.100 Obviously you entered the figure of 82 00:04:02.100 --> 00:04:05.600 700 units sold in b2c, but 83 00:04:05.600 --> 00:04:08.500 the 700 units have been purchased at the 84 00:04:08.500 --> 00:04:10.300 level of $20 per unit. 85 00:04:10.900 --> 00:04:13.500 Then you will have some gross margin to calculate which 86 00:04:13.500 --> 00:04:17.100 is quite easy same figure 700 units 87 00:04:16.100 --> 00:04:19.200 for B2B sold at a lower 88 00:04:19.200 --> 00:04:22.200 price 25 and purchase at the same price 20. 89 00:04:22.900 --> 00:04:25.500 The computer the Sprite she just calculating the 90 00:04:25.500 --> 00:04:29.600 revenues which is 38,500. Congratulations. 91 00:04:28.600 --> 00:04:31.300 The top line is ready. 92 00:04:32.400 --> 00:04:35.700 Once you have calculated the revenues you have to deduct 93 00:04:35.700 --> 00:04:38.700 the cost of sales. The cost of goods sold in 94 00:04:38.700 --> 00:04:41.400 order to calculate the gross margin and then it's a 95 00:04:41.400 --> 00:04:42.700 matter of inventories. 96 00:04:43.300 --> 00:04:46.800 You remember that inventory is about units and it's 97 00:04:46.800 --> 00:04:49.700 about values. The good news is that values is 98 00:04:49.700 --> 00:04:52.100 units multiplied by 20 to make it 99 00:04:52.100 --> 00:04:55.600 simple because in this exercise I made 100 00:04:55.600 --> 00:04:58.900 it simple in terms of purchasing price same 101 00:04:58.900 --> 00:05:00.200 for each and every unit. 102 00:05:01.500 --> 00:05:04.300 Now the first Formula you have to enter in the 103 00:05:04.300 --> 00:05:08.300 spreadsheet is a begin inventory inventories 104 00:05:07.300 --> 00:05:10.300 at the beginning of the months. 105 00:05:11.200 --> 00:05:14.700 But it's quite obvious to understand that the inventories number 106 00:05:14.700 --> 00:05:17.900 of units at the beginning of March is exactly the 107 00:05:17.900 --> 00:05:20.100 same as the inventory at the end 108 00:05:20.100 --> 00:05:21.000 of February. 109 00:05:22.100 --> 00:05:25.800 So the formula which you have to enter is equal and you 110 00:05:25.800 --> 00:05:28.800 get to the other tab February equal 111 00:05:28.800 --> 00:05:31.300 inventory at the end of February. 112 00:05:32.400 --> 00:05:35.700 Once you get there, you have the purchases. The purchases 113 00:05:35.700 --> 00:05:38.700 are provided. I give you the information which 114 00:05:38.700 --> 00:05:42.300 is you purchase 1,350. So 115 00:05:42.300 --> 00:05:45.400 you understand that the formula is about taking the figures from 116 00:05:45.400 --> 00:05:48.300 the prior months and the figure is a number of 117 00:05:48.300 --> 00:05:50.500 units, which I propose you to purchase. 118 00:05:51.300 --> 00:05:52.900 What about the consumptions? 119 00:05:53.600 --> 00:05:56.900 The consumptions are basically what you 120 00:05:56.900 --> 00:05:59.600 have to consume in order to be able to sell you. 121 00:05:59.600 --> 00:06:03.100 Remember that gross margin is sales Minus cost 122 00:06:02.100 --> 00:06:05.300 of sales and then the formula is 123 00:06:05.300 --> 00:06:08.900 a number of units you have sold and 124 00:06:08.900 --> 00:06:11.000 you have consumed you have to 125 00:06:11.300 --> 00:06:14.000 pick that from the previous cells. 126 00:06:15.500 --> 00:06:18.100 Now you simply have to enter a formula which is 127 00:06:18.100 --> 00:06:21.700 a number of units you have consumed because you have sold 128 00:06:21.700 --> 00:06:21.800 them. 129 00:06:22.500 --> 00:06:26.000 The computer can calculate the inventories in 130 00:06:25.100 --> 00:06:29.300 terms of units at the end of the month, which is basically the 131 00:06:28.300 --> 00:06:32.300 beginning plus the purchases minus 132 00:06:31.300 --> 00:06:34.300 the conceptions it gives you how many 133 00:06:34.300 --> 00:06:36.300 units you have in your warehouse at the end. 134 00:06:38.200 --> 00:06:41.500 The good news again. Is that the computer the spreadsheet 135 00:06:41.500 --> 00:06:43.200 transforms that into values? 136 00:06:43.900 --> 00:06:46.600 But the cost of sales has to be entered as 137 00:06:46.600 --> 00:06:49.600 a formula very simply it 138 00:06:49.600 --> 00:06:52.400 is a cost of the consumption's so when 139 00:06:52.400 --> 00:06:56.000 you look at how much you consume in value in 140 00:06:55.800 --> 00:06:59.000 factories value out. It 141 00:06:58.200 --> 00:07:01.100 is a cost of sales because it is 142 00:07:01.100 --> 00:07:04.600 a cost of your consumption. Take care. This is a cost. So 143 00:07:04.600 --> 00:07:08.400 this is a negative figure pose and 144 00:07:07.400 --> 00:07:09.200 then we start again. 145 00:07:09.900 --> 00:07:13.200 Now we start again with the solution beginning 146 00:07:12.200 --> 00:07:16.900 inventory 450 you 147 00:07:16.900 --> 00:07:20.300 purchase 1,350 total 148 00:07:19.300 --> 00:07:24.700 available for sale 1800. How 149 00:07:24.700 --> 00:07:29.000 many units do you consume? Well 1400 700 150 00:07:27.200 --> 00:07:31.100 for B2B 700 for 151 00:07:31.100 --> 00:07:34.500 b2c at the end of the months. How many 152 00:07:34.500 --> 00:07:37.400 units are still in the warehouse? Well, basically how many 153 00:07:37.400 --> 00:07:40.600 units were available 1800 how 154 00:07:40.600 --> 00:07:44.100 many units do you consume 1,400 at 155 00:07:43.100 --> 00:07:46.400 least in theory? You should have 400 units 156 00:07:46.400 --> 00:07:47.400 in your Warehouse. 157 00:07:48.200 --> 00:07:52.000 Now you transform that into values you 158 00:07:51.500 --> 00:07:54.500 multiply each and every number of units 159 00:07:54.500 --> 00:07:57.500 by 20 dollars and you get exactly the 160 00:07:57.500 --> 00:07:59.500 same figure but multiplied by 20. 161 00:08:00.100 --> 00:08:04.600 Now what you consume which is a 1,400 units 162 00:08:04.600 --> 00:08:07.500 had a purchasing price in total of 163 00:08:07.500 --> 00:08:10.800 28,000. This is the cost of all these Goods 164 00:08:10.800 --> 00:08:13.300 which you have saw. So the cost of sales 165 00:08:13.300 --> 00:08:17.700 is not 28,000. It's minus 28,000 166 00:08:16.700 --> 00:08:19.400 because you remember that it is 167 00:08:19.400 --> 00:08:23.700 a cost and then the computer calculates immediately 168 00:08:22.700 --> 00:08:25.100 the gross margin, 169 00:08:25.100 --> 00:08:29.300 which is one again the other revenues Minus 170 00:08:28.300 --> 00:08:30.000 cost of sales. 171 00:08:30.800 --> 00:08:33.200 It's a third step is once you have 172 00:08:33.200 --> 00:08:36.900 calculated the gross margin through the computer. You have 173 00:08:36.900 --> 00:08:40.000 to introduce some figures for the indirect 174 00:08:39.000 --> 00:08:40.300 cost. 175 00:08:41.400 --> 00:08:44.500 You remember administration myself, but now I 176 00:08:44.500 --> 00:08:47.400 am helped by another person. You just 177 00:08:47.400 --> 00:08:50.800 have to introduce a number of managers, which is one myself 178 00:08:50.800 --> 00:08:53.200 and a number of administration person, which is 179 00:08:53.200 --> 00:08:56.700 one person you just hide for the support in 180 00:08:56.700 --> 00:08:58.000 the administration activity. 181 00:08:58.900 --> 00:09:01.800 What about selling expenses? Well you 182 00:09:01.800 --> 00:09:04.300 remember you had one salesperson and you are 183 00:09:04.300 --> 00:09:08.500 high and also one so you have now two people R&D 184 00:09:07.500 --> 00:09:10.600 exactly the same story. You just 185 00:09:10.600 --> 00:09:13.400 have to enter the figures and then what's going 186 00:09:13.400 --> 00:09:16.300 to happen is that the spreadsheet is going to take the cost of 187 00:09:16.300 --> 00:09:19.400 all these people and multiply the 188 00:09:19.400 --> 00:09:23.500 number of people by their cost. It's going to be the indirect 189 00:09:22.500 --> 00:09:26.000 overhead expenses. Not 190 00:09:25.300 --> 00:09:28.500 the spreadsheet calculate. It's a current operating profit, 191 00:09:28.500 --> 00:09:31.300 which was generated by your activity during the month 192 00:09:31.300 --> 00:09:34.100 of March pose. You do your job. 193 00:09:34.100 --> 00:09:35.000 We're back. 194 00:09:35.800 --> 00:09:38.500 The solution is we have a gross margin 195 00:09:38.500 --> 00:09:41.700 10,500 and we have plenty of 196 00:09:41.700 --> 00:09:44.300 additional overall expenses 197 00:09:44.300 --> 00:09:45.500 in direct cost. 198 00:09:46.500 --> 00:09:49.500 Now we have calculated the current operating profit for 199 00:09:49.500 --> 00:09:52.300 the period of March gross margin 200 00:09:52.300 --> 00:09:55.900 was calculated by the spreadsheet sales Minus 201 00:09:55.900 --> 00:09:56.600 cost of sales. 202 00:09:57.300 --> 00:10:00.800 Administrative expense one manager myself one 203 00:10:00.800 --> 00:10:03.700 Administration. Now, we have two salespeople 204 00:10:03.700 --> 00:10:06.600 and two engineers and it's 205 00:10:06.600 --> 00:10:10.000 calculated by the computer taking again from 206 00:10:09.200 --> 00:10:12.800 the assumptions the cost of these people the 207 00:10:12.800 --> 00:10:15.400 current operating profit for the period is 208 00:10:15.400 --> 00:10:18.200 gross margin minus indirect cost 209 00:10:18.200 --> 00:10:21.400 and it's 1000 900 now we 210 00:10:21.400 --> 00:10:24.400 can move to the four step which is going to be the easiest one 211 00:10:24.400 --> 00:10:27.500 because there is absolutely nothing to do. There's no 212 00:10:27.500 --> 00:10:30.800 exceptional event. No loss on inverter is 213 00:10:30.800 --> 00:10:34.700 no capital gain of any kind. So zero 214 00:10:33.700 --> 00:10:36.400 remains zero and the 215 00:10:36.400 --> 00:10:39.900 actual operating profit before tax is 216 00:10:39.900 --> 00:10:43.200 going to be the same at the current operating profit. 217 00:10:43.700 --> 00:10:46.900 Then you can move to step 5 now. Once 218 00:10:46.900 --> 00:10:49.600 you have observed that exceptional items accounts 219 00:10:49.600 --> 00:10:52.500 for Neil the earnings before tax. The 220 00:10:52.500 --> 00:10:56.000 taxable income is the same as a current operating profit. 221 00:10:56.700 --> 00:10:59.700 The computer will have to calculate some income tax 222 00:10:59.700 --> 00:11:02.400 but to calculate the income tax, you have to take the income tax 223 00:11:02.400 --> 00:11:05.500 rate, which you multiply by the earnings before 224 00:11:05.500 --> 00:11:08.200 tax. The one simple thing you have 225 00:11:08.200 --> 00:11:11.400 to do is introduce a formula in which 226 00:11:11.400 --> 00:11:15.000 you repeat the figure which is calculated as 227 00:11:14.900 --> 00:11:17.900 taxable income and then 228 00:11:17.900 --> 00:11:20.200 the computer will calculate the income tax 229 00:11:20.200 --> 00:11:21.700 and the net earnings. 230 00:11:22.800 --> 00:11:25.900 Price pose and do the job the solution 231 00:11:25.900 --> 00:11:28.700 is quite simple. You remember that. The current operating 232 00:11:28.700 --> 00:11:32.400 profit is 1900. You take 233 00:11:31.400 --> 00:11:34.200 this figure and you introduce that in 234 00:11:34.200 --> 00:11:37.600 the income tax calculation 1900 you 235 00:11:37.600 --> 00:11:40.700 pay 20% tomorrow your account 236 00:11:40.700 --> 00:11:43.300 today. So the income tax is 237 00:11:43.300 --> 00:11:46.200 380 the net earnings which are 238 00:11:46.200 --> 00:11:49.300 going to be 100% retained in the 239 00:11:49.300 --> 00:11:53.700 activity in the company is 1000 500 240 00:11:52.700 --> 00:11:55.800 and 20 now we 241 00:11:55.800 --> 00:11:58.300 have completed the p&l and we have just 242 00:11:58.300 --> 00:12:02.000 to make sure that what we decided in terms of dividend 243 00:12:01.400 --> 00:12:04.800 is what is in the spreadsheet we 244 00:12:04.800 --> 00:12:07.800 pay no dividend 0% of 245 00:12:07.800 --> 00:12:10.800 The netting of the period is going to be returned to 246 00:12:10.800 --> 00:12:13.800 shareholders and then the return earnings exactly 247 00:12:13.800 --> 00:12:15.400 match with an ad earnings. 248 00:12:16.300 --> 00:12:19.500 We moved to the second part which is a change in 249 00:12:19.500 --> 00:12:22.600 cash six step collecting cash 250 00:12:22.600 --> 00:12:26.300 from sales. Now. It's about accounts receivable 251 00:12:26.300 --> 00:12:29.900 because you remember that sales revenues its 252 00:12:29.900 --> 00:12:33.600 potential cash and then cash inflow 253 00:12:32.600 --> 00:12:35.700 will depend on the terms of 254 00:12:35.700 --> 00:12:37.500 payment for your customers. 255 00:12:38.600 --> 00:12:41.400 And the beginning of the months you have to 256 00:12:41.400 --> 00:12:44.300 show some accounts receivable, which is exactly the same 257 00:12:44.300 --> 00:12:47.900 as the end of February. The prior 258 00:12:47.900 --> 00:12:50.400 months was February. So a cancer 259 00:12:50.400 --> 00:12:53.400 civil beginning of March is equivalent to 260 00:12:53.400 --> 00:12:56.600 a cancer suitable and of February, you 261 00:12:56.600 --> 00:12:57.900 have to enter the formula. 262 00:12:58.600 --> 00:13:01.400 The second formula you have to enter is a sales 263 00:13:01.400 --> 00:13:04.400 figure which is in the same time. You just pick 264 00:13:04.400 --> 00:13:08.200 the figure where it is this cell equals 265 00:13:07.200 --> 00:13:09.400 another cell. 266 00:13:10.200 --> 00:13:13.600 The third formula which you have to enter is the 267 00:13:13.600 --> 00:13:16.300 accounts receivable at the end. And you remember 268 00:13:16.300 --> 00:13:20.700 that B2B customers are paying cash today b2c 269 00:13:19.700 --> 00:13:22.500 customers are paying in 270 00:13:22.500 --> 00:13:25.200 one month's time. So the accounts receivable at the end 271 00:13:25.200 --> 00:13:28.700 of March we'll be exactly is a b2c 272 00:13:28.700 --> 00:13:31.600 sales of March you enter the formula. 273 00:13:32.200 --> 00:13:35.700 Then the computer calculates by itself accounts receivable 274 00:13:35.700 --> 00:13:38.800 beginning of the period plus sales of 275 00:13:38.800 --> 00:13:41.500 the period minus accounts receivable 276 00:13:41.500 --> 00:13:44.700 and of the period is cash from 277 00:13:44.700 --> 00:13:45.100 sales. 278 00:13:46.100 --> 00:13:49.300 Take care. Even if you see a minus signing 279 00:13:49.300 --> 00:13:52.800 in front of accounts receivable, and the figure itself 280 00:13:52.800 --> 00:13:54.100 will be positive. 281 00:13:54.800 --> 00:13:57.700 What is minus will be the formula 282 00:13:57.700 --> 00:14:00.100 of the cash inflows? This is why you see 283 00:14:00.100 --> 00:14:03.400 plus plus minus equals. This 284 00:14:03.400 --> 00:14:05.800 is a formula in the cell for the computer. 285 00:14:07.300 --> 00:14:10.900 Now the last thing you have to do for step 6 is cash 286 00:14:10.900 --> 00:14:13.400 from sales and it is exactly the figure of 287 00:14:13.400 --> 00:14:16.900 the cash inflow in the accounts receivable 288 00:14:16.900 --> 00:14:17.900 Matrix. 289 00:14:19.000 --> 00:14:19.400 pose 290 00:14:20.300 --> 00:14:23.300 a word on the solution the accounts receivable 291 00:14:23.300 --> 00:14:27.100 at the beginning of March is end of February. 15,000 292 00:14:26.100 --> 00:14:29.800 sales figure is what you calculated a 293 00:14:29.800 --> 00:14:30.400 little bit earlier. 294 00:14:31.500 --> 00:14:34.400 At the end of the month what has not yet been 295 00:14:34.400 --> 00:14:38.000 paid by your customers is b2c sales 296 00:14:37.100 --> 00:14:40.600 17,500 now 297 00:14:40.600 --> 00:14:43.100 the spreadsheet calculates at in March, 298 00:14:43.100 --> 00:14:46.500 you're going to collect b2c sales of 299 00:14:46.500 --> 00:14:50.900 February 15,000 and the b2c sales 300 00:14:49.900 --> 00:14:53.100 of March 21,000. So 301 00:14:52.100 --> 00:14:55.600 some is 36,000. No big 302 00:14:55.600 --> 00:14:58.300 deal and it's cash from sales and it's a 303 00:14:58.300 --> 00:15:02.100 positive figure because of course. It's a cash in flow now 304 00:15:01.100 --> 00:15:04.700 cash out. It's about paying your 305 00:15:04.700 --> 00:15:06.300 beloved suppliers. 306 00:15:07.300 --> 00:15:11.200 Again, you have to enter a formula which is a 307 00:15:10.200 --> 00:15:13.700 chance payable beginning of the period beginning 308 00:15:13.700 --> 00:15:16.700 of March is end of February. It's always the 309 00:15:16.700 --> 00:15:19.400 same story by the way purchases. You remember 310 00:15:19.400 --> 00:15:23.700 that you purchase 1,350 units. 311 00:15:24.500 --> 00:15:27.400 At the price of $20 you have to find the 312 00:15:27.400 --> 00:15:30.100 figure somewhere in the spreadsheet and enter the formula. 313 00:15:31.300 --> 00:15:34.400 At the end of the month you remember that what is still 314 00:15:34.400 --> 00:15:37.700 do is 50% of the purchases. The 315 00:15:37.700 --> 00:15:40.600 50% is in a Cell somewhere. The purchases 316 00:15:40.600 --> 00:15:43.900 is in the cell somewhere and you have to calculate how 317 00:15:43.900 --> 00:15:46.700 much you are going to pay next month 318 00:15:46.700 --> 00:15:49.300 to your suppliers a computer calculate the 319 00:15:49.300 --> 00:15:49.600 outflow. 320 00:15:50.600 --> 00:15:53.400 And if you see plus plus minus it's because 321 00:15:53.400 --> 00:15:56.500 it is a formula of the cash out now. Take care 322 00:15:56.500 --> 00:15:59.600 because you will get a positive figure for 323 00:15:59.600 --> 00:16:02.500 the cash out, but you will have a negative figure 324 00:16:02.500 --> 00:16:05.200 for the cash outlay to suppliers because 325 00:16:05.200 --> 00:16:07.400 it's cash out minus. 326 00:16:08.200 --> 00:16:11.100 Those solution beginning of the 327 00:16:11.100 --> 00:16:16.000 month 15,000 purchases 1,350 times 328 00:16:15.700 --> 00:16:18.200 20 at the end 329 00:16:18.200 --> 00:16:21.700 of the month 50% are still payable to 330 00:16:21.700 --> 00:16:25.100 your suppliers, which means that you cashed 331 00:16:24.100 --> 00:16:27.600 out the remaining 50% of 332 00:16:27.600 --> 00:16:30.500 the purchases of February plus 50% of 333 00:16:30.500 --> 00:16:34.000 the purchases in March. It's 28,000 and 334 00:16:33.800 --> 00:16:36.900 500. The figure is positive in 335 00:16:36.900 --> 00:16:40.100 terms of accounts payable calculation, but 336 00:16:39.100 --> 00:16:42.400 it's negative in your cash flow 337 00:16:42.400 --> 00:16:47.600 statement because it is a cash out minus 28,500. 338 00:16:48.800 --> 00:16:52.400 The rest of the cash flow statement is not difficult because 339 00:16:51.400 --> 00:16:54.900 Administration is already calculated 340 00:16:54.900 --> 00:16:57.800 the salespeople. It's already calculated 341 00:16:57.800 --> 00:17:00.600 the R&D technical Engineers. It's already calculated 342 00:17:00.600 --> 00:17:03.500 then the question is how much do you pay for taxes? 343 00:17:03.500 --> 00:17:05.700 And how much do you pay for dividends? 344 00:17:06.400 --> 00:17:09.300 You remember that you pay your taxes in January next 345 00:17:09.300 --> 00:17:12.800 year. So the figure is going to be zero and as 346 00:17:12.800 --> 00:17:15.800 you have decided to retain 100% of 347 00:17:15.800 --> 00:17:18.600 the earnings and the dividend of the prior 348 00:17:18.600 --> 00:17:21.800 year has already been paid in generate. You 349 00:17:21.800 --> 00:17:24.900 understand that these two figures are going to be zero. You 350 00:17:24.900 --> 00:17:27.400 can do the job. I present post and 351 00:17:27.400 --> 00:17:28.200 back in a minute. 352 00:17:28.900 --> 00:17:31.200 Now you have a zero for taxes and 353 00:17:31.200 --> 00:17:34.700 the zero for dividend, but that's quite important because these 354 00:17:34.700 --> 00:17:38.100 two figures are going to be used in building the 355 00:17:37.100 --> 00:17:40.200 balance it in a few seconds. 356 00:17:41.300 --> 00:17:45.100 Now the cash at the end of the period is Step 357 00:17:44.100 --> 00:17:48.500 9 and this is already calculated you 358 00:17:47.500 --> 00:17:51.000 have the total cash outlet and 359 00:17:50.400 --> 00:17:53.400 you remember that you have calculated the cash 360 00:17:53.400 --> 00:17:58.800 inflows. So the difference is minus 1,100. 361 00:17:56.800 --> 00:17:59.300 It's quite 362 00:17:59.300 --> 00:18:02.800 interesting because you have observed that you have 363 00:18:02.800 --> 00:18:05.900 increase your revenues you have generated 364 00:18:05.900 --> 00:18:08.500 a profit but you have consumed some cash 365 00:18:08.500 --> 00:18:10.800 and we are going to discuss that in a few seconds. 366 00:18:11.600 --> 00:18:16.200 Cash at the beginning of the period was 14,620, by 367 00:18:15.200 --> 00:18:19.100 the way, it was a cash at the end of February. It 368 00:18:18.100 --> 00:18:21.400 is a cash at the beginning of March but the 369 00:18:21.400 --> 00:18:24.400 cash at the end of March is a cash at the 370 00:18:24.400 --> 00:18:27.300 beginning of the month minus the net change 371 00:18:27.300 --> 00:18:30.200 in cash because it's a negative figure and how much 372 00:18:30.200 --> 00:18:35.000 is left in our cash account? 13,000 520. No, 373 00:18:34.400 --> 00:18:37.700 we are at the end of the process which consists 374 00:18:37.700 --> 00:18:39.600 in building the balance shade. 375 00:18:40.100 --> 00:18:44.600 Step 10 assets inventories inventories. 376 00:18:43.600 --> 00:18:46.400 You have to enter the formula, but 377 00:18:46.400 --> 00:18:49.300 the formula is well, the cell is available 378 00:18:49.300 --> 00:18:52.900 somewhere in a tab. So you just have to say I take 379 00:18:52.900 --> 00:18:56.600 the value which is in the cell XYZ accounts 380 00:18:55.600 --> 00:18:58.500 receivable same story, of course 381 00:18:58.500 --> 00:19:02.300 in factories value and accounts receivable 382 00:19:02.300 --> 00:19:02.700 and 383 00:19:03.600 --> 00:19:06.700 And cash. Well, it has been calculated a 384 00:19:06.700 --> 00:19:09.700 few seconds ago because it is available in a 385 00:19:09.700 --> 00:19:12.400 change in the cash position of the company. Then 386 00:19:12.400 --> 00:19:15.200 the computer calculates the total asset which is 387 00:19:15.200 --> 00:19:19.400 a sum of these three figures pose solution 388 00:19:18.400 --> 00:19:21.900 inventories. We still 389 00:19:21.900 --> 00:19:24.300 have 400 units multiplied by 390 00:19:24.300 --> 00:19:28.000 20 accounts receivable. Basically, it's 391 00:19:27.300 --> 00:19:30.500 a b2c sales last but 392 00:19:30.500 --> 00:19:33.700 not least the cash figure has been calculated in Step 393 00:19:33.700 --> 00:19:36.400 9 to the total asset is 394 00:19:36.400 --> 00:19:39.700 13000 and 20, then you 395 00:19:39.700 --> 00:19:42.400 have to move to the 11th steps, which is 396 00:19:42.400 --> 00:19:44.200 equity and liabilities. 397 00:19:45.100 --> 00:19:48.200 Capital to start with no equity issue. So there is 398 00:19:48.200 --> 00:19:50.700 no reason why this figure might change. 399 00:19:51.400 --> 00:19:55.400 Retained earnings at the end of March. Well you 400 00:19:54.400 --> 00:19:57.700 remember that we have the retainer earnings 401 00:19:57.700 --> 00:20:00.600 calculated at the end of February. You have 402 00:20:00.600 --> 00:20:03.500 to pick the figure from the other Tab and 403 00:20:03.500 --> 00:20:06.700 you are going to add the March retained earnings 404 00:20:06.700 --> 00:20:09.400 good news is at the retainers are 405 00:20:09.400 --> 00:20:12.100 100% of the net earnings. So it's quite 406 00:20:12.100 --> 00:20:15.100 easy to find that figure in the current tab. 407 00:20:15.800 --> 00:20:19.600 Shareholders Equity is going to be calculated dividends 408 00:20:18.600 --> 00:20:21.100 payable is zero because 409 00:20:21.100 --> 00:20:24.800 you remember that when hand percent of the net earnings are retained. It 410 00:20:24.800 --> 00:20:27.500 was the case for Generate and February and 411 00:20:27.500 --> 00:20:27.900 March. 412 00:20:28.800 --> 00:20:31.200 A little bit more difficult is the income tax 413 00:20:31.200 --> 00:20:34.400 payable as an operating liability. You remember 414 00:20:34.400 --> 00:20:37.300 you have accumulated some tax payable at 415 00:20:37.300 --> 00:20:40.700 the end of February. It was due to the profit generated 416 00:20:40.700 --> 00:20:43.600 in January and in February and as 417 00:20:43.600 --> 00:20:46.700 it is going to be paid next year. It is 418 00:20:46.700 --> 00:20:49.500 an operating liability in your balance sheet. 419 00:20:49.500 --> 00:20:52.800 You're liable. It is a kind of tax payable. 420 00:20:52.800 --> 00:20:55.400 Then you have to deduct from the 421 00:20:55.400 --> 00:20:58.600 figure and of February is a tax, which is bad in March. 422 00:20:58.600 --> 00:21:01.500 The good news is that it's zero because it's going to be pet next 423 00:21:01.500 --> 00:21:04.300 year, but then you have to add the income tax 424 00:21:04.300 --> 00:21:07.500 which is generated by the profit of the month 425 00:21:07.500 --> 00:21:10.400 of March. So you pick the 426 00:21:10.400 --> 00:21:13.600 figure from the February tab minus tax 427 00:21:13.600 --> 00:21:17.200 PED in March. It is in the cash budget plus 428 00:21:16.200 --> 00:21:19.800 income tax generated in March. It 429 00:21:19.800 --> 00:21:22.300 is somewhere calculated in the spreadsheet. 430 00:21:22.900 --> 00:21:23.500 pose 431 00:21:24.400 --> 00:21:28.500 Again for Capital no equity issue, 10,000 retained 432 00:21:27.500 --> 00:21:30.600 earnings and of February plus 433 00:21:30.600 --> 00:21:33.600 March retain earnings or plus March 434 00:21:33.600 --> 00:21:36.200 earnings because you're retain 100% of the 435 00:21:36.200 --> 00:21:36.800 net earnings. 436 00:21:37.400 --> 00:21:40.800 Shareholders Equity dividends payable nothing accounts 437 00:21:40.800 --> 00:21:43.900 payable at the end. It's 50% of your purchases. It 438 00:21:43.900 --> 00:21:46.500 was calculated in the same Tab and the 439 00:21:46.500 --> 00:21:50.600 income tax payable is now 1,360 the 440 00:21:49.600 --> 00:21:52.400 computer and the spreadsheet 441 00:21:52.400 --> 00:21:55.600 makes the calculation the great news that you 442 00:21:55.600 --> 00:21:59.300 have 39,000 and 20, then 443 00:21:58.300 --> 00:22:01.300 you can get to step 12 which is 444 00:22:01.300 --> 00:22:04.600 relief. In fact in the step 12. You just 445 00:22:04.600 --> 00:22:07.600 have to observe the mechanical balance in 446 00:22:07.600 --> 00:22:10.600 the accounts between on the one hand the assets on 447 00:22:10.600 --> 00:22:13.300 the other hand the equity and liabilities, but 448 00:22:13.300 --> 00:22:15.500 it's quite good news and they balance 449 00:22:16.200 --> 00:22:19.600 Now congratulations, you did the job for March, of 450 00:22:19.600 --> 00:22:22.900 course in real life. It is a little bit more complex 451 00:22:22.900 --> 00:22:25.400 than that. But what was interesting is that 452 00:22:25.400 --> 00:22:28.300 you could observe because you did it by yourself 453 00:22:28.300 --> 00:22:31.900 the main steps of building the p&l. 454 00:22:32.900 --> 00:22:35.700 The cash flow and the balance it 455 00:22:35.700 --> 00:22:38.400 and it is always the same process even though 456 00:22:38.400 --> 00:22:41.000 obviously in real life. It's slightly more 457 00:22:41.600 --> 00:22:44.900 complex, then we can move to the financial analysis 458 00:22:44.900 --> 00:22:47.700 in March you remember we always proceed 459 00:22:47.700 --> 00:22:50.500 the same way sales profit and 460 00:22:50.500 --> 00:22:50.900 cash. 461 00:22:52.100 --> 00:22:55.300 Sales and revenues good news. Why because 462 00:22:55.300 --> 00:22:58.300 after the decrease the decline in 463 00:22:58.300 --> 00:22:59.200 sales of January. 464 00:22:59.700 --> 00:23:03.600 Beginning of the year after Christmas then sales 465 00:23:02.600 --> 00:23:05.200 in February went up and 466 00:23:05.200 --> 00:23:08.400 sells in March went up. So the revenues are 467 00:23:08.400 --> 00:23:12.500 up which is quite good news. We create value. What 468 00:23:11.500 --> 00:23:14.300 about the margins? What about 469 00:23:14.300 --> 00:23:17.400 the profit which is a gross margin is quite stable because you remember 470 00:23:17.400 --> 00:23:20.100 it's always a matter between b2c and 471 00:23:20.100 --> 00:23:23.700 B2B sales as a mix is quite the 472 00:23:23.700 --> 00:23:26.200 same. There's no reason why the gross margin 473 00:23:26.200 --> 00:23:29.400 would dramatically change. What is quite interesting 474 00:23:29.400 --> 00:23:32.400 to observe is the operating margin because you 475 00:23:32.400 --> 00:23:35.600 could say are revenues are so we are going to generate economies 476 00:23:35.600 --> 00:23:38.700 of scale which is absolutely true. But you 477 00:23:38.700 --> 00:23:41.700 generate economies of scale when fixed costs 478 00:23:41.700 --> 00:23:44.300 are fixed and he ends this 479 00:23:44.300 --> 00:23:47.400 case goes or not fixed. Why because you have hard 480 00:23:47.400 --> 00:23:50.600 three people you have to pay the salary 481 00:23:50.600 --> 00:23:53.700 so you have incremented your fixed cost but basically 482 00:23:53.700 --> 00:23:56.500 some costs are real cause 483 00:23:56.500 --> 00:23:59.600 such as Administration, but if you hide 484 00:23:59.700 --> 00:24:02.600 Somebody in sales activity and somebody in 485 00:24:02.600 --> 00:24:06.400 R&D you're in fact making an investment and 486 00:24:05.400 --> 00:24:08.600 this investment shows as a 487 00:24:08.600 --> 00:24:09.900 cause in a p&l. 488 00:24:10.400 --> 00:24:14.800 The investment is to support R&D supporting 489 00:24:13.800 --> 00:24:16.300 sales is just getting 490 00:24:16.300 --> 00:24:19.600 along with increasing Revenue. They're really 491 00:24:19.600 --> 00:24:22.600 investment in that case is research and development. 492 00:24:24.200 --> 00:24:27.500 Now after sales profit, we can 493 00:24:27.500 --> 00:24:30.200 move to cash and cash is very much about 494 00:24:30.200 --> 00:24:33.700 the profit you make minus increase 495 00:24:33.700 --> 00:24:36.500 in the working capital requirement also decrease 496 00:24:36.500 --> 00:24:39.000 in the operating working capital requirement. But in that 497 00:24:39.300 --> 00:24:42.200 case you're growing and the consequence of 498 00:24:42.200 --> 00:24:45.300 growing is at a cancer symbol are quite up 499 00:24:45.300 --> 00:24:48.700 as a consequence. The operating working capital requirement 500 00:24:48.700 --> 00:24:50.800 is by 3000. 501 00:24:51.700 --> 00:24:54.100 This is why when you calculate the funds from 502 00:24:54.100 --> 00:24:57.800 operations you remember it's a current operating profit minus 503 00:24:57.800 --> 00:25:00.700 the current change in operating working capital requirement. 504 00:25:00.700 --> 00:25:04.000 In fact, it's operating profit miners change 505 00:25:03.100 --> 00:25:07.000 in operating working capital requirement because current is 506 00:25:06.400 --> 00:25:10.000 the same as Global no exceptional item, 507 00:25:09.600 --> 00:25:13.600 but how much profit do you generate 1,900 508 00:25:12.600 --> 00:25:16.300 how much cash do you consume in 509 00:25:15.300 --> 00:25:18.800 terms of increase in working capital requirements 510 00:25:18.800 --> 00:25:21.500 3,000 so the growth the 511 00:25:21.500 --> 00:25:25.100 increase into operating working capital requirement as 512 00:25:24.100 --> 00:25:27.200 more than consume the 513 00:25:27.200 --> 00:25:30.400 operating margins. This is why the current funds from 514 00:25:30.400 --> 00:25:34.900 operations is a negative figure by 1000 one-handed. 515 00:25:35.500 --> 00:25:39.000 So it's very interesting to observe that revenues are 516 00:25:38.300 --> 00:25:41.300 up profit is up and 517 00:25:41.300 --> 00:25:44.200 cash is down why because of growth. 518 00:25:44.700 --> 00:25:48.000 Now let's conclude with some knowledge and wrap-up 519 00:25:47.000 --> 00:25:48.700 statements. 520 00:25:49.500 --> 00:25:53.200 First you understood that building financial statements 521 00:25:52.200 --> 00:25:56.000 is a very mechanical process. 522 00:25:55.800 --> 00:25:58.800 You do it step by step 523 00:25:58.800 --> 00:26:02.200 and it's always the same sequence. The principles 524 00:26:01.200 --> 00:26:05.600 are quite simple revenues cars 525 00:26:05.600 --> 00:26:08.400 inventories receivables payables and so 526 00:26:08.400 --> 00:26:11.400 on and so forth now as a principles are 527 00:26:11.400 --> 00:26:14.200 simple, but you need a little bit of practice in order 528 00:26:14.200 --> 00:26:17.700 to be completely fluent in accounting. You 529 00:26:17.700 --> 00:26:21.200 need to grow your skills and you need to practice quite 530 00:26:20.200 --> 00:26:21.600 a lot. 531 00:26:22.700 --> 00:26:26.100 What is interesting is the commercial success 532 00:26:25.100 --> 00:26:28.400 of your company, which is about 533 00:26:28.400 --> 00:26:32.300 creating customer value implies generates 534 00:26:31.300 --> 00:26:34.400 some growth in sales and grows in 535 00:26:34.400 --> 00:26:37.900 revenues, but grows is consuming Financial 536 00:26:37.900 --> 00:26:41.600 Resources for two reasons first operating 537 00:26:40.600 --> 00:26:43.500 working capital requirements. The 538 00:26:43.500 --> 00:26:46.800 operating cycle is growing with your revenues and 539 00:26:46.800 --> 00:26:50.000 each and every door, which is growing the 540 00:26:49.500 --> 00:26:53.100 working capital requirement is consumed now. 541 00:26:52.100 --> 00:26:55.800 Also when you are growing there are 542 00:26:55.800 --> 00:26:58.300 some expenses which are related with 543 00:26:58.300 --> 00:27:01.800 revenues such as marketing or related 544 00:27:01.800 --> 00:27:04.800 with future revenues such as research on 545 00:27:04.800 --> 00:27:07.700 development, which is a true investment. And 546 00:27:07.700 --> 00:27:11.200 this is why some costs are consumptions. Some 547 00:27:10.200 --> 00:27:14.000 other costs are real Investments 548 00:27:13.100 --> 00:27:16.700 for the future, even though they show 549 00:27:16.700 --> 00:27:19.400 in the profit and loss statement of 550 00:27:19.400 --> 00:27:20.100 the period 551 00:27:20.800 --> 00:27:24.100 Now a few statements to conclude this second module 552 00:27:23.100 --> 00:27:27.000 what is financial accounting 553 00:27:26.000 --> 00:27:27.300 about? 554 00:27:28.100 --> 00:27:30.100 It's about recording transactions. 555 00:27:31.200 --> 00:27:35.100 But why do you recall this transactions which were realized by 556 00:27:35.100 --> 00:27:39.200 the corporation? It's because you need to have structured and 557 00:27:38.200 --> 00:27:41.500 usable and valid and 558 00:27:41.500 --> 00:27:43.200 relevant information. 559 00:27:44.200 --> 00:27:47.900 That's absolutely fundamental. If you don't properly 560 00:27:47.900 --> 00:27:50.100 recall the transactions, you don't have 561 00:27:50.100 --> 00:27:54.100 the information about what is happening in the business operations 562 00:27:53.100 --> 00:27:56.400 of the company then starting from 563 00:27:56.400 --> 00:27:59.700 this information. You can integrate the 564 00:27:59.700 --> 00:28:03.200 accounting statements and financial analysis. You 565 00:28:02.200 --> 00:28:05.700 can interpret the figures but to 566 00:28:05.700 --> 00:28:09.000 interpret the figures you need to have reliable relevant and 567 00:28:08.600 --> 00:28:12.100 valid information and financial 568 00:28:11.100 --> 00:28:15.100 analysis is not just about observing the 569 00:28:14.100 --> 00:28:17.700 account or it's up or it's 570 00:28:17.700 --> 00:28:21.200 down now it's about intelligent observation of 571 00:28:20.200 --> 00:28:24.500 the reality financial analysis 572 00:28:23.500 --> 00:28:27.400 is about transforming figures 573 00:28:26.400 --> 00:28:29.900 into interpretation in a 574 00:28:29.900 --> 00:28:30.600 clever way. 575 00:28:31.700 --> 00:28:34.300 Now module 2 is over module 3 576 00:28:34.300 --> 00:28:37.800 is going to be about the investment. We have prepared the 577 00:28:37.800 --> 00:28:40.700 investment now, we are going to implement it and 578 00:28:40.700 --> 00:28:44.100 we are going to purchase a machine a manufacturing equipment 579 00:28:43.100 --> 00:28:46.600 so that we can insource and 580 00:28:46.600 --> 00:28:49.300 Manufacturing and control the production process. 581 00:28:50.500 --> 00:28:51.100 Thank you very much.