Project Llama Case Study Assignment: General Overview you will prepare two Proforma Models that depict two different scenarios; a MANAGEMENT CASE and your own MOST LIKELY (BASE) Case. This memo will provide detailed assumptions for the MANAGEMENT CASE, however, you will develop your own Assumptions for the MOST LIKELY case based on your analysis of the companys historical data. The MOST LIKELY case will require a Revenue Build where the Drivers of Revenue will be based on key unit volume and pricing assumptions described in more detail below. Deliverables for Satisfactory Completion of the Case Study:

1. Prepare a detailed Plus/Minus summary page for Project Llama based on your comprehensive review of the Confidential Informatiion Memorandum (CIM) on Moodle. There is no real need to do any additional industry or market research other than what is contained in the CIM and the related Excel files.

1. Use the related Case Study detailed Excel spreadsheets on sales, unit volume, pricing etc. to calculate the historical trends for the following operating metrics: Based on the data and trends, summarize the historical data in chart form and based on the observed trends, develop your detailed assumptions regarding the key revenue assumptions for your MOST LIKELY SCENARIO. Show your historical AND MOST LIKELY case assumptions as it relates to these operating metrics.

1. # of active customers
2. Average Revenue per customer
3. Annual Unit Volume
4. Average Selling Price (ASP) per Unit
5. Total Company Revenues
6. Annual growth rates for units, customers and ASP
7. COGS as % of Sales
8. SG&A as % of Sales
9. CAPEX as % of Sales

1. Prepare a Financial Model for BOTH SCENARIOS which includes the following components:
   1. Historical Balance Sheet and Income Statement for FY 2015-2018. For purposes of this case study, assume 2018 Estimates are ACTUAL results. Refer to the summary historical information below (modified to 000s) and keep this format for your projection scenarios. In other words, prepare the forecasted IS and BS in 000s.
   2. As part of this exercise, please calculate historical financial ratio estimates for A/R Days on Hand, Inventory Days on Hand, and A/P Days on Hand.
   3. Prepare 5 Year PRO-FORMA Three Statement Model (BS, IS and CF) as detailed in Wall Street Prep course materials for the TWO alternative scenarios: Base Case (Most Likely) and MANAGEMENT CASE (detailed assumptions provided below).
   4. In ALL scenarios, please provide an EXHIBIT which summarizes the historical and projected Income Statement and Balance Sheet with related financial ratio Metrics for each scenario, with a detailed explanation of the KEY ASSUMPTIONS used to generate the pro-forma and your specific rationale or justification for why those revised assumptions or metrics were used.
   5. In all cases, first Pro-forma year results will be for 2019 and final year will be for 2023.
   6. Historical results reflect zero tax liability due to Company status as an S Corporation. For ALL Pro-forma scenarios, assume company converts to C corporation and will pay taxes at the Corporation level. Assume a 40% tax rate for all Pro-forma years and account for taxes on the income statement under the income tax line item.
   7. For simplicity, assume annual depreciation levels equal to $35k annually for ALL SCENARIOS, even if CAPEX assumptions are modified. Also assume no changes to profit sharing and deferred revenue liability accounts for the entire pro-forma period.
   8. The CASH cell on your BS should be a formula that equals PY Cash + the CHG in CASH from your CF statement. There is no PLUG number in a three statement model. If pro-forma scenarios result in negative cash balances, it may mean the firm requires additional external financing. In that case add borrowings to Notes Payable. If there is debt on the BS for any year, ASSUME a 5.0% rate of interest in all notes payable, revolver or term loan debt.

Use the following Balance Sheet and Income Statement Information (obtained from CIM) to calculate the required Historical Balance Sheet RATIOS as well as to develop your MANAGEMENT and MOST LIKELY case proforma balance sheets. Keep the $000s format. A small # of changes or adjustments have been made by your instructor so use these exhibits for your historical financial statements as well as this format for your pro-forma models. -2018 cash of $250 reflects dividend of all excess cash to owners & adj. to equity for such distribution - Use the Following Working Capital ASSUMPTIONS for your MANAGEMENT CASE five year proforma - A/R Days on Hand 35 Days for all 5 years - Inventory Days on Hand 465 Days for all 5 years - Accounts Payable Days on Hand 3 Days for all 5 years - Minimum Cash balance $250 Use the following Assumptions for all other Balance Sheet Accounts in your MANAGEMENT CASE - Deferred Revenue hold constant at $111 for all 5 years - Profit Sharing Plan hold constant at $238 for all 5 years - Prepaid Expenses hold constant at $60 for all 5 years - Capital Expenditures (CAPEX) hold constant at $10 for all 5 years -Warehouse, selling and G&A expenses have been adjusted for all non-recurring or discretionary items MANAGEMENT CASE 5 year PROFORMA Income Statement Assumptions: Revenues Assume 2.5% growth per year COGS Assume COGS as a % of Revenues equals 34.6% for all 5 years Warehouse Expenses Assume Annual Growth Rate of 2.5% per years Selling Expenses Assume Selling Expense is 8.0% of Revenue for all 5 years General & Admin Expenses Assume G&A is 12.5% of Revenue for all 5 years Depreciation hold constant at $35 for all 5 years Dividends Assume dividends equal to 40% of Net Income for proforma period For your MOST LIKELY Case Assumptions, you will need to determine them based on your observations of the historical results and trends and provide a detailed explanation for your reasoning behind each key balance sheet and income statement assumption.

Attachments:

project-llama....docx