In order to successfully build the pro forma income statement for both the best and the worst case scenarios the financial research on the matter should necessarily be derived from the information provided in the case study assignment. Be it the best or the worst scenario the income statement is divided into four distinct categories namely initial investments, the revenue, the expenses and the operational cash flows (Osborne, 2010). Based on these categories the appropriate data was inserted into each year of the total lifespan of Brewpub system which was 10 years including year 0.
The first part of the income statements shows the initial investments of the restaurant when it came to purchasing and implementing the Brewpub system. These initial investments include the Brewpub System Cost of $300,000, the construction cost of the space rented next door to hold the Brewpub System at $250,000, the website redesign at $20,000, and the initial licensing fees of $65,000. With all the above mentioned costs, the total initial investment of the Brewpub project came to a total of $635,000. All of these initial investments are done in Year 0 before the start of the Brewpub system in year 1. The next section was the Revenue section. Listed under the revenue section was the data including the Price paid by customers per pint, the expected number of pints sold per year, the barrels of beer purchased by the restaurant per year and the cost of the barrels? With this information, a total of three calculations were done. The first of the three calculations was the Net Revenue which was calculated by multiplying the price paid by the customers per pint, and the expected number of pints sold per year. The second calculation was done to determine the Cost of Goods Sold which multiplied the barrels sold per year by the cost per barrels The Gross Profit of the Brewpub system project was then calculated by subtracting the Cost of Goods Sold by the Net Revenue. The Gross Profit of the Brewpub system starts in year 1 and will increase yearly due to the increase in the price per pint for the customer, even with the costs per barrel increasing as well. Then, the Expenses section listed out all expenses the company will incur with the implementation of the Brewpub system from Year 1 through Year 10 (Lohmann et al, 1993). These expenses include maintenance on the system, salary of 3 additional services, and salary of the fixed employee to work on the system, the increase in utilities, the yearly licensing fee, and the rent for the additional space to house the system, the advertisement costs, and the depreciation costs of the Brewpub Systems. All of the above mentioned expenses, the Total Expenses can be derived. However, each year the total expenses will increase due to the percentage increase of the salary of the 3 employees (Bas, 2013).
Delegate your assignment to our experts and they will do the rest.
NPV and IRR
NPV is calculated at $409,140.66 and the IRR is 22% in terms of the best case scenario. A positive NPV means the investment is worthwhile, and that Samantha and Grant should go ahead with Brewpub investment (Penman & Penman, 2007). Given the cost of capital sits at 8%, and the IRR is 22%, if the IRR of a new project exceeds a company’s required rate of return, that project is desirable. Samantha and Grant would be inclined to take on a project that has a return of close to 3 times their cost of capital (Pratt, 2003).
Best Case Scenario
| Consolidated Statement for Income (USD $) |
0 |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
| Company Sales |
694,400.00 |
715,232.00 |
736,688.96 |
758,789.63 |
781,553.32 |
804,999.92 |
829,149.91 |
854,024.41 |
879,645.14 |
906,034.50 |
| Total Revenues |
694,400.00 |
715,232.00 |
736,688.96 |
758,789.63 |
781,553.32 |
804,999.92 |
829,149.91 |
854,024.41 |
879,645.14 |
906,034.50 |
| Consultation |
(2,000.00) |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
| Rent |
(30,000.00) |
(30,000.00) |
(30,000.00) |
(30,000.00) |
(30,000.00) |
(30,000.00) |
(30,000.00) |
(30,000.00) |
(30,000.00) |
(30,000.00) |
| Construction |
(250,000.00) |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
| BrewPub System |
(300,000.00) |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
| Cost of Beer |
(224,000.00) |
(235,200.00) |
(246,960.00) |
(259,308.00) |
(272,273.40) |
(285,887.07) |
(300,181.42) |
(315,190.49) |
(330,950.02) |
(347,497.52) |
| Maintenance |
(15,000.00) |
(15,000.00) |
(15,000.00) |
(15,000.00) |
(15,000.00) |
(15,000.00) |
(15,000.00) |
(15,000.00) |
(15,000.00) |
(15,000.00) |
| Payroll |
(168,000.00) |
(173,040.00) |
(178,231.20) |
(183,578.14) |
(189,085.48) |
(194,758.04) |
(200,600.79) |
(206,618.81) |
(212,817.37) |
(219,201.89) |
| Insurance |
(3,000.00) |
(3,150.00) |
(3,307.50) |
(3,472.88) |
(3,646.52) |
(3,828.84) |
(3,828.84) |
(3,828.84) |
(3,828.84) |
(3,828.84) |
| Utilities |
(24,000.00) |
(24,000.00) |
(24,000.00) |
(24,000.00) |
(24,000.00) |
(24,000.00) |
(24,000.00) |
(24,000.00) |
(24,000.00) |
(24,000.00) |
| License Fees |
(65,000.00) |
(700.00) |
(700.00) |
(700.00) |
(700.00) |
(700.00) |
(700.00) |
(700.00) |
(700.00) |
(700.00) |
| Advertising |
(80,000.00) |
(80,000.00) |
(80,000.00) |
(80,000.00) |
(80,000.00) |
(80,000.00) |
(80,000.00) |
(80,000.00) |
(80,000.00) |
(80,000.00) |
| Media Company |
(20,000.00) |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
| Starting Inventory |
(10,000.00) |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
| Total Cost & Expenditure Net |
(1,191,000.00) |
(561,090.00) |
(578,198.70) |
(596,059.01) |
(614,705.40) |
(634,173.96) |
(654,311.05) |
(675,338.15) |
(697,296.24) |
(720,228.26) |
| Opearting Profit |
(496,600.00) |
154,142.00 |
158,490.26 |
162,730.62 |
166,847.92 |
170,825.96 |
174,838.86 |
178,686.26 |
182,348.91 |
185,806.24 |
| Income Before Income Tax |
(496,600.00) |
154,142.00 |
158,490.26 |
162,730.62 |
166,847.92 |
170,825.96 |
174,838.86 |
178,686.26 |
182,348.91 |
185,806.24 |
| Tax Provision |
(645,580.00) |
107,899.40 |
110,943.18 |
113,911.43 |
116,793.54 |
119,578.17 |
122,387.20 |
125,080.38 |
127,644.23 |
130,064.37 |
| Net Income (loss) |
(645,580.00) |
(537,680.60) |
(426,737.42) |
(312,825.99) |
(196,032.44) |
(76,454.27) |
45,932.93 |
171,013.32 |
298,657.55 |
428,721.92 |
| Consolidated Statement for Income (USD $) |
0 |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
| Company Sales |
496,000.00 |
510,880.00 |
526,206.40 |
541,992.59 |
558,252.37 |
574,999.94 |
592,249.94 |
610,017.44 |
628,317.96 |
647,167.50 |
| Total Revenues |
694,400.00 |
510,880.00 |
526,206.40 |
541,992.59 |
558,252.37 |
574,999.94 |
592,249.94 |
610,017.44 |
628,317.96 |
647,167.50 |
| Consultation |
(2,000.00) |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
| Rent |
(30,000.00) |
(30,000.00) |
(30,000.00) |
(30,000.00) |
(30,000.00) |
(30,000.00) |
(30,000.00) |
(30,000.00) |
(30,000.00) |
(30,000.00) |
| Construction |
(250,000.00) |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
| BrewPub System |
(300,000.00) |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
| Cost of Beer |
(160,000.00) |
(168,000.00) |
(176,400.00) |
(185,220.00) |
(194,481.00) |
(204,205.05) |
(214,415.30) |
(225,136.07) |
(236,392.87) |
(248,212.51) |
| Maintenance |
(15,000.00) |
(15,000.00) |
(15,000.00) |
(15,000.00) |
(15,000.00) |
(15,000.00) |
(15,000.00) |
(15,000.00) |
(15,000.00) |
(15,000.00) |
| Payroll |
(168,000.00) |
(173,040.00) |
(178,231.20) |
(183,578.14) |
(189,085.48) |
(194,758.04) |
(200,600.79) |
(206,618.81) |
(212,817.37) |
(219,201.89) |
| Insurance |
(3,000.00) |
(3,150.00) |
(3,307.50) |
(3,472.88) |
(3,646.52) |
(3,828.84) |
(3,828.84) |
(3,828.84) |
(3,828.84) |
(3,828.84) |
| Utilities |
(24,000.00) |
(24,000.00) |
(24,000.00) |
(24,000.00) |
(24,000.00) |
(24,000.00) |
(24,000.00) |
(24,000.00) |
(24,000.00) |
(24,000.00) |
| License Fees |
(65,000.00) |
(700.00) |
(700.00) |
(700.00) |
(700.00) |
(700.00) |
(700.00) |
(700.00) |
(700.00) |
(700.00) |
| Advertising |
(80,000.00) |
(80,000.00) |
(80,000.00) |
(80,000.00) |
(80,000.00) |
(80,000.00) |
(80,000.00) |
(80,000.00) |
(80,000.00) |
(80,000.00) |
| Media Company |
(20,000.00) |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
| Starting Inventory |
(10,000.00) |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
| Total Cost & Expenditure Net |
(1,127,000.00) |
(493,890.00) |
(507,638.70) |
(521,971.01) |
(536,913.00) |
(552,491.94) |
(568,544.93) |
(585,283.72) |
(602,739.09) |
(620,943.25) |
| Opearting Profit |
(432,600.00) |
16,990.00 |
18,567.70 |
20,021.58 |
21,339.37 |
22,508.00 |
23,705.01 |
24,733.72 |
25,578.87 |
26,224.25 |
| Income Before Income Tax |
(432,600.00) |
16,990.00 |
18,567.70 |
20,021.58 |
21,339.37 |
22,508.00 |
23,705.01 |
24,733.72 |
25,578.87 |
26,224.25 |
| Tax Provision |
(562,380.00) |
11,893.00 |
12,997.39 |
14,015.11 |
14,937.56 |
15,755.60 |
16,593.50 |
17,313.60 |
17,905.21 |
18,356.97 |
| Net Income (loss) |
(562,380.00) |
(550,487.00) |
(537,489.61) |
(523,474.50) |
(508,536.94) |
(492,781.34) |
(476,187.84) |
(458,874.24) |
(440,969.03) |
(422,612.06) |
Worst Case Scenario
Alternate Best Case Scenario
Hypothetically if in year 5 the pint price would be increased to $7 and the NPV value becomes $ 365,406 and the IRR reduces to 16%. The below tabulated results showcase the alternative scenario that would cater with such changes, also I have taken into account the 1 million renovation cost at year 5.
|
Consolidated Statement for Income (USD $) |
0 |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
|
Company Sales |
694,400.00 |
715,232.00 |
736,688.96 |
758,789.63 |
781,553.32 |
973,896.00 |
1,003,112.88 |
1,033,206.27 |
1,064,202.45 |
1,096,128.53 |
|
Total Revenues |
694,400.00 |
715,232.00 |
736,688.96 |
758,789.63 |
781,553.32 |
973,896.00 |
1,003,112.88 |
1,033,206.27 |
1,064,202.45 |
1,096,128.53 |
|
Consultation |
(2,000.00) |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
|
Rent |
(30,000.00) |
(30,000.00) |
(30,000.00) |
(30,000.00) |
(30,000.00) |
(30,000.00) |
(30,000.00) |
(30,000.00) |
(30,000.00) |
(30,000.00) |
|
Construction |
(250,000.00) |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
|
BrewPub System |
(300,000.00) |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
Cost of Beer |
(224,000.00) |
(235,200.00) |
(246,960.00) |
(259,308.00) |
(272,273.40) |
(285,887.07) |
(300,181.42) |
(315,190.49) |
(330,950.02) |
(347,497.52) |
|
Maintenance |
(15,000.00) |
(15,000.00) |
(15,000.00) |
(15,000.00) |
(15,000.00) |
(15,000.00) |
(15,000.00) |
(15,000.00) |
(15,000.00) |
(15,000.00) |
|
Payroll |
(168,000.00) |
(173,040.00) |
(178,231.20) |
(183,578.14) |
(189,085.48) |
(194,758.04) |
(200,600.79) |
(206,618.81) |
(212,817.37) |
(219,201.89) |
|
Insurance |
(3,000.00) |
(3,150.00) |
(3,307.50) |
(3,472.88) |
(3,646.52) |
(3,828.84) |
(3,828.84) |
(3,828.84) |
(3,828.84) |
(3,828.84) |
|
Utilities |
(24,000.00) |
(24,000.00) |
(24,000.00) |
(24,000.00) |
(24,000.00) |
(24,000.00) |
(24,000.00) |
(24,000.00) |
(24,000.00) |
(24,000.00) |
|
License Fees |
(65,000.00) |
(700.00) |
(700.00) |
(700.00) |
(700.00) |
(700.00) |
(700.00) |
(700.00) |
(700.00) |
(700.00) |
|
Advertising |
(80,000.00) |
(80,000.00) |
(80,000.00) |
(80,000.00) |
(80,000.00) |
(80,000.00) |
(80,000.00) |
(80,000.00) |
(80,000.00) |
(80,000.00) |
|
Media Company |
(20,000.00) |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
|
Renovation |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
(1,000,000.00) |
0.00 |
0.00 |
0.00 |
0.00 |
|
Starting Inventory |
(10,000.00) |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
0.00 |
|
Total Cost & Expenditure Net |
(1,191,000.00) |
(561,090.00) |
(578,198.70) |
(596,059.01) |
(614,705.40) |
(1,634,173.96) |
(654,311.05) |
(675,338.15) |
(697,296.24) |
(720,228.26) |
|
Opearting Profit |
(496,600.00) |
154,142.00 |
158,490.26 |
162,730.62 |
166,847.92 |
(660,277.96) |
348,801.83 |
357,868.12 |
366,906.22 |
375,900.27 |
Income Before Income Tax |
(496,600.00) |
154,142.00 |
158,490.26 |
162,730.62 |
166,847.92 |
(660,277.96) |
348,801.83 |
357,868.12 |
366,906.22 |
375,900.27 |
|
Tax Provision |
(645,580.00) |
107,899.40 |
110,943.18 |
113,911.43 |
116,793.54 |
(462,194.57) |
244,161.28 |
250,507.68 |
256,834.35 |
263,130.19 |
|
Net Income (loss) |
(645,580.00) |
(537,680.60) |
(426,737.42) |
(312,825.99) |
(196,032.44) |
(658,227.01) |
(414,065.74) |
(163,558.05) |
93,276.30 |
356,406.49 |
Change in Cost of Capital
It is important to associate the IRR to the cost of capital when determining investment decisions: If the Internal Rate of Return is higher than the cost of capital the Brewpub project would be intrinsically valuable, with a positive Net Present Value (NPV), and if the Internal Rate of Return is lower than the COC it should never be effectively utilized in the business model. The NPV is preferred to the IRR to work out stable investments over time. So, whilst a higher COC will not change the IRR, it will lead to fewer investment decisions being satisfactory when using IRR as the process of evaluating those investment conclusions.
Factors Not Considered
Samantha and Grant have not considered inflation as a valid factor to their business model which can effectively derail any sort of pro forma calculation in a given model. Inflation can be speculated by reviewing past financial data of the market that they are reviewing and then based on the information procured, adjustments to the income statement can be made. These inflation costs can lead to overall loss of the business if the target costs for all expenses do not fall under the budgeted value.
References
Osborne, M. J. (2010). A resolution to the NPV–IRR debate? The Quarterly Review of Economics and Finance, 50(2), 234-239.
Penman, S. H., & Penman, S. H. (2007). Financial statement analysis and security valuation (p. 476). New York: McGraw-Hill.
Pratt, S. P. (2003). Cost of capital: estimation and applications. John Wiley & Sons.
Lohmann, J. R., & BAKSH, S. N. (1993). The IRR, NPV and Payback period and their relative performance in common capitial budgeting decision procedures for dealing with risk. The Engineering Economist, 39(1), 17-47.
Bas, E. (2013). A robust approach to the decision rules of NPV and IRR for simple projects. Applied Mathematics and Computation, 219(11), 5901-5908.
