finance

MODELOFF FINANCIAL MODELING WORLD CHAMPIONSHIPS 2012 Modeloff 2013 Questions and Answers Required Calculations: 1. Calculate the Widget sales for CY2014, CY2015 and CY2016. NOTE: The percentage increase is based on the previous calendar year, not the original year Example: CY17 sold 100 units, CY18 was an increase of 4%, and CY 19 was an increase in sales of 6%. Sales in CY18 = CY17 sales of 100 units X 1,04 = 104 units sold Sales in CY19 CY18 sales of 104 units X 1.06 = 110 units sold 2. Using the data under current sales price and margin, determine the contribution margin and the variable costs per unit. Formula is Contribution Margin = Sales price - Variable costs Variable costs - Sales price - contribution margin Variable costs = sales price - (contribution margin % x Sales price per unit) 3. Calculate the indirect costs for each calendar year using the same process as with the calculation for Widget Sales. 4. Calculate the cash receipts using the percentage breakdown provided Example: X Company had $100,000 of sales in January. In the month and 10 of revenues are collected: 50% of revenues are U process as with the calculation for Widget Sales 4. Calculate the cash receipts using the percentage breakdown provided Examplo: X Company had $100,000 of sales in January. In the month sold, 10% of revenues are collected; 50% of rovenues are collected in the second month and 40% of sales are collected in the third month. How much is collected from revenues in each month? Month 1 (aka month of sale) = $100,000 x 10% = $10,000 Month 2 = $100,000 x 50% = $50,000 Month 3 = $100.000 x 40% = 540,000 With each new month of sales, the breakdown will be the same percentage for months 1, 2 and 3. Add that breakdown to the previous revenue collection cycles to get the total revenue for that month 5. Calculate the Cash Receipts on Opening Receivables using the method shown in 4 6. Calculate the Cash on Payments on Purchases using the method shown in #14 7. Calculate the Cash Payments on Opening Payables using the method shown in 14 8. Calculate interest payments using simple interest, as outlined below. Formula for Simple Interest - Principlex rate x time Simple Interest - 2,000,000 x 7% x # of years = total interest for life of the loan So you can assume that the interest por yoar is 2,000,000 x 796 Annual interest will then - 2,000,000 x 7% 140,000 per year $140,000 per year / 12 months = $11,666.67 per month interest Calculate the Cash Payments on Opening Payables using the method shown in #4 8. Calculate interest payments using simple interest, as outlined below. Formula for Simple Interest = Principle x rate x time Simple Interest = 2,000,000 x 7% x # of years = total interest for life of the loan So you can assume that the interest per year is 2,000,000 x 7% Annual interest will then = 2,000,000 x 7% = 140,000 per year $140,000 per year / 12 months = $11,666.67 per month interest 9. To calculate any monthly costs from an annual cost divide the annual costs by 12 months, UNLESS a percentage breakdown is provided within the data. Then use the percentage breakdown provided. 10. Using the collected data, complete a cash flow statement broken out by month for each quarter, for the period of CY2013 - Q4 through CY2016 - Q3 11. Using the collected data, complete an income statement broken out by month for each quarter, for the period of CY2013 - Q4 through CY2016 -Q3. 12. Using the collected data, complete a balance sheet broken out by each year for September 30th