My Store
Instruction: these values are given to you to develop the financial model, they are fixed. You must use these parameters to dev
is to determine what would be the initial investment required to make this business profitable, achieved 10+% profit, and stay
Note: please name /save your Excel file as follows: "yourname_CIS310_Fall2016"when you submit your assignment into black
charts will be different depending on the numbers you modify.
Configuration (Decision) Variables
Values
Credit Card vs Cash Ratio
10%
Overhead Cost Ratio
20%
Sales Tax Rate
10%
Cost of Goods Sold
15%
Payroll Tax Rate
10%
Employee sales capacity
220
References:
The True Cost To Hire An Employee in
California
you to develop the financial model, they are fixed. You must use these parameters to develop the next few tabs in the spreadsheet. Your jo
tial investment required to make this business profitable, achieved 10+% profit, and stay in month to month positive cash flow.
ile as follows: "yourname_CIS310_Fall2016"when you submit your assignment into blackboard. Also each students financial numbers and
the numbers you modify.
Descriptions
Accounting - Estimated percentage of customers using Credit Card instead of Cash
for each month
Accounting - Estimated percentage of overhead (rent, untility, insurance,
equipments, etc.) off total revenue excluding sales tax
Marketing - Sales Tax (%) to be collected per sale, LA Coutny sales tax is approx.
10%
Marketing - COGS per unit as percentage of sales price
Tax rate to employers based on payroll expense paid to government
Maximum # of units sale each employee can handle per month
https://framework.gusto.com/the-true-cost-to-hire-an-employee-in-californiainfographic/
tabs in the spreadsheet. Your job
h positive cash flow.
students financial numbers and
Instruction: this is the sales and income projection for 12 months using the given unit price, unit co
for sales plus sales taxe. Sales units is based on each employees capacity of sales and total number
Sales
Products
Product A
Product B
Product C
Decision Variables
Unit Price
Unit COGS
$
24.00 $
3.60
$
20.00 $
3.00
$
16.00 $
2.40
Sales (units) Projection
Units
Jan
Ratio by Products
60%
30%
10%
Feb
220
Mar
440
Apr
660
Projected Revenue
Product A
Product B
Product C
Total Revenue
$
$
$
$
3,168.00
1,320.00
352.00
4,840.00
$
$
$
$
6,336.00
2,640.00
704.00
9,680.00
$
$
$
$
9,504.00
3,960.00
1,056.00
14,520.00
Projected COGS
Product A
Product B
Product C
Total COGS
$
$
$
$
475.20
198.00
52.80
726.00
$
$
$
$
950.40
396.00
105.60
1,452.00
$
$
$
$
1,425.60
594.00
158.40
2,178.00
Gross Profit $
4,114.00 $
8,228.00 $
12,342.00
Sales Tax (collected) $
484.00 $
968.00 $
1,452.00
5,324.00 $
10,648.00 $
15,972.00
Total Income:
$
nths using the given unit price, unit cost, ration by products, number of staff, the number of sales volume per staff, and estimated sales tax
es capacity of sales and total number of employees. !st month is start-up month, so sales is projected to be only half.
Note: you could adjust the unit price and/or product ration if you would like to achive the
10% profit.
May
Jun
Jul
Aug
Sep
Oct
aff, and estimated sales tax. Note: you collect $$
y half.
Nov
Dec
Staff
Instructions: estimate and calculate the staff for 12 months required using the salaries provided,
you see fit over the 12 months. Increase in staff also increases the number of sales untis and the
Decision Variables
Employee Positions
Annual Salary
Store Manager
Staff
$
30,000.00
$
note: minimum wage is 10/hr in California as of
20,800.00 8/18/16
Number of Employees
Store Manager
Staff
Jan
Feb
Mar
Payroll
Store Manager
Staff
$
$
Payroll Expense $
Jan
2,500.00 $
1,733.33 $
4,233.33 $
Feb
2,500.00 $
1,733.33 $
4,233.33 $
Mar
2,500.00
3,466.67
5,966.67
Payroll Tax $
423.33 $
423.33 $
596.67
1
1
1
1
1
2
STAFFING
Store Manager
Staff
3.5
EMPLOYEES
3
2.5
2
1.5
1
0.5
0
1
2
3
4
5
6
7
MONTHS
8
uired using the salaries provided, and show 1) a employee ramp-up graph (Excel line graph) and 2) annual payroll chart (Excel column or b
he number of sales untis and therefore revenenu, income, cost of goods, payroll taxe, and sales tax. Ensure your charts are properly labele
Apr
May
Jun
Jul
Aug
Sep
Annual Payroll
FFING
Staff
7000
These charts look different
depending on how you staff
for from April thru
6000
$$
5000
4000
3000
2000
1000
0
1
8
9
10
11
12
2
3
4
5
13
Payroll Expense
al payroll chart (Excel column or bar chart). You can choose to increase the number of staff as
ure your charts are properly labeled.
Oct
Nov
Dec
Annual Payroll
5
6
7
8
9
MONTHS
Payroll Expense
Payroll Tax
10
11
12
13
monthly cash flow
Instruction: develop 12 month cash flow and determine the minmum investment required to achieve m
basically is income/revenu or cash/available funds minus all expenses and taxes for the current month,
annual expenses compared to your revenue or income.
Decision Variables
Startup Investment
minimum investment amount in order to maintain positive cash flow
Revenue
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
$
$
$
$
Jan
$
$
$4,791.60
4,791.60 $
968.00
726.00
4,233.33
5,927.33
Feb
(2,043.07)
532.40
$9,583.20
8,072.53
$
$
$
$
1,936.00
1,452.00
4,233.33
7,621.33
Taxes
Sales Tax Expense
$
Payroll Tax Expense
$
Total Tax Expense $
484.00 $
423.33 $
907.33 $
968.00
423.33
1,391.33
Total Cash Outflow $
6,834.67 $
9,012.67
Ending Cash Balance $
(2,043.07) $
(940.13)
minmum investment required to achieve monthly positive cash flow. Motnhly cash flow is a way to manage the money you have vs. the pay
expenses and taxes for the current month, left over funds carry to the next month for next month's payments. This is important in that it al
unt in order to maintain positive cash flow
$
Mar
(940.13)
1,064.80
$14,374.80
14,499.47
$
$
$
$
2,904.00
2,178.00
5,966.67
11,048.67
$
$
Apr
May
Jun
Jul
Aug
$
$
$
$
-
$
$
$
$
-
$
$
$
$
-
$
$
$
$
-
$
$
$
$
-
$
$
$
1,452.00 $
596.67 $
2,048.67 $
-
$
$
$
-
$
$
$
-
$
$
$
-
$
$
$
-
$
13,097.33 $
-
$
-
$
-
$
-
$
-
$
1,402.13 $
-
$
-
$
-
$
-
$
-
ge the money you have vs. the payments you have to make each month, so
ents. This is important in that it allows you to see your monthly expenses and
Sep
Oct
Nov
Dec
$
$
$
$
-
$
$
$
$
-
$
$
$
$
-
$
$
$
$
-
$
$
$
-
$
$
$
-
$
$
$
-
$
$
$
-
$
-
$
-
$
-
$
-
$
-
$
-
$
-
$
-
Graph 1
Total Income:
Total Expenses:
Line Chart showing Total Revenue comparing to total expenses (including all taxes) annual total and
to create the charts. Be sure to use "red' to inidicate expenses on your charts. Ensure your charts are
Jan
$ 5,324.00 $
$ 6,834.67 $
Feb
10,648.00 $
9,012.67 $
Mar
15,972.00 $
13,097.33 $
Apr
May
-
$
$
-
Income vs. Expenses (monthly)
$18,000.00
$16,000.00
These charts loo
depending on ho
for from April th
$14,000.00
$12,000.00
$10,000.00
$8,000.00
$6,000.00
$4,000.00
$2,000.00
$1
2
3
4
5
Total Income:
6
7
8
Total Expenses:
9
10
11
12
ng all taxes) annual total and by month. You need to ensure you have the right calculations in order
harts. Ensure your charts are properly labeled.
Jun
$
$
Jul
-
$
$
Aug
-
$
$
Sep
-
$
$
Total Income:
Total Expenses:
These charts look different
depending on how you staff
for from April thru
Oct
-
$
$
-
$
$
-
Annual Total
$
31,944.00
$
28,944.67
Annual Income vs. Expenses
48%
12
Nov
52%
Dec
$
$
vs. Expenses
-
This pie chart must show a
10+% profit between income
& expenses
Total Income:
Total Expenses:
Graph 2
Pie Chart showing breakdown of annual expenses into these four categories: COGS, Overhead, Payroll, and Taxes.
Jan
Overhead Expense
COGS
Payroll Expense
Sales Tax Expense
Payroll Tax Expense
968.00
726.00
4,233.33
484.00
423.33
$
$
$
$
$
Feb
1,936.00
1,452.00
4,233.33
968.00
423.33
Overhead Expense
COGS
Payroll Expense
Sales Tax Expense
Payroll Tax Expense
$
$
$
$
$
$
5,808.00
4,356.00
14,433.33
2,904.00
1,443.33
28,944.67
$
$
$
$
$
$
$
$
$
$
Mar
2,904.00
2,178.00
5,966.67
1,452.00
596.67
COGS, Overhead, Payroll, and Taxes. Ensure your charts are properly labeled.
Apr
$
$
$
$
$
May
-
$
$
$
$
$
Jun
-
$
$
$
$
$
Jul
-
$
$
$
$
$
Aug
-
$
$
$
$
$
Sep
-
$
$
$
$
$
Oct
-
$
$
$
$
$
-
Annual Expenses:$XXXXXX
10% 5%
each student's chart will be
different depending on their own
20%
15%
50%
Overhead Expense
COGS
Sales Tax Expense
Payroll Tax Expense
Payroll Expense
Nov
$
$
$
$
$
Dec
-
$
$
$
$
$
-
CIS 310 Excel Assignment
Objective of this assignment is to provide the students the opportunity to develop analytic and
problem solving skills, and learn business decision making by using data and information, and
Microsoft Excel.
For this assignment, you (the student) use Microsoft Excel to run a financial model and make
decisions of a business selling specific products. You are expected to utilize Excel formulas and
charts to analysis the financial data and determine how much you should investment in this
business in order to make it profitable. You utilize Excel to create and capture data, formulate
the data into information, utilize the information to analyze scenarios, and draw business
conclusions.
Your final Microsoft Excel workbook (spreadsheet) must include tabs named as follows along
with multiple charts and graphs:
1.
2.
3.
4.
Set up
Sales
Staff
Accounting
Additional instructions will be in the ‘template’ spreadsheet for this assignment.
Grade for this assignment
Problem Solving & Analytic Assignment (Microsoft Excel)
Spreadsheet must have these deliverables (tabs)
completed:
1. Sales (for 12 months)
2. Staff (for 12 months including staffing graph and
annual payroll expense chart)
3. Accounting (cash flow for 12 months)
4. Income vs. Expenses by month (for 12 months)
and annually
5. Annual expense (COGS, Overhead, Payroll, and
Taxes)
Weights
20%
20% each
Points
20
4 points
each
CIS 310 ACCESS Assignment
Objective of this assignment is to provide the students the opportunity to develop analytic and
problem solving skills, and learn relational database design using Microsoft Access. This
assignment is built on top of the Excel Assignment.
For this assignment, you (the student) use Microsoft ACCESS to create a relational database and
produce SQL queries and two reports that show total sales and total cost of products.
Tutorial on Microsoft Access is available on Lynda.com. Below are a few suggestions:
▪ Learn Access 2016: The Basics with Adam Wilbert
▪ Access 2016 Essential Training with Adam Wilbert
▪ Access 2016 Advanced Tips and Tricks with Adam Wilbert
Be sure to watch these videos to refresh your knowledge on how to use ACCESS prior to
starting the assignment. The prerequisite of CIS 310 is CIS 101 where you learned the basic on
how to use Microsoft tools.
Grade for this assignment
Problem Solving & Analytic Assignment (Microsoft
ACCESS) – creation of relational database and reports
Break-down as follows:
Tables: Customers, Products, Sales Orders, Staff
Forms: Customers, Products, Sales Orders, Staff
Queries: Total Sales, Product Costs
Reports: Total Sales Report, Product Cost Report
Weights
20%
20%
20%
30%
30%
Points
20
4
4
6
6
Instructions:
Below are the tables, forms, queries and reports that you are expected to complete for this
assignment (see screen shots)
Here are the steps for you to follow:
1. Create the four tables using specific data (see below) by creating the “tables” in ACCESS and key
in these data as shown below (screen shots)
2. Create the four forms from the four tables in ACCESS
3. Create two queries, you may want to use the wizard to do this
4. Create two reports from the two queries
Your Database must have all these tables, forms, queries, and reports. Please follow the naming
convention as shown. A template ACCESS database is given to you with customers, staffs, and products
tables already created and populated.
Tables
Use “create’ function and ‘Design View” option to create the tables. You must ensure that appropriate
data fields in the tables are of the right data types and “masked” using ‘input mask” option in Access to
ensure data integrity.
Customer table – phone number and zipcode are two example in this tables
Design View of Customers table. Note that Zip Code field’s “input mask” to ensure data entry integrity.
Products Table
Staff Table – SS#, phone number and zip code are “masked” using “mask input”
Sales Orders
You are expected to create this tables using information from the above three tables. Make sure that
Product ID, Employee ID, Customer ID are of data type number.
Design View of Sales Orders Table
Forms
You are expected to create four forms look like these. Remember the forms are generated from the
tables in Access, you use the “create” function then select “form design” to create forms in Access. Be
sure to reformat the forms to look like these using “Design View” and move/drag/drop the fields.
Below is the “layout view” of the sales Order Details form.
Below is the “design view” of the sales Order Details form.
To get the drop down arrow on the product ID field, Employee ID field, and Customer ID filed,
click/select the field itself, right click on mouse, a menu shows up, click “change to” then select “combo
box”. Notice the column count is 2, and column width is .25”, .25” on the property sheet, this allows the
“drop down menu” to show product ID and Product Name for selection purpose.
Set “Limit to List” on the data tab on the Property Sheet to “yes” , this limits the data entry to the pick
list for Product ID.
Repeat the same steps above for Customer ID and Employee ID; the column count is 3, and column
width is .25”, 1”, 1” on the property sheet, the drop down arrow on the Customer and Employee ID
fields show ID, first and last names. Set “Limit to List” on the data tab on the Property Sheet to “yes”.
Queries look like these. Here are the steps:
1. Create them using “Query Wizard” or “Query Design” in the Create function.
2. Establish “relationship” between the tables by connecting the “key” fields between each table
by drag & drop the identical field from one table to the next; for example: “Product ID” in the
“products” table to the “product ID” in the “Sales Orders “table, and so forth between all fours
tables. This is how you establish “relationship” between “key’ data fields/identifier.
3. Double click on each fields that are required to build the report, and add “total sales:
[quantity]*[price/unit]” to the last column, be sure to have ‘ check mark’ on all columns.
4. Repeat the same steps to create “product costs” query, again “total cost: [quantity]*[cost/unit]”
Reports
You are expected to create two reports. Create the reports from the queries using report wizard, makes
you format the reports as shown below. Make sure you select sort by product ID in ascending order
when creating this report using “report wizard”
Make sure you select sort by Employee ID in ascending order when creating this report using “report
wizard”
You use the “design view” in Access to format the reports (as shown below) so that the reports look line
up and easy to read.
Extra Credit Item:
Add text items to the Sales Order Details form so the user can determine the Product, Employee’s First
and Last Name, and Customer’s First and Last Name.
Instructions:
Open the Sales Order Details form in the Design view.
Open the Property Sheet for the Sales Order Details Form. Select “Form” on the Property Sheet menu,
In the Record Source, click on the “…” select item to create a query. You are creating a query for the
form, this is not a query for the report.
When the query opens, click on “select table” to add the Products table, the Staff table, and Customers
table. If the tables were set up correctly, the key fields will automatically connect.
Add all the fields from the Sales Order table, the Product Name and Product Description from the
Products table, the Employee First and Last Name from the Staff table, and the Customer’s First and Last
Name from the Customer’s table by highlighting the fields and dropping them to the grid below, or by
double clicking each field. It looks like this.
Close the query, returning you to the form. Be sure you are in the “Form design tool” view, click on
“ab|” to add a field to the right of the Product ID field, click on the drop down arrow on ‘control source’
listed on the property sheet, select Product Name field. Repeat the same steps to add Product
Description, Employee first and last names to the right of the Employee ID field, and Customer first and
last names to the right of Customer ID. Make sure the fields lined up properly
Purchase answer to see full
attachment