Excel Question

excel question and need the explanation and answer to help me learn.

I have ab assignment done in python but another client needs the similar assignment done in Excel. I can share the python analysis if need be. I need a code, a word and a pdf.
Requirements: As needed
ENG/TE 566 Final Project Sample (Computer Program)
Summary¥ This document shall walk you through the expected delivery for the final project of creating a computer program that can be used as a bookkeeper for your future small business.
Main Menu
View & Add Employees
View & Add Customers
View & Add Vendors
Pay Employee
Pay Employee & Payment History (1/2)i.ii.
Pay Employee & Payment History (2/2)iii.
Inventory
Create Invoice & Invoice History (1/2)i.ii.
Invoice Employee & Invoice History (2/2)iii.iv.
Create PO & PO History (1/2)i.ii.
Create PO & PO History (2/2)iii.iv.
Net30 example (for Accounts Receivable & Accounts Payable)
Business Problem in Project Requirements (Start)
Business Problem in Project Requirements: Purchase Orders
Business Problem in Project Requirements: Payroll
Business Problem in Project Requirements: Invoices
Final Income Statement and Balance Sheet
Computer Program Final Turn in Assignment A) Start the program with some initial conditions. B) Take a screen shot of the Income statement and Balance Sheet C) Payables are 30 day terms and sales are 30 day terms. (A sale shows up as a receivable on the balance sheet) Do the following three transactions and show the before and after Income Statement and Balance Sheet: 1) Make a sale for $50,000. 2) Purchase $20,000 in inventory 3) Pay an employee for 1 payroll period. I think these 3 transactions should put your program through its’ paces…. Save as a single pdf document and submit. Please also submit a copy of the program code.
ENG/TE 566 2020 Computer Program Instructional Guide
1. Problem Statement Using the following information and the software modules that you have previously developed, create a software program that continually updates the income statement, balance sheet, and cash flow for your business upon completion of a business financial activity. The program should store historical information for later review. The program should be able to provide a hard copy of the various completed forms. Note: The toy truck example given in class is an example business and does not have to be used for the computer program exercise. If you chose to use the toy truck example, please see part 2 of this instructional guide for a description of initial financial conditions. 2. Toy Truck Financial Conditions Example bill of materials (BOM) Item Quantity per Truck Unit Cost 1 Wheels 8 $.01 2 Windshield Glass 1 $.05 3 Interior 1 $.05 4 Tank 1 $.10 5 Axles 4 $.01 6 Cab 1 $.10 7 Body 1 $.10 8 Box (packaging) 1 $.05 3. General Functions (applicable to all computer programs) Initial investment = $200,000 No long-term asset purchases Sales = $1,000,000 Four inventory purchases will be made during the year (purchase order at the beginning of each quarter). Parts will be ordered from 8 unique vendors. You may assume that the inventory arrives when the PO is produced (although creating the option to wait is good). Note, what is received is often different in quantity than what is ordered. For example, you order 5,000 carton labels and 5471 are delivered and billed to you. Conversely, sometimes quantities are shipped less than the order quantity.
The inventory module should show quantity on hand in terms of units (not just $), finished goods, and reorder points. Additionally, the computer program should warn the user when the inventory levels gets low so that more raw parts can be ordered. The business will experience 12 sales. A sale requires the creation of a customer invoice (beginning of each month). There will be a total of 10 customers. Terms of a sale are net 30. At the end of each month, you will update the books by posting receivables to the cash account and paying your bills even if it’s not quite 30 days. The software should handle 10 different expense accounts (you may ignore depreciation in your software). Expenses total $200,000 for the year not including salary or withholding taxes. You should have a separate expense account for the various taxes (in addition to the 10). There will be 12 payroll events (1 employee @ $150,000 annual salary paid at end of the month). The employee is single with 15 children. Just kidding single with no kids but has a pet dog (it’s a cross between a bull dog and a Shih Tzu). I’m sure I said something different in class. Oh well. It would be great if your software could handle the general payroll situation, but the minimal requirement is to handle this slacker. 4. Programming – Screen Shots and Description of Functionality a. Main Menu
b. View Employees – Displays the current list of employees c. Add Employees – Gathers employee information from the user and updates the employee listing d. View Customers – Displays the current list of customers e. Add Customer – Gathers customer information from user and updates the customer listing
f. View Vendors – Displays the current list of vendors g. Add Vendor – Gathers vendor information from user and updates the vendor listing
h. Pay an Employee – Actions to be completed: i. Update the balance sheet based on the cash outflow shown on the employee listing sheet ii. Update the income statement based on the cash outflow shown on the employee listing sheet iii. Document the history of payroll events
Select the employee to pay After the salary payment, it should automatically update the proper cells in the income statement and balance sheet Create a record of the payroll events i. View Payroll Events – View the history of previous payroll events Make sure you include the Federal tax, State tax, social security tax, and Medicare tax. Check this year tax %
j. Create Invoice – Actions to be completed when completed units are sold: i. Update the balance sheet with receivables from a sale. See 3. General Functions for a completed description of the payment scheme (net 30, cash, receivables, 10 customers) ii. Update the income statement with sales iii. Update the inventory to reflect the sale of complete units iv. Update the history of invoices Select the Customer and input the number of units to invoice
Update the balance sheet and income statement Update the Inventory of complete units in stock once a sale is made Update the history of invoices k. Create PO – Actions to be completed when a purchase order is created: i. Update the balance sheet with the expense of buying components ii. Update inventory of parts iii. Update the history of purchase orders
Select the part and quantity Update the PO history
Update the Inventory of components Update the balance sheet and income statement l. View Inventory – Display the current amount of parts, units under construction, completed products ready for sale, and flag the parts that need to be reordered m. View Balance Sheet – Go to the current balance sheet n. View Income Statement (P&L) – Go to the income statement for the current year 5. Optional Functions (non-essential) a. View expense account – Shows the total expenses of the business
Example business expenses b. Pay a monthly bill – Pay a business expense from the expense account and update the balance sheet and income statement Pay for business expenses and update the balance sheet and income statement c. View Paid Expenses – View the paid business expenses History of paid business expenses d. Build Units – Build additional units with the inventory of parts in stock and update the inventory listing
Select the number of units to be built out of the available inventory

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *