CIS Excel assignment

profileethan5599
StudentTemplate1.xlsx
Home>Computer Science homework help>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)