run a financial model and make decisions of a business selling specific products, homework help

User Generated

zvxrlhohrr

Business Finance

Description

Hello need this assignment completed before Thursday, any help would be great. hoping to pay $25, wont let me upload the Access database so i can email that after. I will upload the Excel template that was provided by the instructor. T he instructions are located in the files i have attatched .

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. Set up
  2. Sales
  3. Staff
  4. Accounting

Additional instructions will be in the ‘template’ spreadsheet for this assignment.

Unformatted Attachment Preview

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
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Hello, Am back. This is now the step I wanted us to work together. I don't want you to present something you have no idea about. So I want you to put yourself in t...


Anonymous
Really helpful material, saved me a great deal of time.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags