Reference no: EM132263891

Assignment: Preview: Your father-in-law, Steve, owns and operates a small but successful landscaping company in the suburbs of Philadelphia. However, the continuing success of the company strongly depends on his ability to do a lot of the day-to-day work. Steve recently turned 50, and the thought of continued intense labor is not very appealing to him. He is faced with a choice: invest money in the company to build it to a point that all he has to do is manage it from a distance, or sell the company. Steve has come to you to help him model the outcome of his choices and provide recommendations. You will use Microsoft Excel to provide Steve a look into the future.

Assignment 1: Creating A Spreadsheet For Decision Support

In this assignment, you will produce a spreadsheet that models Philly Landscaping's estimated 2017 revenues, expenses, and profits; provides forecasts of 10 years of cash flows for the company; and allows for the input of other variables to answer Steve's questions. In Assignment 2, you will use your spreadsheet to gather data and then write a memorandum that documents your analysis and findings.

First, you need to create the spreadsheet model based on your conversations with Steve and your understanding of the questions he would like to have answered. The model will cover 11 years-2017 as the base year and 10 subsequent years as requested by the bank to provide estimated cash flows. This section helps you set up each of the following spreadsheet components before entering cell formulas (see attached file).

ASSIGNMENT 2: USING THE SPREADSHEET FOR DECISION SUPPORT

Complete the case by (1) using the spreadsheet to answer Steve's questions, (2) thoroughly analyzing your data, and (3) documenting your findings in a memo.

Steve needs to understand the impacts of a few different options he is considering. He wants to determine his available cash flow at the end of each year based on the following scenarios:

1. Do nothing-Assume that no changes will be made and keep all of Steve's assumptions. With this scenario, he anticipates an annual 5 percent decrease in the customer base. Annual income in this scenario is $65,000.

2. Accept the bank loan with low return on investment (ROI)-Steve has met with the bank's loan officer, who estimated that a 10-year loan with 3 percent annual payments would total $120,000. Using the loan for capital expenses and marketing campaigns, Steve expects that the customer base can grow at 6 percent annually starting in 2018. Annual income in this scenario is $100,000.

3. Accept the bank loan with high ROI-Again, the loan officer estimated that a 10-year loan with 3 percent annual payments would total $120,000. Using the loan for capital expenses andmarketing campaigns, Steve expects that the customer base can grow at 13 percent annually starting in 2018.

4. Accept buyout offer-In this scenario, Steve would accept the buyout offer from a competitor, invest it, and start collecting $100,000 annually from the annuity. Note that this scenario would require a one-time change to the customer base, from 850 to 0.

Assignment 2A: Using the Spreadsheet to Gather Data

You have built the spreadsheet to model several possible situations. For each of the four test scenarios, you want to know the annual cash flow, whether Steve will be able to hire a general manager, and whether income from the company surpasses the estimated annuity value.
You will run "what-if" scenarios with the four sets of input values using Scenario Manager. (See Tutorial C for details on using Scenario Manager.) Set up the four scenarios. Your instructor may ask you to use conditional formatting to make sure your input values are proper. Note that in Scenario Manager you can enter noncontiguous cell ranges, such as C19, D19, C20:F20.

The relevant output cells are Annual Income, Enough Income to Hire Manager?, and Income Over Expected Annuity Earnings? from 2018 to 2027. All of these cells are shown in the Summary of Key Results section. Run Scenario Manager to gather the data in a report. When you finish, print the spreadsheet with the input for any of the scenarios, print the Scenario Manager summary sheet, and then save the spreadsheet file a final time.

Information related to above question is enclosed below:

Attachment:- Assignment and data.rar