**** Need help with completing an excel spreadsheet regarding the following information, please ***

Need help incorporating a feasible discount rate that takes into account maintenance and other expenses, the desired profit margin of 15%

One analytical technique that can be used to find the bid price for the lease is the net present value (NPV) method. This method involves discounting the future cash flows of the project to their present value using an appropriate discount rate, and then subtracting the initial investment to arrive at the net present value.

To build the financial model to estimate the bid price using the NPV method in Excel, you can follow these steps:

Open a new Excel spreadsheet.

In row 1, enter the following headings in separate cells: "Year", "Cash inflow", "Cash outflow", "Net cash flow", "Discount factor", "Present value".

In column A, enter the years of the lease term from year 0 (initial investment) to year 6.

In cell B2, enter the annual lease payment of $83,760 for the first three years (assuming the lease payment remains the same for the first three years).

In cell B6, enter the annual lease payment of $92,136 for the last three years (assuming a 10% increase in lease payment from year 4 onwards).

In cell B8, enter the initial investment of $283,200 (assuming the purchase of 100 DG sets at a cost of $2,820 per set).

In column C, enter the annual interest payment on the loan, which is calculated as the loan amount ($226,560) multiplied by the interest rate (10%). This value will be the same for each year of the lease term.

In column D, calculate the net cash flow for each year by subtracting the cash outflow from the cash inflow (i.e., net cash flow = cash inflow - cash outflow).

In column E, calculate the discount factor for each year using the formula: 1 / (1 + discount rate)^year.

In column F, calculate the present value of the net cash flow for each year by multiplying the net cash flow by the discount factor.

In cell F8, calculate the total present value of the net cash flow by summing the present values in column F.

In cell F9, calculate the net present value of the project by subtracting the initial investment (cell B8) from the total present value of the net cash flow (cell F8).

Your Excel spreadsheet should now show the net present value of the lease project, which can be used as the basis for estimating the bid price. To arrive at the bid price, Rudra would need to add a suitable margin to the net present value to cover their costs and generate a profit.

Totalcost=$354,000+$20,000+$8,760,000=$9,134,000 To calculate the bid price, we can add a suitable profit margin to the total cost. The margin will depend on various factors such as competition, market conditions, and the desired return on investment. Let's assume that Rudra wants to earn a profit margin of 15% on the leasing service. The bid price would then be: Bid price = Total cost x(1+ Profit margin ) =$9,134,000(1+0.15)=$10,509,100 The cost of providing the generator leasing service will include the cost of the generators, maintenance expenses, and the cost of diesel fuel. We already know that the cost of 100 DG sets is $354,000. To estimate the annual maintenance expenses, we can multiply the number of DG sets (100 ) by the estimated annual maintenance cost per DG set ( $200), which gives us a total of $20,000 per year. The cost of diesel fuel will depend on the amount of fuel consumed by the DG sets and the price of diesel fuel. Assuming that each DG set runs for 12 hours per day (50\% utilization), and consumes 2 litres of diesel fuel per hour, we can calculate the total annual diesel consumption as follows: Total annual diesel consumption =100 DG sets 12 hours/day 365 days/year 2 litres / hour =8,760,000 litres The March 2020 contract offered by Baroda Bank for the lease of 100 DG sets was the opportunity that Hairiya and Patra had been anticipating for some time. Their company could expand from operating strictly in the highly competitive business of providing DG maintenance services into the new, vertical business market of leasing DG sets. Both Hairiya and Patra were eager to explore this lucrative new opportunity, which they hoped would bring a much-needed longer-term revenue scope and help diversify their business. The proposal was required to be structured in the form of a lease, rather than a sale, and had to include all incidental and operational costs. The COVID-19 lockdown had made the entire Rudra staff unavailable. Therefore, the task of calculating the pricing in the bid proposal for the lease of all equipment and services rested on Hairiya and Patra. The two company executives were able to meet in person to discuss and prepare their proposal. After inquiring about the well-being of each other's families, especially under the dire circumstances of the pandemic, the two co-workers started with a conversation about the business: Patra: How are our field workers doing? Were they able to attend to the emergency maintenance calls that we received? Hairiya: I have been in regular touch with our field workers and office staff; all are keeping good health. Some of our field workers were able to go and attend to the DG emergencies at our hospital clients. They are taking all precautions. Luckily, local administration has classified DG Patra: That's good. We don't know how long this lockdown will last, but we need to support our clients to the best of our ability. Our finance manager has assured me that we have enough cash balances to pay salaries for a few months. He will come for a couple of hours at the end of April and transfer salaries. Hairiya: He could have done so from home, had we provided him with a laptop computer instead of our usual office desktop computers. That would have allowed him to help us now, in our bid preparation, also. Does it make sense to go ahead with the proposal in this uncertain environment? Will we be able to procure and service the generators required by the Baroda Bank? Patra: Banks are an essential business, and branches are opening for limited hours every day. All their support services have been deemed essential too. Once we are successful in the bid, the bank will help us procure and service the DGs. Hairiya: What about field workers? Will we be able to hire more [workers] to service this large proposal, while maintaining our service standards? Patra: In the near future, as many of our other clients have been forced to shut [down] their establishments due to the lockdown, we in fact have excess field workers on our [pay]rolls. If and when businesses start to reopen, we can look to hire more field staff quickly. Hairiya: Sounds fair. Let's discuss the technical details before the financial bid. What type of DGs is the bank looking for, and how much would it cost us? Patra: The bank wants 15kVA generators set up at their 100 branches. A 15kVA DG set would cost approximately $3,000 plus 18 per cent goods and services tax. The 100DG sets would cost $354,000. I believe we can fund this internally using our cash reserves. Hairiya: We can think of taking a loan as well. Our bank would be ready to fund at most 80 per cent of the capital expense, at 10 per cent interest, payable in equal instalments on an annual basis. A 30 per cent corporate tax would provide us with a tax shield, and the actual interest paid by us in terms of cash flow might be even lower. We have to fund the rest through Rudra's cash reserves. What is the contract period? Patra: The lease is for six years, with the lease payment remaining the same for the first three years. We are allowed to increase the lease payment by 10 per cent in the fourth year, and [it] has to be kept [the] same thereafter. I hope our bankers will be comfortable providing us with a six-year loan. The generators can be used as collateral for the loan. Hairiya: Yes, definitely. The collateral will make the loan less risky for our bankers. Who will incur the running costs of diesel and maintenance, Rudra or Baroda Bank? Patra: We will have to incur all running costs. Because we are purchasing the generators, we can also depreciate them using the straight-line method to zero. Hairiva: That is good. We can do the maintenance in house and ensure that the generators remain kept [the] same thereafter. I hope our bankers will be comfortable providing us with a six-year loan. The generators can be used as collateral for the loan. Hairiya: Yes, definitely. The collateral will make the loan less risky for our bankers. Who will incur the running costs of diesel and maintenance, Rudra or Baroda Bank? Patra: We will have to inr all running costs. Because we are purchasing the generators, we can also depreciate them using the straight-line method to zero. Hairiya: That is good. We can do the maintenance in house and ensure that the generators remain in good condition. Banks work for approximately 250 days a year, as compared to telecom towers, which run all through the year, and this is to our advantage. If the contract is not renewed, we can sell the DGs at $1,500 apiece after six years. Totalcost=$354,000+$20,000+$8,760,000=$9,134,000 To calculate the bid price, we can add a suitable profit margin to the total cost. The margin will depend on various factors such as competition, market conditions, and the desired return on investment. Let's assume that Rudra wants to earn a profit margin of 15% on the leasing service. The bid price would then be: Bid price = Total cost x(1+ Profit margin ) =$9,134,000(1+0.15)=$10,509,100 The cost of providing the generator leasing service will include the cost of the generators, maintenance expenses, and the cost of diesel fuel. We already know that the cost of 100 DG sets is $354,000. To estimate the annual maintenance expenses, we can multiply the number of DG sets (100 ) by the estimated annual maintenance cost per DG set ( $200), which gives us a total of $20,000 per year. The cost of diesel fuel will depend on the amount of fuel consumed by the DG sets and the price of diesel fuel. Assuming that each DG set runs for 12 hours per day (50\% utilization), and consumes 2 litres of diesel fuel per hour, we can calculate the total annual diesel consumption as follows: Total annual diesel consumption =100 DG sets 12 hours/day 365 days/year 2 litres / hour =8,760,000 litres The March 2020 contract offered by Baroda Bank for the lease of 100 DG sets was the opportunity that Hairiya and Patra had been anticipating for some time. Their company could expand from operating strictly in the highly competitive business of providing DG maintenance services into the new, vertical business market of leasing DG sets. Both Hairiya and Patra were eager to explore this lucrative new opportunity, which they hoped would bring a much-needed longer-term revenue scope and help diversify their business. The proposal was required to be structured in the form of a lease, rather than a sale, and had to include all incidental and operational costs. The COVID-19 lockdown had made the entire Rudra staff unavailable. Therefore, the task of calculating the pricing in the bid proposal for the lease of all equipment and services rested on Hairiya and Patra. The two company executives were able to meet in person to discuss and prepare their proposal. After inquiring about the well-being of each other's families, especially under the dire circumstances of the pandemic, the two co-workers started with a conversation about the business: Patra: How are our field workers doing? Were they able to attend to the emergency maintenance calls that we received? Hairiya: I have been in regular touch with our field workers and office staff; all are keeping good health. Some of our field workers were able to go and attend to the DG emergencies at our hospital clients. They are taking all precautions. Luckily, local administration has classified DG Patra: That's good. We don't know how long this lockdown will last, but we need to support our clients to the best of our ability. Our finance manager has assured me that we have enough cash balances to pay salaries for a few months. He will come for a couple of hours at the end of April and transfer salaries. Hairiya: He could have done so from home, had we provided him with a laptop computer instead of our usual office desktop computers. That would have allowed him to help us now, in our bid preparation, also. Does it make sense to go ahead with the proposal in this uncertain environment? Will we be able to procure and service the generators required by the Baroda Bank? Patra: Banks are an essential business, and branches are opening for limited hours every day. All their support services have been deemed essential too. Once we are successful in the bid, the bank will help us procure and service the DGs. Hairiya: What about field workers? Will we be able to hire more [workers] to service this large proposal, while maintaining our service standards? Patra: In the near future, as many of our other clients have been forced to shut [down] their establishments due to the lockdown, we in fact have excess field workers on our [pay]rolls. If and when businesses start to reopen, we can look to hire more field staff quickly. Hairiya: Sounds fair. Let's discuss the technical details before the financial bid. What type of DGs is the bank looking for, and how much would it cost us? Patra: The bank wants 15kVA generators set up at their 100 branches. A 15kVA DG set would cost approximately $3,000 plus 18 per cent goods and services tax. The 100DG sets would cost $354,000. I believe we can fund this internally using our cash reserves. Hairiya: We can think of taking a loan as well. Our bank would be ready to fund at most 80 per cent of the capital expense, at 10 per cent interest, payable in equal instalments on an annual basis. A 30 per cent corporate tax would provide us with a tax shield, and the actual interest paid by us in terms of cash flow might be even lower. We have to fund the rest through Rudra's cash reserves. What is the contract period? Patra: The lease is for six years, with the lease payment remaining the same for the first three years. We are allowed to increase the lease payment by 10 per cent in the fourth year, and [it] has to be kept [the] same thereafter. I hope our bankers will be comfortable providing us with a six-year loan. The generators can be used as collateral for the loan. Hairiya: Yes, definitely. The collateral will make the loan less risky for our bankers. Who will incur the running costs of diesel and maintenance, Rudra or Baroda Bank? Patra: We will have to incur all running costs. Because we are purchasing the generators, we can also depreciate them using the straight-line method to zero. Hairiva: That is good. We can do the maintenance in house and ensure that the generators remain kept [the] same thereafter. I hope our bankers will be comfortable providing us with a six-year loan. The generators can be used as collateral for the loan. Hairiya: Yes, definitely. The collateral will make the loan less risky for our bankers. Who will incur the running costs of diesel and maintenance, Rudra or Baroda Bank? Patra: We will have to inr all running costs. Because we are purchasing the generators, we can also depreciate them using the straight-line method to zero. Hairiya: That is good. We can do the maintenance in house and ensure that the generators remain in good condition. Banks work for approximately 250 days a year, as compared to telecom towers, which run all through the year, and this is to our advantage. If the contract is not renewed, we can sell the DGs at $1,500 apiece after six years