CIS Excel assignment
Configuration
| Configuration (Decision) Variables | Values | Descriptions |
| Sales Tax Rate | Marketing - Sales Tax (%) to be collected per sale | |
| Cost of Goods Sold | Marketing - COGS per unit as percentage of sales price | |
| Employees Threshold | HR - Maximum # of units sale each employee can handle per month | |
| Supervisor Threshold | HR - Maximum # of employees each supervisor can manage per month | |
| Manager Threshold | HR - Maximum # of supervisors each manager can manage per month | |
| Payroll Tax Rate | HR - Tax rate to employers based on payroll expense (employee salaries) paid to government | |
| Credit Card vs Cash Ratio | Accounting - Percentage of customers using Credit Card to pay for the purchase instead of Cash for each month | |
| Overhead Cost Ratio | Accounting - Percentage of overhead (rent, untility, insurance, equipments, etc.) off the total sales revenue excluding sales tax |
Marketing
| Decision Variables | ||||||||||||
| Products | Unit Price | Unit COGS | Ratio b/w Products | |||||||||
| Iphone X | ||||||||||||
| Iphone 8 Plus | ||||||||||||
| Iphone 8 | ||||||||||||
| iWatch Series 3 | ||||||||||||
| Sales (units) Projection | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
| Target Units (Meals) | ||||||||||||
| Projected Revenue | ||||||||||||
| Iphone X | ||||||||||||
| Iphone 8 Plus | ||||||||||||
| Iphone 8 | ||||||||||||
| iWatch Series 3 | ||||||||||||
| Total Revenue | ||||||||||||
| Projected COGS | ||||||||||||
| Iphone X | ||||||||||||
| Iphone 8 Plus | ||||||||||||
| Iphone 8 | ||||||||||||
| iWatch Series 3 | ||||||||||||
| Total COGS | ||||||||||||
| Gross Profit | ||||||||||||
| Sales Tax |
HR
| Decision Variables | |||||||||||||
| Employee Positions | Annual Salary | ||||||||||||
| President | |||||||||||||
| Manager | |||||||||||||
| Supervisor | |||||||||||||
| Staff | |||||||||||||
| Number of Each Position on Payroll | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | |
| President | |||||||||||||
| Manager | |||||||||||||
| Supervisor | |||||||||||||
| Staff | |||||||||||||
| Monthly Payroll Expense by Position | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | |
| President | |||||||||||||
| Manager | |||||||||||||
| Supervisor | |||||||||||||
| Staff | |||||||||||||
| Total Payroll Expense | |||||||||||||
| Payroll Tax |
Accounting
| Decision Variables | |||||||||||||
| Startup Investment | |||||||||||||
| Revenue | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | |
| Beginning Cash Balance | |||||||||||||
| Last Month Credit Card Intake | |||||||||||||
| Current Month Cash Intake | |||||||||||||
| Total Cash Onhand | |||||||||||||
| Operational Expense | |||||||||||||
| Overhead Expense | |||||||||||||
| COGS | |||||||||||||
| Payroll Expense | |||||||||||||
| Total Operational Expense | |||||||||||||
| Taxes | |||||||||||||
| Sales Tax Expense | |||||||||||||
| Payroll Tax Expense | |||||||||||||
| Total Tax Expense | |||||||||||||
| Total Cash Outflow | |||||||||||||
| Ending Cash Balance |
Reports
| Graph 1 | Pie Chart showing breakdown of annual expenses into these four categories: COGS, Overhead, Payroll, and Taxes |
| Graph 2 | Line Chart showing total Sales comparing to total expenses (including all tax expenses) by month |
| Graph 3 | Pie Chart showing total units sold for the year with the beakdown of percentage of each product type (ratio between products sold) |