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. In Assignment 3, you will prepare and give an oral presentation of your analysis and conclusions to Steve.
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:
• Summary of Key Results
A discussion of each section follows. The spreadsheet skeleton for this case is available for you to use; it will save you time. To access the spreadsheet skeleton, go to your data files, select Case 6, and then select
Your spreadsheet should include the constants, otherwise known as assumptions, shown in Figure 6-1. An explanation of the line items follows the figure.
• Prices—These prices are based on averages that Steve provided.
• Rough Yard Work per Square Foot
• Gutter Cleaning per Linear Foot
• Power Washing per Square Foot
• Lawn Mowing and Edging per Square Foot
• Driveway Seal Coating per Square Foot
• Fall Leaf Clearing per Square Foot
• Snow Removal per Square Foot
• Costs—The average cost of labor and materials is based on averages Steve provided from previous years.
• Customer Base—These values show the company’s current number of customers and average lot coverage areas for various company services. Most of these averages are shown in square footage (Sq Ft).
• Customers—This value shows the number of customers currently served by the company.
• Average Lawn Surface (Sq Ft)
• Average Power Washing Surface (Sq Ft)
• Average Gutter Length (Linear Ft)
• Average Snow Removal Surface (Sq Ft)
• Average Driveway Seal Coating Surface (Sq Ft)
• Average Fall Leaf Clearing Surface (Sq Ft)
• Economic and Environmental Factors—Based on conversations with his accountant, Steve feels comfortable using a 25 percent tax rate for the model.
As Steve explained earlier, he would like to answer some important questions to determine his best option for retirement. First, the model needs to evaluate the impact of the loan on the customer base’s growth. Second, the model needs to evaluate the repayment of the loan if it is approved. Finally, Steve has different ideas on how much money he will need to retire comfortably; he thinks an amount between $75,000 and $100,000 annually would be sufficient. The DSS will determine whether these options are viable.
Your spreadsheet should include the following inputs, as shown in Figure 6-2. Note that the spreadsheet extends to 2027, as explained earlier, but the remaining figures in this case have been cropped to fit the page.
• Customer Base Change %—This value is the expected change in the size of the customer portfolio. The value could be positive, negative, or zero starting in 2017.
• Annual Payments For Loan—The bank’s loan officer has provided an estimate of an annual total payment of $120,000 for a loan of $1 million with a 3 percent interest rate over 10 years.
• Annual Income Required For Retirement—This value represents what Steve is willing to accept as annual retirement income.
Summary of Key Results Section
Your spreadsheet should include the results shown in Figure 6-3. A general explanation of this section follows
For each year starting in 2017, this section should include values that are already calculated elsewhere in the spreadsheet. The formulas in the Summary of Key Results section will echo results from throughout your model; no long or complicated formulas need to be used in this section. The purpose of gathering the results together is to make for an easier job when configuring Scenario Manager later.
To create an accurate decision tool, you should calculate intermediate results that will be used to determine the year-end numbers needed for the model. It is generally a good idea to arrive at these final numbers in a series of steps rather than in one short calculation. Errors are easier to identify if the steps are broken out, and it also makes troubleshooting a breeze. The calculations shown in Figures 6-4, 6-5, and 6-6 are based on 2017 values in the Constants section (Customer Base values and prices); starting in 2018, the calculations take into account the inputs from each scenario. When called for, use absolute referencing properly. Values must be computed by cell formula; hard-code numbers in formulas only when you are told to do so. Cell formulas should not reference a cell with a value of “NA.”
An explanation of each item in this section follows the figure in which the item is shown.
• Rough Yard Work—The product of the average lawn surface and the number of customers. Steve tells you that only 25 percent of customers request this service. Format cells for numbers with zero decimals.
• Lawn Mowing and Edging—The product of the average lawn surface and the number of customers. Steve tells you that only 25 percent of customers request this service. Format cells for numbers with zero decimals.
• Power Washing—The product of the average power washing surface and the number of customers. Format cells for numbers with zero decimals.
• Gutter Cleaning—The product of the average gutter length and the number of customers. Format cells for numbers with zero decimals.
• Snow Removal—The product of the average snow removal surface and the number of customers. Format cells for numbers with zero decimals.
• Driveway Seal Coating—The product of the average driveway seal coating surface and the number of customers. Format cells for numbers with zero decimals.
• Fall Leaf Clearing—The product of the average lawn surface and the number of customers. Steve tells you that 75 percent of customers request this service. Format cells for numbers with zero decimals.
• Rough Yard Work—Total expected revenue multiplied by the unit price in the Constants section. Format cells for currency with zero decimals.
• Lawn Mowing and Edging—Total expected revenue multiplied by the unit price in the Constants section. Format cells for currency with zero decimals.
• Power Washing—Total expected revenue multiplied by the unit price in the Constants section. Format cells for currency with zero decimals.
• Gutter Cleaning—Total expected revenue multiplied by the unit price in the Constants section. Format cells for currency with zero decimals.
• Snow Removal—Total expected revenue multiplied by the unit price in the Constants section. Format cells for currency with zero decimals.
• Driveway Seal Coating—Total expected revenue multiplied by the unit price in the Constants section. Format cells for currency with zero decimals.
• Fall Leaf Clearing—Total expected revenue multiplied by the unit price in the Constants section. Format cells for currency with zero decimals.
• Total Revenue—The sum of all revenues for the year. Format cells for currency with zero decimals.
• Labor and Materials—Based on current estimates, the annual expense for labor and materials is $500,000. This number will need to be updated based on customer base changes starting in 2018. Format cells for currency with zero decimals.
• Loan Repayment—Repayment would start in 2018 if the loan offer is approved. Format cells for currency with zero decimals.
• Total Expense—The sum of labor and materials and the loan repayment. Format cells for currency with zero decimals.
• Income Before Taxes—The difference between total revenue and total expense. Format cells for currency with zero decimals.
• Tax Expense—The tax liability based on the tax rate in the Constants section. Format cells for currency with zero decimals.
• Net Income—The difference between net income before taxes and tax expense. Format cells for currency with zero decimals.
• Enough Income to Hire Manager?—Starting in 2018, if the difference between net income and the amount required to retire is over $50,000, enter “Yes.” Otherwise, enter “No.”
• Income Over Expected Annuity Earnings?—Starting in 2018, if net income is greater than the estimated annuity value ($100,000), enter “Yes.” Otherwise, enter “No.”
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.
Documenting Your Results in a Memo
Use Microsoft Word to write a brief memo that documents your analysis and results. You can address the memo to Steve, the owner of Philly Landscaping. Observe the following requirements:
• Set up your memo as described in Tutorial E.
• In the first paragraph, briefly state the business situation and the purpose of your analysis.
• Next, describe the scenarios tested.
• State your conclusions.
• Support your statements graphically, as your instructor requires. Your instructor may ask you to return to Excel and copy the results of the Scenario Manager summary sheet into the memo. You should include a summary table built in Word based on the Scenario Manager summary sheet results. (This procedure is described in Tutorial E.)
• Your table should have the format shown in Figure 6-7.
GIVING AN ORAL PRESENTATION
Your instructor may ask you to explain your analysis and results in an oral presentation. If so, assume that Steve wants the presentation to last 10 minutes or less. Use visual aids or handouts that you think are appropriate. See Tutorial F for tips on preparing and giving an oral presentation.
SortMyEssay.com is an online academic writing platform that provides customers with high-quality papers, regardless of their academic level or the country they come from. Founded in 2012, SortMyEssay.com has quickly become one of the leading providers of quality academic writing services across the globe. It is well known for its exceptional customer service and its dedication to providing only top-notch content to its customers.
At SortMyEssay.com, we understand that not all students are on the same academic level – some may be just starting out, while others may have already completed a number of degrees in various disciplines – so our team strives to provide essays that meet each student’s individual needs. Our writers have been specially trained to write according to different levels of students and also customize each essay according to the client’s specific requirements and instructions given by their professor/teacher/lecturer etc., thus ensuring accuracy and quality at all times.
When it comes to undergraduate (UG) essays, our writers focus on topics such as Accounting, Biology, Computer Science, Economics, English Literature and Language Arts among many others; Masters’ (MA) essays usually require profound knowledge about a particular subject matter or topic with respect to which our experts are well versed; Doctoral (PhD) essays call for original research work based on primary sources; Postgraduate (PG certificate/diploma) essays are more focused on extensive secondary source review focusing on areas such as Business Administration or Human Resource Management; And finally Professional Certification courses requires up-to-date knowledge about current developments & trends within a particular field like Project Management or Quality Assurance etc.. Such broad range allows us to accommodate almost any request made by our esteemed customers who can rest assured knowing their essay will be written perfectly tailored towards their current educational goals & aspirations!
Our commitment towards excellence doesn’t stop at just meeting your individual needs but extends even further when it comes countries served – SortMyEssay covers nearly every country around the world including USA , UK , Canada , Australia , Germany , France among other countries making sure there’s nothing stopping you from achieving success! Our staff consists of professional writers committed towards delivering top notch content while keeping prices affordable enough so everyone can benefit from them no matter what part of world they live in !
Furthermore we ensure strict adherence towards deadlines set forth by clients so you don’t miss any important milestones along your way . We believe time is valuable & should not be wasted trying to figure out how produce high quality paper without breaking bank – which is exactly why we turn impossible into possible & always deliver ahead schedule !
All in all we strive hard everyday make sure none our clients ever disappointed with services provided here at sortmyessay . With expert assistance available round clock guarantees fast turnaround time helping them secure good grades . As one biggest players industry today you can rely us full confidence knowing your assignment safe hands . So go ahead contact now get started journey success !
At SortMyEssay.com, we understand that quality is the key to successful academic writing and this is why all of our papers are written by experienced professionals who have extensive knowledge in their respective fields. We also ensure that each paper goes through multiple levels of review before being delivered to the customer, which includes a rigorous plagiarism check as well as editing for grammar mistakes & typos. This guarantees that only the best-written papers make it into your hands!
Although essays are our specialty, we do not limit ourselves to just this service – our writers can assist with any type of academic writing you may require such as term papers, research papers, coursework assignments & even dissertations so don’t hesitate contact us if you need help completing more complex tasks!
We take pride in delivering quality services to all clients at an affordable price and believe that everyone deserves access to high-quality content regardless of their location or budget. Our commitment towards excellence has earned us accolades from customers around world making us one best online academic writing platforms available today !
So what are you waiting for ? Get started now and experience the power behind sortmyessay ! You won’t regret it