16 Jul On the Excel Financial Forecast worksheet fill in the information highlighted in red with data from your company.
Please see attached instructions. Throughout the entire course, two companies to analyze, Wicked Good Cupcakes and Cinemark (Cinemark is chosen company)
This week, you will be evaluating the financial performance through ratio analysis of Wicked Good Cupcake company.
See the attached document for complete instructions and grading rubric.
Need this assignment by Sunday 7/16 6 pm (CST Central Standard time, US)
Due Date: Sunday by 11:59 p.m of Unit 3
Total Point: 100
Instructions:
• On the Excel Financial Forecast worksheet fill in the information highlighted in red with data from your company.
• Repeat the formulas in column C row 18-37 in column D. Doing this should allow you to adjust values in the Fact and Assumptions part of column D and see its affect in row 18-27 of the same column. https://www.youtube.com/watch?v=d0GyMLC8bZs
• Using the Excel Financial Forecast worksheet, determine the ‘sensitivity’ of the model by adjusting the values of growth rate in sales and cost of goods sold in column D. Increase and decreases the % values for 2017 and observe and record the results. Create an Excel chart for each (growth rate in sales and cost of goods sold) the resulting impact on external funding required.
• Explain your observations on: o How did adjusting growth rate of sale and cost of good sold affect the income statement and
balance sheet? o What is External Funding Required and how was it affected by adjusting sales projections and cost
of goods sold?
Requirements:
• All questions posed must be addressed completely.
• Length: two-page Executive Summary
• Include the two charts.
• All sources used must be properly cited in APA format.
• Forecast model must be included with the word document.
Be sure to read the criteria, by which your assignment will be evaluated, before you write, and again after you write.
Evaluation Rubric for Sensitivity of Financial Data Assignment
Criteria Exemplary Proficient Deficient
(18-20 Points) (13- 17 Points) (0 – 12 Points)
Growth Rate of Sales Chart
Chart and evaluation are clearly presented, including all key details.
Chart and evaluation are presented, though key details may not be clearly or completely presented.
Chart and evaluation are inadequate or missing.
Cost of Goods Sold Chart and evaluation are clearly presented, including all key details.
Chart and evaluation are clearly presented, including all key details.
Chart and evaluation are inadequate or is missing.
Results Analysis Analysis fully addresses the results of the growth rate in sales and cost of goods sold.
Analysis mostly addresses the results of the growth rate in sales and cost of goods sold.
Analysis is inadequate or missing.
Impact Analysis Analysis fully addresses how the results may affect the external funding.
Analysis mostly addresses how the results may affect the external funding.
Analysis is inadequate or missing.
(9-10 Points) (7-8 Points) (0-6 Points)
Length 2 pages 1 page Less than 1 page or more than 2 pages
Clear and Professional Writing and APA Format
Writing and format are clear, professional, APA compliant, and error-free.
Few errors that do not impede professional presentation.
Errors impede professional presentation; guidelines not followed.
,
WGC Proforma Income
| Wicked Good Cupcakes | ||||||||||
| Pro Forma Income Statement | ||||||||||
| Pro Forma | Estimated | Pro Forma | ||||||||
| Actual Quarter | Quarter | Full Year | Year | |||||||
| Ended 9-30-2017 | Ended 12/31/2017 | Ended 12/31/2017 | Ended 12/31/2018 | |||||||
| Units Sold | 137000 | 205500 | 226050 | 259958 | ||||||
| Sales | 12650 | 13283 | 14611 | 16802 | ||||||
| Cost of Goods Sold | ||||||||||
| Labor | 2210 | 2321 | 2553 | 2935 | ||||||
| Materials | 2045 | 2147 | 2362 | 2716 | ||||||
| Overhead | 5685 | 5969 | 6566 | 7551 | ||||||
| Delivery | 305 | 320 | 352 | 405 | ||||||
| Total CoGS | 10245 | 10757 | 11833 | 13608 | ||||||
| Gross Margin | 2405 | 2525 | 2778 | 3194 | ||||||
| Expense | ||||||||||
| Selling Expense | 875 | 919 | 1011 | 1162 | ||||||
| General & Admin | 585 | 614 | 676 | 777 | ||||||
| Total Expenses | 1460 | 1533 | 1686 | 1939 | ||||||
| Operating profit (EIBT) | 945 | 992 | 1091 | |||||||
| Interest | 190 | 200 | 219 | 252 | ||||||
| Profit before Taxes | 755 | 793 | 872 | 1003 | ||||||
| Income Taxes | 272 | 286 | 314 | 361 | [The first two heading levels get their own paragraph, as shown here. Headings 3, 4, and 5 are run-in headings used at the beginning of the paragraph.] | |||||
| Net Income | 483 | 507 | 558 | 642 | ||||||
| Dividends | 100 | 105 | 116 | 133 | ||||||
| Retained Earnings | 383 | 402 | 442 | 509 | ||||||
| Depreciation | 575 | 604 | 664 | 764 | ||||||
| Net cash after divends | 958 | 1006 | 1106 | 1272 | ||||||
| Add back tax-adjusted interest | 122 | 128 | 141 | 162 | ||||||
| Add back divends | 100 | 105 | 116 | 133 | ||||||
| Cash flow from operations | 1180 | 1239 | 1363 | 1567 |
Sales
12650 13282.5 14610.750000000002 16802.362499999999
Time Period
Sales in $
Total CoGS
10245 10757.25 11832.975 13607.921249999999
Time Period
Cost in $
Pro-Forma Year Ended 12-31-18
WGC Proforma Balance Sheet
| Wicked Good Cupcakes | |||||||||||
| 612/1/201 | Pro Forma Income Statement | ||||||||||
| Actual | Pro Forma | Pro Forma | |||||||||
| Assets | 9/30/17 | Change | Ended 12/31/2017 | Change | Ended 12/31/2018 | ||||||
| Current Assets | |||||||||||
| Cash | 1450 | 5% | 1523 | 10% | 1675 | ||||||
| Accounts Receivable | 4250 | 5% | 4463 | 10% | 4909 | ||||||
| Raw Materials | 1500 | 5% | 1575 | 10% | 1733 | ||||||
| Finished Goods | 4050 | 5% | 4253 | 10% | 4678 | ||||||
| Total Current Assets | 11250 | 5% | 11813 | 10% | 12994 | ||||||
| Fixed Assets | |||||||||||
| Land | 2500 | 5% | 2625 | 10% | 2888 | ||||||
| Plant and equipment | 20800 | 5% | 21840 | 10% | 24024 | ||||||
| Less Accumulated depreciation | 8350 | 5% | 8768 | 10% | 9644 | ||||||
| Net plant and equipment | 12450 | 5% | 13073 | 10% | 14380 | ||||||
| Total Fixed Assets | 14950 | 5% | 15698 | 10% | 17267 | ||||||
| Other Assets | 1250 | 5% | 1313 | 10% | 1444 | ||||||
| Total Assets | 27450 | 5% | 28823 | 10% | 31705 | ||||||
| Liabilities and Net Worth | |||||||||||
| Current Liabilities | |||||||||||
| Accounts Payable | 1120 | 5% | 1176 | 10% | 1294 | ||||||
| Notes Payable | 3000 | 5% | 3150 | 10% | 3465 | ||||||
| Due Contractor | 3400 | 5% | 3570 | 10% | 3927 | ||||||
| Accrued taxes | 1250 | 5% | 1313 | 10% | 1444 | ||||||
| Total current liabilities | 8770 | 5% | 9209 | 10% | 10129 | ||||||
| Long-term liabilities | 8500 | 5% | 8925 | 10% | 9818 | ||||||
| Common Stock | 4250 | 5% | 4463 | 10% | 4909 | ||||||
| Retained earnings | 5930 | 5% | 6227 | 10% | 6849 | ||||||
| Total Liabilities and net worth | 27450 | 5% | 28823 | 10% | 31705 | ||||||
| Funds required |
Charts
Sales
12650 13282.5 14610.750000000002 16802.362499999999
Time Period
Sales in $
Total CoGS
10245 10757.25 11832.975 13607.921249999999
Time Period
Cost in $
,
Data
| Order Details for December 2016 | |||||||||||||||||||
| Order ID | Order Date | Customer ID | Address | City | State | Country | Salesperson | Region | Shipped Date | Shipper Name | Ship Name | Ship Address | Ship City | Ship State | Flavor | Unit Price | Quantity | Revenue | Shipping Fee |
| 1389 | 12/01/16 | 1 | 123 1st Street | Seattle | WA | USA | Nancy Freehafer | North | Anna Bedecs | 123 1st Street | Seattle | WA | Lemon | $5.95 | 64 | $380.80 | $118.66 | ||
| 1390 | 12/02/16 | 1 | 123 1st Street | Seattle | WA | USA |
