Excel
SL18
IncomeTemplateL3Start.xlsxDocumentation
| TheZone - Sunglasses Products | ||||
| Income Projections | ||||
| Created by: | Kiola Taylor | On: | 15-Feb-17 | |
| Last Updated by: | Kiola Taylor | On: | 19-Feb-17 | |
| Purpose of workbook: | This workbook was created to estimate the income information, including EPS, for TheZone's sunglasses line | |||
| Worbook contents: | The workbook contains worksheets for Projected Income, SalesHistory and Data. Sales history is provided by the sales group listing sales volumes by salesperson by item. The Data worksheet contains a list of each unique item sold for the specified product line and summarizes sales and allocates payroll, advertising and rent based on % of sales dollars. The Projected Income worksheet takes data from the SalesHistory and Data worksheets based on the item specified by the user. The user may also specify Total in cell B4 - to obtain overall product line data. | |||
| Assumptions | The projected Income Statement model calculates COGS as a percentage of sales, instead of on a per unit basis. All other assumptions relate to input values and are apparent from the assumptions section of the Projected Income worksheet itself. | |||
| How to use this workbook: | Choose the name of the item on the Projected Income worksheet in cell B4 from the drop-down list to view its projected income data. The user must input valid values for COGS.Tax rate and Average number of common shares outstanding. The formulas use SUMIF functions to automatically determine total volumes and allocated payroll, advertising and rent. VLOOKUP functions retrieve unit pricing and the allocated dollars expenses. |
Projected Income
| TheZone - Sunglasses Products | ||||
| Projected Income Statement | ||||
| For the Period Ended Dec 31, 2016 | ||||
| Item: | Eton High Bend | |||
| Assumptions: | Projections: | |||
| Unit sales volume | 2295 | Sales | $ 208,845 | |
| Unit price | 91 | COGS | 62,654 | |
| Cost of goods sold (COGS) | 30% | Gross profit | 146,192 | |
| Payroll | $ 4,223.00 | Payroll | 4,223 | |
| Advertising | $ 6,335.00 | Advertising | 6,335 | |
| Rent | $ 1,689.00 | Rent | 1,689 | |
| Tax rate | 35.00% | Pretax income | 133,945 | |
| Average number of | Est. income tax expense | 46,881 | ||
| shares outstanding | 200,000 Guest1: Kiola: Average number of shares is used to compute EPS | Net income | $ 87,064 | |
| EPS | $ 0.44 |
SalesHistory
| TheZone - Sunglasses Products | |||||||
| Sales Volume History | |||||||
| For the Period Ended Dec 31, 2016 | |||||||
| Product | Salesperson | Qtr 1 | Qtr 2 | Qtr 3 | Qtr 4 | Total Volume | Total Value |
| XLS Racing Goggles | ANTON | 8 | 311 | 261 | 929 | 1,509 | $ 82,995 |
| Univisor Sports 333 | BERGS | 621 | 130 | 80 | 945 | 1,776 | 3,552 |
| SkiOptics 730 | BOLID | 385 | 232 | 115 | 924 | 1,656 | 167,256 |
| K-Twenty | BOTTM | 40 | 726 | 840 | 1,606 | 133,298 | |
| Litton Q30 | ERNSH | 199 | 21 | 850 | 1,070 | 34,240 | |
| Bascom Optics | GODOS | 637 | 677 | 330 | 983 | 2,627 | 123,469 |
| GlareFree 225 | HUNGC | 74 | 875 | 117 | 747 | 1,813 | 193,991 |
| Eton High Bend | PICCO | 966 | 248 | 832 | 249 | 2,295 | 208,845 |
| Univisor Sports 333 | RATTC | 281 | 969 | 887 | 635 | 2,772 | 5,544 |
| XLS Racing Goggles | REGGC | 683 | 757 | 102 | 417 | 1,959 | 107,745 |
| Bascom Optics | SAVEA | 871 | 857 | 667 | 35 | 2,430 | 114,210 |
| Litton Q30 | SEVES | 700 | 15 | 471 | 1,186 | 37,952 | |
| Litton Q30 | WHITC | 803 | 944 | 584 | 494 | 2,825 | 90,400 |
| Univisor Sports 333 | ALFKI | 575 | 270 | 153 | 324 | 1,322 | 2,644 |
| K-Twenty | BOTTM | 874 | 685 | 658 | 360 | 2,577 | 213,891 |
| Bascom Optics | ERNSH | - 0 | 514 | 739 | 394 | 1,647 | 77,409 |
| Litton Q30 | LINOD | 631 | 65 | 209 | 297 | 1,202 | 38,464 |
| Univisor Sports 333 | QUICK | 756 | 429 | 983 | 949 | 3,117 | 6,234 |
| XLS racing goggles | VAFFE | 881 | 90 | 313 | 1,284 | 70,620 | |
| GlareFree 225 | ANTON | 378 | 38 | 797 | 987 | 2,200 | 235,400 |
| GlareFree 225 | BERGS | 258 | 535 | 857 | 39 | 1,689 | 180,723 |
| Bascom Optics | BONAP | 725 | 369 | 3 | 808 | 1,905 | 89,535 |
| Bascom Optics | BOTTM | 83 | 510 | 485 | 282 | 1,360 | 63,920 |
| Univisor Sports 333 | BSBEV | 986 | 639 | - 0 | 889 | 2,514 | 5,028 |
| XLS Racing Goggles | FRANS | 667 | 376 | 917 | 556 | 2,516 | 138,380 |
| XLS Racing Goggles | HILAA | 25 | 250 | 203 | 328 | 806 | 44,330 |
| Univisor Sports 333 | LAZYK | 771 | - 0 | 3 | 415 | 1,189 | 2,378 |
Data
| Sales Summary by Item | |||
| Items: | Unit Price | Total Sales $ | % of Total |
| Bascom Optics | 47 | $ 468,543 | 19% |
| Eton High Bend | 91 | 208,845 | 8% |
| GlareFree 225 | 107 | 610,114 | 25% |
| K-Twenty | 83 | 347,189 | 14% |
| Litton Q30 | 32 | 201,056 | 8% |
| SkiOptics 730 | 101 | 167,256 | 7% |
| Univisor Sports 333 | 2 | 25,380 | 1% |
| XLS Racing Goggles | 55 | 444,070 | 18% |
| Total | $ 2,472,453 | 100% | |
| Total Division Expenses: | |||
| Payroll | $ 50,000 | ||
| Advertising | $ 75,000 | ||
| Rent | $ 20,000 |
Selling Expenses
| Sunglasses Products -- Budgeted Selling Expenses for 2016 | ||||||||||
| January | February | March | ||||||||
| Type | Rate | Fixed | Variable | Total | Fixed | Variable | Total | Fixed | Variable | Total |
| Salaries | $ 3,000 | $ 3,000 | $ 3,000 | $ 3,000 | $ 3,000 | $ 3,000 | ||||
| Commissions | 3% | - 0 | 2,925 | 2,925 | - 0 | 3,094 | 3,094 | - 0 | 3,287 | $ 3,287 |
| Travel | 2% | - 0 | 1,950 | 1,950 | - 0 | 2,063 | 2,063 | - 0 | 2,191 | $ 2,191 |
| Advertising | 1% | - 0 | 975 | 975 | - 0 | 1,031 | 1,031 | - 0 | 1,096 | $ 1,096 |
| Depreciation | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | $ 1,000 | ||||
| Miscellaneous | 800 | 800 | 800 | 800 | 800 | $ 800 | ||||
| Total selling expenses | $ 4,800 | $ 5,851 | $ 10,651 | $ 4,800 | $ 6,188 | $ 10,988 | $ 4,800 | $ 6,574 | $ 11,374 | |
| Statistics | ||||||||||
| Average monthly expense | $ 11,004 | |||||||||
| Max expense | $ 11,374 | |||||||||
| Min expense | $ 10,988 |
