Coffee Washing Station Financial Analysis Model

You don't have the latest version of Adobe Flash Player.

Please update your flash player.

Please login or register to make a comment!

...Description...... more. less.

operation. The information generated by the model will allow CWS management to make informed decisions on such matters as whether they can afford to buy a new piece of equipment or hire additional laborers. In addition, the model will allow management to calculate how much cash will be available for repaying loans or investing in capacity expansion. Only by using financial projections created by this model will you be able to estimate how much money will be required to operate the CWS over the next several years and plan your finances accordingly. 3 Secondly, an Excel financial model allows CWS management to update financial projections when coffee prices or the cost of production changes. The coffee market is very dynamic and the price paid for a pound of coffee is constantly fluctuating. In addition, other factors may change regularly, such as the cost of electricity, oil or labor. Without an Excel model, it is difficult to determine exactly how a change in coffee prices or costs will affect your ability to repay a loan or pay your workers. A traditional business plan becomes outdated and is no longer useful when prices change. With an Excel financial model, you can easily update all your financial projections by simply changing one number in the model. All the financial projections and analysis will automatically update themselves. It 9s that easy! There is no need to perform tedious calculations over and over again, you type in one number and the model does all the work for you! Finally, using an Excel financial model will allow bankers to analyze a particular project by calculating the DCF value, internal rate of return (IRR) and common financial ratios. This will allow bankers to easily determine if a project will be profitable and help them make decisions on which projects should receive a loan. Structure of this Manual This manual assumes a basic working knowledge of Excel, including how to find cells based on their coordinates; enter a number into a cell; change worksheets and copy cells. Please consult a beginning Excel book if you have difficulties performing these tasks. 4 The manual is divided into three sections. The first section will explain how to enter data into the model to customize it for your particular project. A second section explains the financial statements and analysis that are generated by these inputs. Finally, the last section contains a number of exercises that will allow you to test your skills in using the model and your understanding the financial statements that are created. 5 ENTERING DATA An Excel financial model is a powerful business tool because it allows you to create financial projections that are customized to your specific project. Every CWS built in Rwanda is different 3 different capacities, power sources, transport costs, etc. Therefore, each CWS business plan must take these differences into account. This section will explain exactly how to customize the model to your specific situation. The five worksheets after the Instructions worksheet are used for entering data. The rest of the worksheets are automatically calculated based on the data entered in these first five sheets. This section will explain in detail how to enter data into the model The first step is to open the financial model. Excel may ask you if you want to enable or disable macros. Choose the cEnable Macros d option. The first thing you may notice when you open the CWS financial model is that each number is one of three different colors: blue, red or black. In addition, some squares are yellow, while the rest are clear. The color of each cell is determined by its function. The meaning of the colors is explained in the table below: Blue Blue numbers are inputs. These numbers should be changed to the appropriate value for the CWS you are analyzing . Red Red numbers should only be changed by an Excel expert. Black Black numbers are automatically calculated by the model. You will not be able to alter these cells. Yellow Background Numbers with a yellow background mean that a correct value has not yet been entered. When you enter a value appropriate for your CWS, the yellow background will disappear. THE MODEL IS NOT READY UNTIL THERE ARE NO YELLOW SQUARES LEFT IN THE MODEL. 6 Main Inputs Open the model to the third worksheet, entitled Main Inputs . In cell B6, you will find a cell that contains blue text with a yellow background. The current value entered in to the cell is c1 d. Enter the capacity of your planned CWS into this cell. For example, if the planned CWS will have a capacity of 200 tons of parchment coffee / year, enter the number c200 d into the cell. Notice that when you enter c200 d the yellow background disappears. This indicates that you have successfully completed the task of entering data into this cell, and you can move to the next yellow box. Also, notice that the cell has blue text. As stated in the table above, this means that this cell contains an input and can be changed. For example, if you change your mind and decide to build a CWS with a capacity of 300 tons, simply change the c200 d in cell B6 to c300. d Directly below, in cell B7, is a box where you can choose the source of energy that will be used to power the CWS. Using your mouse, click on the downward pointing arrow on the right side of the cell. When you do this, four choices will appear: cDiesel Generator, d cElectric Grid, d cHydroelectric, d or cManual. d Use the mouse to select the answer appropriate to your CWS, and you will notice that this selection appears in cell B7. Use the same technique in cell B8 to select the type of business plan the CSW will be using. This financial model can analyze two types of CWS businesses. These two choices are explained in the table below: Sell Parchment Coffee to Mill The CWS operator will sell the parchment coffee it produces to a miller / exporter. The CWS will not pay for milling or act as an exporter. Export Green Coffee The CWS operator will pay to have his coffee milled or mill it himself and then sell the resulting green coffee to a foreign buyer, acting as his own exporter. 7 After choosing the type of business plan, move down to cells B11-17. Notice that each cell has a yellow background and therefore requires data to be entered. The data required for these cells is self-explanatory. Continue down the page and enter the % of the capacity of the CWS that will be utilized each year in the appropriate yellow boxes in row 20. Notice that below, in rows 21-23, the model will automatically calculate how many tons of cherries need to be purchased and how much parchment and green coffee will be produced. As stated above, these cells with black text are automatically calculated and DATA SHOULD NEVER BE ENTERED INTO BLACK CELLS. Continue down the page, entering the percentage of coffee produced that will be FW-AA quality and FW-AB quality. The amount of FW-C coffee produced will be automatically calculated. These classifications will be used to determine the price received for each grade of coffee. If your CWS will not be sorting coffee into different grades, simply enter c0 d for FW-AA and FW-AB and assume that all of the coffee will be sold as FW-C grade. At the bottom of this first worksheet are cells that contain red text. These cells control the choices available in the model. Remember, RED CELLS SHOULD ONLY BE CHANGED BY AN EXCEL EXPERT. Examine this worksheet to be sure that the correct data has been entered into all the yellow cells. If any yellow cells remain, enter the correct data into that cell. ONLY WHEN THERE ARE NO YELLOW CELLS REMAINING ON THE PAGE SHOULD YOU MOVE TO THE NEXT WORKSHEET, ENTITLED INVESTMENT COSTS . 8 Investment Costs The first step on this page is to go to cell B5 and select whether you would like to enter the cost of constructing the CWS in cDetailed d or cSummary Only d mode. cDetailed d mode means that you will enter the cost of each separate component that is required for building your CWS. In cSummary Only d mode, you will only enter the total cost of broad categories, such as cConstruction d or cProduction Equipment. d Once you have chosen how you will enter the costs, input the appropriate data in any yellow cell that appears. If your CWS does not require a particular item listed, enter c0 d for the cost. Notice that when you scroll over some cells, a hint or warning will appear. For example, move to cell B38. A hint will pop up, telling you cEnter zero if not using hydroelectric power. d Watch for these hints throughout the model. Notice the red cells labeled cOther. d These cells may be used if the list does not contain an item necessary to the construction of your CWS. You may replace the cOther d cell with the name of the item you wish to add, and enter the price of that item in the red cell to the right. The model will automatically include the cost of this item in its calculations. When you have completed entering all the appropriate prices, move to cell B73 and enter the % of the total cost that will be necessary for unforeseen expenditures. In addition, all projects will require ongoing capital expenditures each year for repairs, replacements, etc. Enter the average amount that you expect to spend each year on maintenance capital expenditure in cell B74. 9 Before moving on to the next worksheet, scroll around to make sure that there are no yellow cells remaining. ONLY WHEN DATA HAS BEEN ENTERED INTO ALL THE YELLOW CELLS SHOULD YOU MOVE TO THE NEXT WORKSHEET. Operating Inputs This worksheet allows you to determine the costs of operating your CWS. The model divides operating expenses into two main categories: Fixed Costs and Direct Costs. Fixed Costs are, well, fixed, while Direct Costs will vary based on how much parchment or green coffee is produced. Let 9s begin at the top with the Fixed Costs section. Fixed Costs: The different types of employees are listed in column A on the left hand side. In column B, enter the expected salary per month for each position. If you do not plan on hiring someone for this type of position, enter c0 d for the salary. In column D, enter the number of cman-months d expected to be worked at this position each year. For example, if you plan on hiring one mechanic who will work all year, enter c12 d in cell D8. If you plan on hiring two night guards who will work 5 months a year each, enter c10 d (2 workers x 5 months = 10 cman-months d) in cell D16. As on the previous worksheet, if you are comfortable with Excel you may add new types of employees using the red cOther d cells. Do not enter general labor in this section, as its cost will be calculated in the Direct Costs section at the bottom of the page. Notice that the total annual cost in both FRW and US$ is automatically calculated. 10 Direct Costs: The Direct Costs section is calculated in a different manner. Column A lists items that are consumed in the operation of a CWS and the applicable units for each item are listed in parentheses. In column B, enter the cost of one unit of each item. If you do not plan on using an item, enter c0 d for the cost. Additionally, enter c0 d for the cost if the item listed in Column A is free for you (e.g. water is free for most CWS, electricity is free if you 9ve installed your own hydroelectric generator). Also, if you are not planning to act as your own exporter, you should enter c0 d for the four items under cMilling & Export Costs, d as the exporter will pay for those items. ONLY ENTER THESE COSTS IF YOU WILL BE ACTING AS YOUR OWN EXPORTER . Definitions of these three items can be found in the table below: OCIR Café Payments Payments made to OCIR Café by the exporter. Enter these payments as a % of green coffee revenue in cell B51. Milling Cost If you are running your own milling equipment, this is the cost of operating your equipment per ton of green coffee produced. If not, this is the amount charged by an outside mill per ton of green coffee produced. Marketing Expense Marketing costs per ton of green coffee. Transport to Boat and FOB Costs Total cost per ton to transport green coffee to onboard a ship (or to any other sale point), including land transport, loading, and bill of lading costs Many of the cells in this section contain hints or warnings. Move the cursor to each cell to view information on what data should be entered. At the bottom of the page, additional information is required in order to allow the model to calculate the quantity used of each item listed in the Direct Costs section. Most of these items ask you to enter the quantity of an item used per ton of parchment coffee 11 produced. For example, in cell B45 you are asked to enter the number of cman-days d of manual labor required to produce one ton of parchment coffee. A quick example will help you understand exactly how to calculate the number to be entered in this cell. Suppose you plan on running your CWS for 5 months a year and for 25 days each month. A quick calculation determines that you plan on running your CWS for 125 days this year. Then, imagine that each day the CWS is operating there will be 50 general laborers working. The total number of cman-days d worked that season will be 125 days x 50 people = 6,250 cman-days. d Finally, imagine that you plan on producing 200 tons of parchment coffee that season. If you divide the total number of cman-days d by the number of tons of parchment coffee produced (6,250 cman-days d / 200 tons of parchment coffee) you get an answer of 31.25. This is the number of cman-days d necessary to produce one ton of parchment coffee. Enter the result in cell B45. Similar calculations can be done to determine the liters of oil, cubic meters of water, number of sacs, etc. used per ton of parchment coffee produced. Enter those results in the appropriate cells in column B at the bottom of the page. Notice that the total quantity of each item used is automatically calculated above in the Direct Costs section. Notice that there is a space at the bottom for entering other costs that may occur. Only add additional items if you are an Excel expert, as you must write complicated formulas in order for the model to add these items properly. When you have completed entering data in each cell and there are no yellow boxes left on the page, move on to the next worksheet. 12 Cherry and Green Coffee Prices On the Main Inputs worksheet, you entered the percentage of green coffee produced by your CWS that would be rated FW-AA, FW-AB and FW-C. The current worksheet allows you to enter the FOB price that you expect to receive for each grade of coffee. Even if you will not be acting as an exporter, you must enter the price that you expect the exporter to receive for each coffee grade. However, if you entered on the first page that you do not expect to produce any coffee of a certain grade, you do not need to enter a price for that grade. For example, if you entered on the Main Inputs worksheet that you do not expect to produce any FW-AA grade coffee in 2004, you do not need to enter a market price for FW-AA grade coffee for that year. Cells in which you do not need to enter a price will be black and crossed out. This worksheet also allows you to enter the expected purchase price of coffee cherries from farmers. Include in this price any transportation costs that the CWS incurs in the purchase of these cherries. Be sure to take inflation into account when projecting future prices for the cherries. The Excel model allows you to create three different coffee price scenarios: cPessimistic, d cNormal d and cOptimistic. d You can then create separate sets of projections and financial analysis based on each different scenario. When you enter coffee prices for each scenario, make sure that they are consistent across coffee grades and cherry purchase prices. If you chose the option on the Main Inputs worksheet in which the CWS station will not be acting as an exporter, you must choose the green coffee revenue split between the exporter and the CWS for each year of operation. For example, if you expect the 13 exporter to keep 40% of the green coffee FOB sale price and pay 60% to the CWS for the parchment coffee, enter 40% in cell B36. If you indicated on the Main Inputs worksheet that you would be acting as your own exporter, this section will appear crossed out. YOU DO NOT NEED TO ENTER ANY INFORMATION IN THIS SECTION IF YOU WILL BE ACTING AS YOUR OWN EXPORTER. There is a button in column A between rows 39-41 labeled cUpdate Breakeven Prices. d Pressing this button will update the weighted average price in $/kg that you must sell your green coffee cherries each year in order to break even in net profit. This button takes all costs into account, therefore THE BREAKEVEN CALCULATION WILL ONLY GIVE ACCURATE RESULTS ONCE YOU HAVE ENTERED DATA FOR ALL COSTS, INCLUDING THE PURCHASE PRICE OF CHERRIES. If you change any inputs affecting costs, you must press the cUpdate Breakeven Prices d button again in order to update the table. The last item of interest on this worksheet is cell B6, which allows you to choose which price scenario the model will use to make all of its calculations. If you choose cNormal, d the model will use all the prices entered in the rows labeled cNormal. d If you choose cPessimistic, d the model will use the rows labeled cPessimistic, d etc. Every worksheet that depends on this selection will state at the top which scenario is currently in use. You may come back to this worksheet and change which scenario the model uses at anytime. Once you have chosen, move on to the next worksheet. 14 Sources and Uses The Sources and Uses worksheet allows you to determine where management will get the necessary funds to construct the CWS and fund the initial working capital requirements. All the costs, including those entered on the Investment Costs worksheet, are listed in column B under cUses. d The working capital requirement for the first year, automatically calculated by the model, is also listed in the cUses d column. Potential sources of funds are listed in columns C-F. The four potential sources are described in the following table: Own Funds Cash that the CWS owners contribute to fund the construction or operation of the CWS. Grant Money Money provided by an outside source for the construction or operation of the CWS does that not have to be paid back. Term Debt A bank loan that has a fixed, constant repayment schedule. Revolving Debt A line of credit in which the CWS can borrow or repay money to the bank at anytime. You must enter the amount of Own Funds, Grant Money and Term Debt that will be used to finance each item. Sources must always exactly equal the uses for each item (i.e. if cConstruction d will cost FRW 40,000,000, the sum of Own Funds, Grant Money, Term Debt and Revolving Debt applied to Construction must equal FRW 40,000,000). Therefore, the amount of Revolving Debt will be automatically calculated for each item to insure that sources and uses always balance. A positive number in the Revolving Debt column indicates that money has been borrowed from the revolving line of credit to finance the construction of that item. A negative number means that there is already more than enough money to fund this item from the other funding sources and that the leftover funds will be contributed as cash to be used in the operation of the CWS. 15 The bottom of this page contains cells for inputting the interest rate charged for the Term Debt and Revolving Debt and the schedule for paying back the Term Debt. The Grace Period is the number of years in which the CWS is not required to make principal payments on the Term Debt, only interest payments. There is also a cell for entering the number of years in which the Term Debt loan is required to be repaid. When you are finished completing this worksheet, you 9re done! The model automatically generates every other worksheet; no further input is required! However, remember that every section of the model is linked to every other section. Therefore, if you change the cost of construction, or the type of energy used, or any other input, you must check every input in the model to make sure that it is still correct. For example, if you change the construction cost on the Investment Costs worksheet, you will have to update the Sources and Uses worksheet, as the numbers will have changed. If you change the energy source, you must update the Operating Inputs worksheet as the station will now require different inputs (e.g. oil instead of electricity) to operating the CWS. THEREFORE, TAKE ONE LAST LOOK THROUGH THE FIVE INPUT WORKSHEETS BEFORE MOVING ON TO MAKE SURE THAT EVERYTHING IS STILL CORRECT. When you 9ve made one final check, turn to the second section of this manual, Financial Projections and Analysis, which will explain the outputs on the automatically generated worksheets. 16 Financial Projections and Analysis Congratulations, you have now successfully customized the model for your CWS. This section will explain the financial projections and analyses that are automatically generated based on your inputs. The model has now created projections for the profitability of your CWS for the next 10 years. It also has completed an analysis of your ability to repay your debt, obtain the necessary working capital to run the business and generate cash for the CWS owners. Finally, the model has generated sophisticated project analyses that will allow you to determine the theoretical value of your CWS and the rate of return that you can expect from your investment. Operating Cost Summary Based on the data provided on the Operating Inputs worksheet, the model automatically creates projections for the total CWS operating costs for a ten-year period. Again, the costs are divided into two main categories: Fixed Costs and Direct Costs. The Fixed Costs do not vary with the amount of parchment coffee produced, while the Direct Costs depend on the level of coffee production. All of these costs are expressed in FRW and therefore increase from year to year based on the level of inflation entered on the Main Inputs worksheet. Once again, there are spaces marked cOther, d reserved for costs that are not currently listed, but you must be an Excel expert to write the necessary formulas for including these new items in the calculations. In addition, there are line items labeled cNew Fixed Costs d and cNew Direct Costs d. While these numbers will not update automatically, you can type in additional fixed or direct costs in the corresponding red cells if you want an easy way of adding costs not included elsewhere in the model. 17 Income Statement An income statement is a financial document that lists all the revenues earned and costs incurred while running a business. After determining the revenues and costs, the income statement calculates how much profit or loss is generated each year. Let 9s start at the top of the income statement and explain each item that appears. Revenue: The revenue section lists the amount of green coffee sold, the price received, and the total revenue earned for each grade of green coffee produced. It then adds up the revenue received for each grade to calculate the total revenue earned by the CWS for each year. Notice that the selling price in FRW has been adjusted for inflation based on your entry on the Main Inputs worksheet. Costs: Costs are divided into four main categories, listed in the table below: Cost of Goods Sold (Cost of Cherries) The cost of items that must be purchased in order to produce your product. In the case of a coffee washing station, this is equal to the cost of the cherries purchased. Operating Costs The cost of running in the business, including salaries, gas, electricity, water, etc. In this model, operating costs are labeled as either Fixed Costs or Direct Costs. Depreciation Depreciation is the loss in value of your equipment as it ages. Interest and Taxes Interest paid on outstanding loans and taxes paid to the government. 18 The income statement calculates the profit remaining after each of these costs has been taken into account. The table below defines the four measures of profit used in this model: Gross Profit Revenue 3 cost of cherries Operating Profit Revenue 3 cost of cherries 3 operating costs Profit before interest and taxes Revenue 3 cost of cherries 3 operating costs 3 depreciation Net profit Revenue 3 cost of cherries 3 operating costs 3 depreciation 3 interest expense - taxes The income statement generated by the financial model automatically calculates each of these measures of profit and projects them for ten years. Balance Sheet A balance sheet is a statement of the assets and liabilities of a business at a given point in time. The balance sheet created by this model is a statement of the CWS 9s financial position at the end of each year. A balance sheet is useful because it lays out on one small worksheet the overall financial position of the CWS. How much cash do I have to buy cherries and pay my workers next year? How much money do I owe to the bank? What is the current value of my equipment? All of these questions can be answered with a quick look at the balance sheet. The definition of the exact terms used in the balance sheet can be found in the table below: 19 Cash Value of cash held by CWS Property & Equipment Current value of equipment owned by CWS. This is equal to the amount paid for equipment minus accumulated depreciation Total Assets Cash + Property & Equipment Revolving Debt Outstanding principal of Revolving Debt Term Debt Outstanding principal of Term Debt Total Liabilities Revolving Debt + Term Debt Paid in Capital Amount of cash contributed by owners to fund the CWS Retained Earnings Total Net Profit earned over the life of the CWS Total Liabilities & Equity Total Liabilities + Paid in Capital + Retained Earnings Cash Flow The difference between an income statement and a cash flow statement is perhaps the single most important thing to learn about financial statements. A cash flow statement is a list of how cash is used and generated each year. If you spend cash to buy a new truck, pay your workers or buy cherries, it will be reflected on the cash flow statement. If you get new cash by borrowing money from the bank or selling green coffee these transactions must also be included in the cash flow statement calculations. An income statement is different. An income statement includes many that do not involve spending any cash. For example, the depreciation of your equipment is included as a cost on an income statement. However, this cost did not involve spending any cash, and is therefore added back on a cash flow statement. Depreciation is simply the loss in value of your equipment over the course of a year. If your truck is worth $10,000 at the beginning of the year and $8,000 at the end of the year, it will have depreciated $2,000 and this will be charged against your earnings for the year. However, the loss in the truck 9s value did not involve any loss of cash, therefore that $2,000 cost will not affect a cash flow statement. A cash flow statement is divided into three separate sections, described below: 20 Cash Flow from Operations Cash generated from the operation of the CWS, including cash earned from the sale of green coffee and cash spent on buying cherries and operating the CWS Cash Flow from Investments Cash spent on investments in the CWS (capital expenditures such as new equipment) Cash Flow from Financial Activities Includes cash gained from borrowing money and cash spent on interest, repaying debt or paying dividends The sum of the cash flow from operations, investments and financial activities is called cNet Cash Generated d and is always exactly equal to the difference between the cash available at the beginning of the year and cash available at the end of the year. Debt The Debt worksheet summarizes the status of any debt owed by the CWS to the bank. The financial model uses two types of bank loans: Term Debt, which must be repaid according to a fixed schedule, and Revolving Debt, which may be repaid at any time. Information on interest expense, principal payments, and any new borrowing that is required in order to have enough cash to buy cherries and pay operating expenses can be found on this worksheet. An explanation of each item on this worksheet is presented in the table below: Term Debt Principal Payments Payments that reduce the amount of Term Debt outstanding Term Debt Interest Payments Interest expense payments for Term Debt Total Term Debt Payments Sum of Term Debt principal and interest payments Beginning Term Debt Balance Balance of Term Debt principal owed at the 21 beginning of the year Ending Term Debt Balance Balance of Term Debt principal owed at the end of the year Revolving Debt Repayment or (Borrowing) Positive numbers indicate that the business has generated excess cash and is able to repay previous Revolving Debt borrowings. Negative numbers indicate that the business needs to borrow additional money in order to meet its working capital requirements for the following year. Revolving Debt Interest Payments Interest payments made on Revolving Debt borrowings. Total Revolving Debt Payments or (Borrowing) Sum of Revolving Debt principal and interest payments Beginning Revolving Debt Balance Balance of Revolving Debt principal owed at the beginning of the year Ending Revolving Debt Balance Balance of Revolving Debt principal owed at the end of the year Beginning Total Debt Balance Sum of Term Debt and Revolving Debt principal owed at the beginning of the year Ending Total Debt Balance Sum of Term Debt and Revolving Debt principal owed at the end of the year Beginning Cash Balance Amount of cash available at the beginning of the year Cash Flow Before Debt Service Total cash flow generated by the CWS before making debt principal and interest payments Funds Available to Service Debt Funds available to make debt principal and interest payments. If this number is less than the required principal and interest payments, new borrowing will be required. Cash Available After Debt Service Cash available after making interest and principal debt payments Ending Cash Balance Amount of cash available at the end of the year Interest charges Total interest expense for Term and Revolving Debt Interest earnings Interest earned from cash holdings Net Interest (Expense) Income Sum of interest charges and interest earnings Don 9t worry if you don 9t understand every item listed on this worksheet. The single most important item on the page is the item cRevolving Debt Repayment or (Borrowing). d A negative number in this row means that the CWS does not have enough cash at the end of the year to allow it to buy cherries and pay its workers for the next season. Therefore, additional borrowing from the bank will be required in order to continue 22 operating the CWS. The value of a negative number in this row tells you exactly how much additional money you will need that year in order to continue running your CWS. The model is currently set so that the CWS earns no interest on the cash that it holds in its cash account. This may be changed in cell B44 if necessary. Working Capital The Working Capital worksheet calculates exactly how much cash is required in order to operate the CWS. A CWS has many expenses that must be paid for before revenue from selling the green coffee is received. Cherries must be purchased and workers must be paid before any revenue from selling green coffee is realized. Therefore, the CWS requires a certain amount of working capital at the beginning of each season in order to pay for these items. The model calculates exactly how much working capital is required at the beginning of each year by multiplying cherry and operation costs by a certain percentage. This percentage is usually set at 50%, but if you believe that working capital should be a higher or lower percentage of these expenses you may change the percentage in cell B10. Calculating the working capital requirement is EXTREMELY IMPORTANT. If it is calculated incorrectly, the CWS may run out of cash and be unable to purchase cherries or pay its workers and be forced to shut down temporarily. Depreciation This worksheet calculates the depreciation of CWS 9s assets for inclusion on the income statement. For people who are familiar with asset valuation accounting, there is a 23 wide variety of information on this worksheet. If you do not have this knowledge, the information on this page is not important and may be ignored. The model is set to depreciate all assets over a period of 10 years, but this number may be changed in cell B13. Discounted Cash Flow Analysis This worksheet calculates one of the most important measures of a project 9s worth, its discounted cash flow (DCF) value. The DCF value is the present value of free cash flows generated by the business over the next 10 years. This value can be thought of as the fair value of the CWS given the current projections. Imagine that you calculate a DCF value of your CWS to be 400 million FRW and it will only cost 150 million FRW to build the CWS. This indicates that building the CWS would be a good investment, because you obtain a business with a value of 400 million FRW for only 150 million FRW. Obviously a great deal! However, imagine that you calculate a DCF value of only 100 million FRW. In this case, building the CWS would not be a good investment, as you will obtain a business with a value of only 100 million FRW for your 150 million FRW investment. How to Calculate a DCF Value: The first step in calculating a DCF value is to calculate projections for the business. You have already accomplished this; the model has automatically done it for 24 you! The second step is to use these projections to calculate the free cash flow (FCF) for each year of the projections. FCF is defined as the cash flow generated by the CWS before interest expense and taxes. It can be calculated using the following formula: Free Cash Flow (FCF) = Net Income + Interest Expense + Depreciation + Taxes 3 Capital Expenditures 3 Change in Working Capital Requirements 1 All of these items can be found on the income statement or cash flow statement that we have already created; therefore, it is a simple matter to calculate the FCF for each year of the projections. The model automatically calculates FCF in row 12 of the DCF worksheet. Once we have calculated the FCF, we must obtain the present value of the future free cash flows. Cash earned in 2008, for example, is not as valuable as cash earned today. To understand why this is so, imagine that you have $100 today. You can invest this money and earn a rate of return between now and 2008. So if you receive $100 today, you will have much more than $100 by 2008. If you receive $100 in 2008, you will obviously only have $100 in 2008! Therefore, this ability to invest money and earn a return makes $100 received today worth more than $100 received in the future. Therefore, we must discount the value of the future cash flows to obtain its present value. A discount rate must be chosen for this task. The actual mathematics involved in calculating the present value of future free cash flows is beyond the scope of this text. However, the model will complete all of these calculations for you. DCF values for the CWS are automatically calculated in row 17 given discount rates chosen in row 16. If you would like to choose different discount rates, they can be 1 All free cash flow calculations used in this model to calculate DCF and IRR values are pre-tax in accordance with local convention. 25 changed by altering the value of cell C16. The DCF value using a discount rate of 20% is shown in a box in cell E17. Users with advanced knowledge of finance can use the bottom part of this worksheet to assign a terminal value to the business and obtain a DCF value based on both the present value of the cash flows and the present value of the terminal value. This is an advanced concept, however, and use of this feature is beyond the scope of this text. IRR Analysis The internal rate of return (IRR) is the rate of return per year on capital outstanding while it is invested in the project. More simply, it is the rate of return that you can expect to receive on your investment in a CWS. On the IRR worksheet, the amount of the investment is shown in cell B12, the free cash flows are shown in C12 3 L12, and the IRR is calculated in cell C15. The detail of how to calculate an IRR mathematically is beyond the scope of this text. However, it is easy to understand what the IRR says about the value of a project. For example, imagine that the model calculates an IRR of 40% for the project. Remember that the IRR is the rate of return that one can expect from their investment in the CWS. If you are able to borrow money at an interest rate of 16%, and earn 40% from investing in a CWS, it is obviously a good idea to do so. It will be a very profitable investment for you! However, imagine that the model calculates an IRR of 10%. In this case, you will be paying 16% in interest each year on the amount you borrowed while only earning 10% on your investment. In this case, it would not be profitable to invest in the CWS. 26 Finally, imagine that the model calculates an IRR of 18%. This means you will be earning 18% on your investment while paying 16% in interest, for a profit of 2% each year. Remember, however, that the IRR is based on projections, not reality. There is always a risk that your projections will not be accurate and you will not earn the expected 18% on your investment. Taking this risk is probably not worth a tiny 2% gain, so once again, this investment is not worthwhile. There are no hard and fast rules about what level of return is acceptable. An investor must weigh the potential risks and rewards himself before making an investment decision. As on the DCF worksheet, this financial model has the ability to calculate the IRR assuming a given terminal value, but an explanation of this function is beyond the scope of this text. Scenario Analysis The Scenario Analysis worksheet is unique in that it does not automatically update itself. BEFORE USING THE NUMBERS ON THIS WORKSHEET, CLICK ON THE BUTTON LABELED cUPDATE TABLES d AT THE TOP OF THE PAGE. The images on the screen will move for a few moments while the computer makes some calculations. This is normal. When it is complete, the model will have updated the tables on this worksheet. These tables summarize the most important information contained in the model. In addition, this worksheet is unique in that it calculates the information using all three possible scenarios ( cPessimistic, d cNormal, d or cOptimistic d) at the same time: A description of each table is given in the table below: Net Profit and Margin Lists the net profit and net profit margin projections for all three scenarios. 27 Operating Profit and Margin Lists the operating profit and operating profit margin projections for all three scenarios. New Funding Required New funding required each year beyond funding provided by the Term Debt loan, the owner 9s own funds, or outside grants. This number is taken from the amount that the CWS is forced to borrow from Revolving Debt. THIS IS PERHAPS THE MOST IMPORTANT ITEM IN THE MODEL, AS IT IS A CALCULATION OF THE NEW FUNDING THAT IS REQUIRED TO KEEP THE CWS OPERATING. Total Debt Total debt outstanding including Term Debt and Revolving Debt Total Debt / Free Cash Flow This is a measure of the CWS 9s debt burden. If this ratio gets too high, the CWS is in danger of not being able to generate enough cash to service its debt. FCF / Total Debt Service The ratio of cash generated by the CWS to principal and interest debt payments. If this ratio falls below one, a new source of funding is required. FCF / Interest Expense Also known as the interest coverage ratio, this ratio calculates the ability of the CWS to produce enough cash to cover its required interest payments DCF Value DCF values for all three scenarios IRR IRR values for all three scenarios This worksheet is perhaps the most important page in the entire model because it allows you to view every important metric in one simple format. You can determine the profitability of the CWS, your ability to pay back your debt and the DCF and IRR value of the project all on one simple page. And you can do it for all three coffee price scenarios at the same time! Coffee Price Conversions This worksheet allows you to convert coffee prices from one format to another. Simply type in the price you want to convert in the appropriate red numbered cell and it will automatically convert that price into every other possible coffee price format. Be sure to type in the latest exchange rate into cell B14 before doing any calculations. 28 That 9s it! You now know how to customize the CWS model to your particular project and understand the projections and financial analysis that are generated. Turn to the next section for a series of exercises that will allow you to test your knowledge and increase your mastery of the model. 29 Exercises Exercise 1: Coffee Price Conversions Open the model and move to the very last worksheet, entitled Coffee Price Conversions . Given an exchange rate of 495 FRW / US$, use the price conversion tool to convert the prices from one format to another and write your answer in the third column: Coffee Price New Units Answer 80 cents / pound US$ / kilogram $1.76 / kilogram $100 / ton cents / pound $2.34 / kilogram FRW / ton FRW 48,372 / 60 kg sack US$ / ton FRW 1,234,321 / ton US$ / 60 kg sack $2,167 / ton cents / pound 94 cents / pound FRW / 60 kg sack $111 / 60 kg sack FRW / ton FRW 987,583 / ton US$ / kilogram $156 / 60 kg sac FRW / kilogram $1.98 / kilogram $US / ton $1,876 / ton FRW / ton FRW 51,234 / 60 kg sac FRW / kilogram 30 Exercise 2: Data Entry Turn to the Appendix, where you will find a printout of the entry worksheets for a typical coffee washing station. Practice your data entry skills by inputting into your model the data shown in these printouts. When you are done, use your model to fill in the following data in order to verify that the data has been entered correctly: 1. Total cost of construction including working capital requirements: FRW___________ 2. Tons of FW-AB green coffee produced in 2005: ______________ 3. Total Term Debt borrowed to finance construction: FRW______________ 4. Transport to ship & FOB costs in 2009: FRW______________ 5. Tons of parchment coffee produced in 2012: ______________ 6. Total cost of Construction and Water Works: FRW______________ 7. Miscellaneous and Unforeseen Construction Expenses: FRW______________ 8. US$ / kg price of cherries in 2012 given the cPessimistic d scenario: ______________ 9. FOB price of FW-AA coffee in 2009 given the cOptimistic d scenario in US$ /kg: ___ 10. Initial working capital requirements: FRW______________ 11. Number of man-days of labor required to produce 100 tons of parchment coffee at this CWS: ______________ 12. Amount spent on fuel oil in 2005: FRW______________ When you are finished, try changing different inputs and notice how it affects the entire model. Experiment to determine how changing costs and coffee prices affect the profitability of the CWS. When you are finished, reenter the original data from the appendix before moving on to the next exercise. 31 Exercise 3: Using Scenarios Continue using the model you created in Exercise 2 for the remainder of the exercises. This exercise will test your ability to use the scenario option to create different financial statements. Turn to the Cherry and Green Coffee Prices worksheet and practice changing the scenario between cPessimistic, d cNormal d and cOptimistic. d Notice that it will tell you at the top of each page which scenario is currently selected. When you feel you understand how to use the scenario switch, answer the following questions to test your knowledge: 1. Net Income in 2013 using the cPessimistic d scenario: FRW______________ 2. Net Income in 2013 using the cNormal d scenario: FRW______________ 3. Net Income in 2013 using the cOptimistic d scenario: FRW______________ 32 Exercise 4: Understanding the Income Statement For this exercise, use the same model originally created in Exercise 2. Set the scenario to cNormal, d turn to the Income Statement worksheet, and fill out the following tables with the correct answers from your model: 1. 2004 2005 2006 2007 2008 Net Income (FRW) 2. 2004 2005 2006 2007 2008 FW-C Revenue (FRW) 3. 2004 2005 2006 2007 2008 Total Revenue (FRW) 4. Total cost of cherries in 2009: FRW______________ 5. Depreciation expense in 2012: FRW______________ 6. Tax expense in 2007: FRW______________ Do not use the model for the following questions. Assume that a certain CWS has the following revenue and costs in 2004: Total Revenue = $100, Cost of Cherries = $40, Operating Costs = $20, Depreciation = $10, Interest Expense = $7, Tax Expense = $5 Calculate the following for this business: 7. Gross profit: US$______________ 8. Operating profit: US$______________ 9. Profit before interest and taxes: US$______________ 10. Net profit: US$______________ 33 Exercise 5: Understanding the Balance Sheet Set your model to the cOptimistic d scenario and turn to the Balance Sheet worksheet. Using this balance sheet, answer the following questions: 1. In what year will the CWS finish paying back its Term Debt? ______________ 2. In what year will the CWS finish paying back its Revolving Debt? ______________ 3. How much cash will the CWS have on hand at the end of 2009? _______________ 4. How much cash will the CWS have on hand at the beginning of 2007? ____________ 5. Why is the value of the CWS 9s property and equipment falling each year? _________ ________________________________________________________________________ Now turn the model to the cPessimistic d scenario and answer the following questions: 6. In what year will the CWS finish paying back its Term Debt? ______________ 7. In what year will the CWS finish paying back its Revolving Debt? ______________ 8. What is the total debt owed by the CWS at the end of 2004? ______________ 9. What is the total debt owed by the CWS at the end of 2013? ______________ 10. In your opinion, will this CWS be able to survive as a business without receiving more funding? ___________________________________________________________ 34 Exercise 6: Understanding the Cash Flow Statement Answer the following questions to test your understanding of the Cash Flow worksheet: 1. Which of the three sections of the cash flow statement is the best indicator of the overall health of the CWS before taking into account required debt payments? _________ 2. When the CWS makes payments on its debt, does that increase or decrease cash flow for that year? ______________ 3. When the CWS borrows additional money from the bank, does that increase or decrease cash flow for that year? ______________ 4. If the CWS spends FRW 10,000,000 on a new truck in 2005, what will be the effect on the cash flow for that year? ______________ 5. Let 9s say the value of the truck depreciates FRW 2,000,000 in 2006. What will be the effect on the cash flow in 2006? ______________ Use the same model as in the previous exercises set to the cNormal d scenario to answer the following questions: 6. What is the CWS 9s cash flow after investments in 2006? ______________ 7. What is the CWS 9s cash flow from operations in 2012? ______________ 8. Imagine that the CWS had no debt in 2008. What would its net cash generated be that year if this were the case? ______________ 35 Exercise 7: Understanding the Debt Worksheet Turn to the Debt worksheet and set the model to the cNormal d scenario before answering the following questions: 1. Why are the Term Debt principal payments c0 d FRW in 2004 and 2005? __________ 2. Why do the Term Debt principal payments get larger over time? _________________ 3. Does the CWS make a payment on the Revolving Debt or borrow additional money from the Revolving Debt in 2005? ______________ 4. Imagine that in reality the CWS is unable to borrow this amount in 2005. What will happen to the CWS 9s operations in 2006? ______________________________________ 5. Imagine that borrowing Revolving Debt is not possible. What can the CWS do to avoid having to ever borrow Revolving Debt? __________________________________ 6. What is the first year that the CWS generates enough cash from its own operations that it does not need to borrow additional money? ______________ 7. Not including the original Term Debt loan, what is the total amount of money borrowed by the CWS to fund its working capital requirements? ______________ 8. Why does the Term Debt principal remain unchanged in the first two years even though total Term Debt payments were over FRW 20,000,000 each year? ____________ ________________________________________________________________________ 9. What is the highest total debt amount reached by the CWS? ______________ 36 Exercise 8: DCF Analysis Set your model to the cNormal d scenario and turn to the DCF worksheet to answer the following questions: 1. What is the DCF value using a discount rate of 20%? ______________ 2. What is the DCF value using a discount rate of 16%? ______________ 3. What is the DCF value using a discount rate of 24%? ______________ 4. What is the best explanation for why FCF is so much lower in the 2004 than in any other year? ______________ 5. Would you purchase this CWS for FRW 50,000,000? Why or why not? ___________ ________________________________________________________________________ 6. Would you purchase this CWS for FRW 500,000,000? Why or why not? __________ ________________________________________________________________________ 7. Why is depreciation added back to net profit when calculating free cash flow? _______ ________________________________________________________________________ 8. Why are capital expenditures subtracted when calculating free cash flow? __________ ________________________________________________________________________ 37 Exercise 9: IRR Analysis Set the model to the cNormal d scenario and turn to the IRR worksheet: 1. What is the IRR calculated by the model? ______________ 2. If you are able to borrow money to invest at 16%, is investing in this CWS a good investment opportunity? Why or why not? _____________________________________ 3. Will the IRR be higher, lower or stay the same if you change the scenario to cPessimistic? d ______________ 4. Will the IRR be higher, lower or stay the same if the interest rate on the Term Debt is 25% instead of 16%? Why? _________________________________________________ ________________________________________________________________________ 5. If the interest rate on borrowing money to invest in this CWS were 25%, would this be a good investment opportunity given the cNormal d scenario? Why or why not? _______ ________________________________________________________________________ 38 Exercise 10: The Scenario Analysis Worksheet Turn to the Scenario Analysis worksheet and press the cUpdate Tables d button at the top of the page. Remember, this worksheet displays results for all three scenarios at the same time; therefore, it does not matter which scenario is currently selected. Once the model has updated the tables, use them to answer the following questions: 1. What is the DCF value for the cPessimistic d ______________ cNormal d __________ and cOptimistic d ______________ scenarios? 2. What is the IRR for the cPessimistic d ______________ cNormal d ______________ and cOptimistic d ______________ scenarios? 3. Based on the information on this worksheet, in which scenarios do you think an investment in this CWS will be profitable? ______________ For which scenarios will an investment be unprofitable? ______________ 4. If Free Cash Flow / Total Debt Service is below 1.0x for the life of the project, will the investment be viable in the long-term? Why or why not? _______________________ ________________________________________________________________________ 5. What Total Debt / Free Cash Flow ratio indicates a more healthy business c10.0x d or c2.0x d? ______________ 6. What costs are included in the calculation of net profit that are not included in the calculation of operating profit? ______________ 7. Under the cNormal d scenario, how much new funding is required in 2004? _________ 8. If new funding required exceeds the amount of funding that the CWS is actually able to receive from banks, will the CWS be able to operate at full capacity? ______________ 39 Appendix 40

less