Financial Modeling, Planning, and Decision Analysis
Part I: Financial Planning Model
Use Excel to complete the financial planning model IN GOOD FORM - INCLUDING APPROPRIATE HEADINGS AND EASY TO FOLLOW SCHEDULES. ALL case assumptions and other information necessary to complete the financial model should be integrated into the model as explained below. To complete the financial planning model, it will be necessary to review the exhibits in Chapter 9 of the Noreen et al. text. ONLY HARDCODE INPUT ASSUMPTIONS IN THE INPUT SECTION.Finally, be sure to save your work OFTEN as you complete the template. A template to get you started accompanies this document on D2L. Complete the financial planning model on a monthly and quarterly basis for the three-month period ending September 30, 2018.
IMPORTANT: You MUST use formulas throughout the spreadsheet- DO NOT HARDCODE IN FORMULAS. Strive to attain computational efficiency with formula construction. For example, if you are able to construct a formula with 3 references rather than 5, then use fewer references. Likewise, reference intermediate computations (e.g., from your Schedules Section) rather than reconstructing formulas. Finally, be sure to use absolute and relative row/column references correctly so that you can simply copy and paste formulas as appropriate. Your financial planning model template contains three key areas as follows:
(1) Assumptions
The Assumptions contains all of the base assumptions presented in the case, which includes the information within each exhibit as well as the other information embedded in the case paragraphs. Any changes and/or revisions to the case for sensitivity and what-if analysis will be made here. You must also use Goal Seek in Excel, which is Excel's "what-if" analysis tool. DO NOT use trial and error - rather,useGoal Seek for all what-if analysis.See Goal Seekdocument on D2L.
(2) Schedules
Complete the sales and merchandise purchase plans with supporting schedules:
a) A sales plan by month and in total, including a schedule of projected cash collections from sales and accounts receivable, by month and in total.
b) An inventory purchases plan in units and in dollars, including a schedule of projected cash payments for purchases, by month and in total.
NOTE: All schedules and budgets should be calculated based on information entered into the (1) Assumptions and other schedules.No hardcoding of numbers should be included on these schedules. All schedules should automatically update given changes to theassumptions.
(3) Projected Statements
Complete the following projected financial statements:
1) A projected contribution format income statement by month and in total. Do NOT include interest expense as a variable or fixed expense. Include interest expense (if any) after the operating income subtotal. The contribution income statement should be shown as follows:
Sales $
Less: Variable expenses (show line by line detail)
Contribution margin
Less: Fixed expenses (show line by line detail)
Operating income
Less: Interest expense (if any)
Net income before income taxes $
2) A projected statement of cash by month and in total.
3) A projected balance sheet as of September 30, 2018.
NOTE: All financial statements above should be calculated based on information entered into (1) Assumptions and computations made from (2) Schedules and (3) Projected Statements. No hardcoding of numbers should appear on the projectedstatements. All projected statementsshould automatically update (and balance) given changes to (1) Assumptions. BALANCE SHEET SHOULD ALWAYS BALANCE!
Hint: For July and August, compute the line-of-credit interest expense and then link (with a one month lag) to interest payments on the projected statements of cash (or vice versa). For September, compute interest expense and link forward to interest payable on the projected balance sheet.
Part II: Decision Making and Analysis
Results from Part I provide the Baseline Scenario for Part II analyses. Refer to the Baseline Scenario and, unless stated otherwise, consider each situation incrementally from the baseline (with all other assumptions remaining unchanged). Answer the following questions and SAVE YOUR RESULTS as a new worksheet within your Excel file and label the worksheet tab appropriately. See guidelines that follow Part II.
A. Cost-Volume-Profit Analysis
1. PDA's market researcher suggests that the product market is becoming highly competitive. Due to economic conditions and competitive pricing, sales prices must be reduced.
a) Begin with the Baseline Scenario (This would be a perfect time to make a copy of the Baseline model worksheet, label the tab "A.1.a." and complete the analysis on the newly created worksheet. Always keep the Baseline model clean of analysis so you always have it to come back to.) - By what overall percentage may PDA lower the unit selling prices every month for both products and still maintain breakeven operating income for the quarter? (Hint: you need to revise cell F18, which will change the Sales Price Sensitivity row (use Goal Seek). Confirm that you obtained breakeven operating income with the price reduction and save your file and analyze your results. Explain your results and what information does this result provide to management?
b) Given the price reduction above (new worksheet "A.1.b."), compute the breakeven point in total units and the number of units for each product model for the month of September only, (consider all variable costs). Make this computation on a new worksheet and save your file. Explain your results and what information does this result provide to management?
c) Given your results in A.1.a., how much is the line-of-credit balance on September 30 with the price reduction? (Copy A.1.a worksheet and create A.1.c.) Assuming the price reduction, determine the percentage change in unit volume that is required to achieve a "breakeven cash flow" for the quarter, where the Line of Credit Balance is exactly $0. (Hint: you should obtain a percentage increase or decrease for the Unit Volume Sensitivity result at theAssumptions(useGoal Seek). Save your Excel file andexplain/interpret your results. What information does this result provide to management?
2. Ms. McMillan is disappointed with the baseline income projection. Laura Sweet suggests that an advertising campaign (cost of $50,000 per month), combined with increases in sales commissions (from 5% to 8%) can improve unit sales and overall profitability.
a) Begin with the Baseline (new worksheet A.2.a.) - incorporate Ms. Sweet's assumptions and then determine by what percentage must unit sales increase monthly, and in total, to achieve quarterly operating income of $1,000,000? (Hint: you should obtain a percentage increase or decrease for the Unit Volume Sensitivity result at the Assumptions (use Goal Seek).Save your file and analyze your results. Explain your results and determine the feasibility of this result. What information does this result provide to management?
b) Continue with the revised marketing assumptions (copy your A.2.a. worksheet and label it A.2.b.) - but,in addition, shift the sales mix per month from BASE to PREM by an additional 5% per month. For example, the Sales Mix Sensitivity for July should be 2% + 5% =7%, August should = 8%, etc. Given this shift, by what percent must unit sales increase each month, and in total, to achieve quarterly, operating income of $1,000,000? (Hint: You should obtain a percentage increase or decrease for the Unit Volume Sensitivity result at the Assumptions(use Goal Seek).Save your file and analyze your results. Explain your results, feasibility, and what information does this result provide to management?
c) Return to the Baseline (copy the Baseline and label it A.2.c.) - suppose that the PREM product does not achieve sales growth expectations, but instead the sales mix remains constant at 80% (BASE) and 20% (PREM) throughout the projection period. Determine the differential (change in) operating income and line-of-credit balance relative to the original (baseline), sales mix assumptions. Create a chart showing the original versus the revisions.Save your file.Discuss the impact of this change on your results. Provide valid, sound reasoning for the changes obtained, i.e. how did they occur?
3. Now, assume that PDA's supplier experiences an increase in the cost of the special flash memory chip required by the premium model. The supplier has indicated that it will pass along the costs to PDA as an increase of $21.00 per PREM model, beginning in July.
a) Begin with the Baseline (new worksheet A.3.a.) - What is the projected operating income for the quarter, and ending line-of-credit balance, assuming that the cost increase materializes? Save your final and analyze your results. Explain your results and what information does this result provide to management?
b) By what percentage must unit sales volume increase each month and in total to maintain the same level of quarterly operating profit as before the cost increase (new worksheet A.3.b.)? Use GOAL SEEK.Save your file and analyze your results. Explain your results and what information does this result provide to management?
c) Could PDA achieve the baseline operating income (as computed before the cost increase) by ONLY shifting sales mix from BASE toward PREM - without increasing total unit sales volume (new worksheet A.3.c.)? If so, what equal, monthly sales mix shift is required given the cost increase? (Hint: you should obtain a percentage increase or decrease for the Overall Sales Mix Sensitivity result at the Assumptions(use Goal Seek).Save yourfile and explain/interpret your results (i.e. what information does this result provide to management?). Note: all months should reflect the same percentage shift in sales mix percentage.
B. Income vs. Cash Flow and Benchmarking Analysis
1. Refer to the Baseline - projected income statement and statement of cash.
a) Compute PDA'sQuality of Income.Formulate this computation into the Baseline model near or with the benchmarking ratios instead of creating a new worksheet.
Where, Operating Cash Flow = Cash Collections - Cash Disbursements before Capital Acquisitions
Interpret this result and what information does this result provide to management?
b) Explain why income is greater than cash flow and identify at least two specificareas in which PDA may not be effectively using cash (focus on working capital components).
c) Discuss other business risks that could affect PDA's financial strength. Consider outside sources or former courses to define and identify what business risks are. Explain in detail. This discussion should be rich and solid.
2. Refer to the Baseline and review the selected benchmarking information in Exhibit 4.
a) Compute PDA'sfinancial metrics for the projected quarter. Per initial instructions, you should have formulated the green area beside the Assumptions after completing the initial model results.
(Hint: Be sure to annualize Inventory Turnover and Asset Turnover.)
b) Compare and assess how well PDA's expected performance across the measures rates with respect to its key competitor and the industry average? Explain each result in detail.
3. Begin with the Baseline Scenario
a) (New worksheet B.3.a.) Recall that the baseline assumption for the percentage of inventory carried by PDA equals 75 percent of next months' sales. Determine the percentage of ending inventory carried by PDA that is required to reach the key competitor level of inventory turnover.Describe the effect on the ending line-of-credit (or cash) balance (useGoal Seek). Save your file and analyze your results. Explain this result and what information does this result provide to management?
b) Begin with the Baseline again(new worksheet B.3.b.)- assume that 50% of sales are still collected in the month following sale. What shift in the receivables collections pattern (the percentage collected in the month of sale) is required to achieve the key competitor days' sales in ending accounts receivable metric (28 days)? Determine the specific dollar effect on PDA's ending line-of-credit (or cash) balance if PDA achieves key competitor performance on the receivables metric (use Goal Seek). Save your file and analyze your results. Explain this result. What information does this result provide to management?
C. Overall Analysis and Recommendations
a) Given all of the analysis completed above, what is your recommendation to Ms. McMillan regarding PDA, Inc.? Provide a recommendation and justify it.BEFORE YOU DO... the easy way out is to choose one of the ideas presented by one of the members ofPDA management. However, this will not earn you an above average grade. Note that companies should not hire outside consultants to agree with one of their ideas. Consultants are expected to make sound recommendations that provide the BEST opportunities for the company to succeed. Therefore, to have an opportunity to earn a top grade, I strongly suggest that your team use the model to develop your own course of action and recommendations. Think about what makes sense, what might be too risky; what might be most economical in the short-term, what might be best long-term, etc. Combine any ideas together to come up with your best recommendation(s) for Ms. McMillan.
Any recommendations you proposemust be supported with financial model results and documentation on new worksheets, properly labeled. A recommendation without model results = a worthless recommendation. Support your recommendations with results. Your results must be discussed in your recommendation. PDA management doesn't care what you ‘believe,' or what you ‘think' - don't write that way. Write saying "we recommend based on our analysis."Remember, you're a team, thus, write your analysis as such. PLEASE DO NOT USE "I" WITHIN ANY "TEAM" SUBMISSIONS.
The BEST projects are those where teams step out of the box and identify courses of actions that may or may not use portions of the ideas above, might combine ideas above, and definitely consider and develop new ideas (assuming the results are reasonable, feasible AND the model continues to provide accurate results).
b) Final Wrap Up Section:
i) Identify any deficiencies in the model relations of input parameters (i.e. are there any model calculations that may not realistically reflect expected changes?) and comment on possible needed changes or revisions.
ii) Discuss other ways in which PDA managers might use this financial model (i.e. indicate possible analyses that managers might be interested in performing.)
Attachment:- PROJECT AND TEMPLATE.rar