CIS Excel assignment

Configuration

Configuration (

Dec

ision) Variables Values Descriptions Sales Tax

Rate

Mar

keting

– 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

Plus

Iphone 8

Mar

Dec

Iphone X

Iphone 8
iWatch Series 3

Iphone X
Iphone 8 Plus
Iphone 8
iWatch Series 3

Sales Tax

Decision Variables

Products

Unit Price

Unit COGS

Ratio b/w Products

Iphone X

Iphone 8

iWatch Series 3

Sales (units) Projection

Jan

Feb

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Target Units (Meals)

Projected

Revenue

Iphone 8 Plus

Total Revenue

Projected COGS

Total COGS

Gross Profit

HR

Decision Variables

Manager
Supervisor

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

President
Manager
Supervisor
Staff

by Position

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

President
Manager
Supervisor
Staff

Payroll Tax

Employee Positions	Annual Salary
		President
		Staff
Number of Each Position on Payroll
Monthly	Payroll Expense
Total Payroll Expense

Accounting

Decision Variables

Revenue Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
COGS
Payroll Expense

Startup Investment

Beginning Cash Balance

Last Month Credit Card Intake

Current Month Cash Intake

Total Cash Onhand

Operational Expense

Overhead 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)

Individual Excel Project Guideline

CIS 310 Management Information System

Kan Wang

DECISION MAKING USING DATA/INFORMATION – Microsoft Excel Assignment 100 points

Students will be given a new small business idea selling specific products to customers. Using Microsoft Excel, students will develop various data models to simulate an information system that provides business information for decision making. Students will utilize Excel to model the business operation, formulate the data into information, utilize the information to analyze scenarios, and draw conclusions for the business concept.

Your final Microsoft Excel project workbook (file) will include the following worksheets (tabs). Specific instructions, concepts, expectations, and demo will be provided during scheduled class hours (or via a video for online students). It is highly recommended you attend all classes (or review the video) to obtain the skills and understanding to complete this assignment.

The Business Idea:

· You are planning to open a small electronic store selling three Apple products.

You will be provided an Excel Template as a starting point. The Excel Template will contain the following sheets (Tabs) which you are to make functional as instructed in class:

· Configuration

· Marketing Model

· HR Model

· Accounting Model

· Graph & Charts

Instructions:

· For each sheet, the cell colored in yellow are
decision variable
cells. Within these cells, users of your excel model will be able to change the values contained within it. Decision variables are decision points that you, as a potential owner of this simulated business, may decide and/or test so the model will generate calculated results to help you determine how this business can be viable or profitable.

· For each sheet, the cell colored in gray are
calculated values
. Within these cells, you are expected to apply formulas so the sheet can yield correct calculated results based on your decisions entered into the decision variable cells.

· For all decision variables and calculated value cells, you are to make sure the
formatting
of the cell is correct. Example if the cell represent a percentage, it should be formatted to %.

· You are not to rearrange and/or reformat the layout within each sheet. The one exception is you are free to design your graphs in the last sheet as long as it meets the requirement stated in the template.

Grading:

Configuration Tab (10 Points) – Application Setting

· 1 point deduction for every cell not formatted correctly

· 2 points deduction for each missing value

Marketing Model Tab (25 Points) – Objective: Model, Revenue, COGS, and Gross Profit Monthly Projection

· 1 point deduction for every cell not formatted correctly

· 2 points deduction for every ‘data referencing’ error

· 4 points deduction for every calculation error

HR Model Tab (25 Points) – Objective: Model, Salary Expense, and Employer Tax Expense Monthly Projection

· 1 point deduction for every cell not formatted correctly
· 2 points deduction for every ‘data referencing’ error
· 4 points deduction for every calculation error

Accounting Model Tab (25 Points) – Objective: Model and Cash Flow Monthly Projection

· 1 point deduction for every cell not formatted correctly
· 2 points deduction for every ‘data referencing’ error
· 4 points deduction for every calculation error

Graph & Charts Tab (15 Points) – Objective: Executive Graphical Presentation

· 1 point deduction for each missing label to the data presented on the graph

· 2 points deduction for every ‘data referencing’ error

Outcomes of your completed Excel project file will be a tool for users to:

Model – framing of decision variables, presenting calculated data/information, and relationships between data variables for analysis

What-if analysis – checks the impact to the bottom line based on assumptions and changing of decision variables

Sensitivity analysis – the study of the impact that changes in one (or more) parts of the model (decision variables) have on other parts of the model

Goal-seeking analysis – finds the inputs necessary to achieve a goal such as a desired level of output

Optimization analysis – An extension of goal-seeking analysis, finds the optimum value for a target variable by repeatedly changing other variables, subject to specified constraints.