Create an Excel spreadsheet and upload it to Canvas under the Assignment. Do all of your work in a single Excel tab. You should format the spreadsheet so that it will print on no more than two sheets of paper. The spreadsheet should be formatted to be attractive and professional. The printed and electronic versions of the spreadsheet should be a something that you would be proud to submit to your manager or supervisor at your professional workplace.
Hint: Use Print Preview to see what the spreadsheet will look like when it is printed. You may need to change the spreadsheet to Landscape Orientation.
Except for the source information, you should not type any other numbers into the spreadsheet. You should use formulas and references to create a working model. All calculations should be done in the spreadsheet. You may not calculate or display any value more than once, or enter any source data more than once. You must also record all assumptions.
Even though this is an assignment that is done outside of class, the rules regarding cheating still apply. You ARE allowed to work with your classmates on the ideas and processes involved, but you must create and submit your own unique work. Submitting the work of others will be a violation of the UT honor code.
Model 2: Capital Budgeting Model Create an Excel spreadsheet and upload it to Canvas under the Assignment. Do all of your work in a single Excel tab. You should format the spreadsheet so that it will print on no more than two sheets of paper. The spreadsheet should be formatted to be attractive and professional. The printed and electronic versions of the spreadsheet should be a something that you would be proud to submit to your manager or supervisor at your professional workplace. Hint: Use Print Preview to see what the spreadsheet will look like when it is printed. You may need to change the spreadsheet to Landscape Orientation. Except for the source information, you should not type any other numbers into the spreadsheet. You should use formulas and references to create a working model. All calculations should be done in the spreadsheet. You may not calculate or display any value more than once, or enter any source data more than once. You must also record all assumptions. Even though this is an assignment that is done outside of class, the rules regarding cheating still apply. You ARE allowed to work with your classmates on the ideas and processes involved, but you must create and submit your own unique work. Submitting the work of others will be a violation of the UT honor code. You must complete and submit the spreadsheet to Canvas no later than 02:00 PM CST on Thursday, April 1st. Late work WILL NOT be accepted and there are no make-up opportunities. Your employer is considering a capital project that involves installing a flavored water bottling line at a cost of $1,870,000. The line will be installed in an area of the plant that was refurbished in 2018. At that time, the refurbishment cost $1,250,000. If it is not employed by this project, that area of the factory will remain unused. The new bottling line, if built, will be depreciated on a straight-line basis over six years, to a book value of $0. If implemented, the project will cause an immediate need for additional inventory of $97,000. It will also cause immediate increases in Accounts Receivable of $131,000, Accounts Payable of $132,000 and $1,710,000 in long term debt. If implemented, the project is expected to generate annual sales of $1,630,000 by the end of the first year. Sales are expected to increase 4.5% per year. Costs are expected to be $680,000 during the first year. Thereafter, costs expected to remain at a constant percentage of Sales. At the end of the project’s six-year life, production will cease, and the bottling equipment will be sold for an estimated $96,000. At that time, Inventory, Accounts Receivable and Accounts Payable will return to their pre-project levels. If the project is implemented, it will likely cannibalize/erode sales of the firm’s fruit juice product line. The net impact of the cannibalization is expected to be a $23,000 annual reduction in pre-tax profits. Your employer’s tax rate is 21%. The firm has 750,000 shares of common stock outstanding. The firm requires an 11% rate of return on capital projects. Prepare a cash flow analysis to determine whether your employer should implement this project. Your analysis should reveal answers to each of the following questions. 1. What is the initial investment amount? 2. What are the future annual operating cash flow amounts? 3. What is the NPV? 4. What is the IRR? 5. What is the Profitability Index? 6. Should the firm implement the project? Why or why not? How much does the project earn? Model 2: Capital Budgeting Model