attachment_1 (1)

.docx

School

Arizona Western College *

*We aren’t endorsed by this school

Course

1310

Subject

Computer Science

Date

Nov 24, 2024

Type

docx

Pages

Uploaded by JudgeTitaniumStingray37

School of Engineering and Computer Science (SECS) Department of Computer Science and Engineering Instructions: You must submit two separate copies (one Excel file and one PDF file) using the Assignment Template on Moodle via the allocated folder. These files must not be in compressed format . It is your responsibility to check and make sure that you have uploaded both the correct files. Email submission will not be accepted. You are advised to make your work clear and well-presented. This includes filling your information on the cover page. You must use this template. You MUST show all your work, and text must not be converted into an image, unless specified otherwise by the question. The work should be your own. Use Times New Roman font for all your answers. Assignment 4 Deadline: Tuesday 11/28/2023 @ 23:59 Total Mark for this Assignment is 100 Intro Computer and Programming w/Excel CSI 1200

Question One You are a finance manager for Garten Furniture, a local furniture store that also provides financing, delivery, and worry-free setup. As part of your daily tasks, you create an Excel workbook that reports sales, payment plan information, and summary statistics. Insert Current Date In order to ensure proper documentation, you want to insert the current date and time. 1. Open the e02c1FurnitureSales workbook and save it as e02c1Furniture_LastFirst. 2. Insert a function in cell C3 to display the current date and format as a Long Date. 3. Set column C’s width to Autofit. 25 Marks

Question Two Create Item Lookup Your first task is to use the VLOOKUP function based on the data in the range A18:C23 to determine the name of the item purchased and the corresponding price based on the provided SKU number. 4. Insert a function in cell D11 to display the item named based on the provided inventory lookup information in the range B24:D29. 5. Copy the function from cell D11 down through D19 to complete column D. 6. Insert a function in cell E11 to display the item price based on the provided inventory lookup information. 7. Copy the function from cell E11 down through F19 to complete column E. 8. Apply Currency Format to column E. Determine Delivery Fee You will calculate the total due for each customer’s order. The total is the purchase price plus an optional $75.00 delivery charge. 9. Insert an IF function in cell G11 to calculate the total due. If the customer has chosen home delivery, there is an additional delivery charge located in cell L11. Be sure to use appropriate relative and absolute cell references. 10. Copy the function from cell G11 down through G19 to complete column G. 11. Apply Currency format to column G. 25 Marks

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Question Three Calculate the Monthly Payment Your next step is to calculate the periodic payment for each customer's purchase. The payments are based on the years financed in column H and the annual interest rate in cell I12. All accounts are paid on a monthly basis. 12. Insert the function in cell I11 to calculate the first customer's monthly payment, using appropriate relative and absolute cell references. 13. Copy the formula down the column. 14. Use Quick Analysis to insert a function in cell H20 that calculates the total of all monthly payments in column I. 15. Apply Currency format to column I. Create Client Lookup You want to add functionality to the worksheet by using the XLOOKUP function to return finance information based on client name. 16. Type the customer name McGowan in cell B6. 17. Use the XLOOKUP function in cell C6 to look up the customer name in the worksheet and return the corresponding SKU, item name, delivery option, price, total due, years financed, and monthly payment. 18. Use Format Painter to copy the format from the range C11:I11 to C6:I6. 25 Marks

Question Four Finalize the Workbook You perform some basic statistical calculations and finalize the workbook with formatting and page setup options. 19. Insert a function in cell H24 to calculate the total number of orders. 20. Insert a function in cell H25 to calculate the lowest monthly payment in column H. 21. Insert a function in cell H26 to calculate the average monthly payment in column H. 22. Insert a function in cell H27 to calculate the highest monthly payment in column H. 23. Insert a function in cell H28 to calculate the median monthly payment in column H. 24. Apply Currency format to the range H25:H29. 25. Save and close the workbook. Exit Excel. Based on your instructor’s directions, submit: e02c1FurnitureSales_LastFirst 25 Marks

Recommended textbooks for you

Enhanced Discovering Computers 2017 (Shelly Cashm...

Computer Science

ISBN:9781305657458

Author:Misty E. Vermaat, Susan L. Sebok, Steven M. Freund, Mark Frydenberg, Jennifer T. Campbell

Publisher:Cengage Learning

Microsoft Windows 10 Comprehensive 2019

Computer Science

ISBN:9780357392607

Author:FREUND

Publisher:Cengage

COMPREHENSIVE MICROSOFT OFFICE 365 EXCE

Computer Science

ISBN:9780357392676

Author:FREUND, Steven

Publisher:CENGAGE L

Np Ms Office 365/Excel 2016 I Ntermed

Computer Science

ISBN:9781337508841

Author:Carey

Publisher:Cengage

Oracle 12c: SQL

Computer Science

ISBN:9781305251038

Author:Joan Casteel

Publisher:Cengage Learning

SEE MORE TEXTBOOKS

Recommended textbooks for you

Enhanced Discovering Computers 2017 (Shelly Cashm...
Computer Science
ISBN:9781305657458
Author:Misty E. Vermaat, Susan L. Sebok, Steven M. Freund, Mark Frydenberg, Jennifer T. Campbell
Publisher:Cengage Learning
Microsoft Windows 10 Comprehensive 2019
Computer Science
ISBN:9780357392607
Author:FREUND
Publisher:Cengage
COMPREHENSIVE MICROSOFT OFFICE 365 EXCE
Computer Science
ISBN:9780357392676
Author:FREUND, Steven
Publisher:CENGAGE L
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning