Data link: https://drive.google.com/drive/folders/1txHtl8_W2u1rUnGwdgDONyfg22KVKWUP?usp=sharing

CAS 171 - Final Project: Computer Warehouse

Data Files: ComputerWarehouse.accdb, WarehouseAnalysis.xlsx, CW_Financial.xlsx Problem: Demonstrate Excel knowledge and competence by completing the below fifteen part

comprehensive project for Computer Warehouse.

Part 1 - Import Customer and Product Data - Create Invoice

Computer Warehouse: Computer Warehouse is a retailer of computers and office equipment. The company maintains an internal database with customer and inventory information. However, this database does not allow customers to place orders directly. You have been asked to design a customer order form, which includes select data from database, to make it easier for customers to place future orders. The form you will create appears in the image below.

1 CAS 171 Final Project

1. Create a new work book and save it as ComputerWarehouse.xlsx
2. Import only the Customer and Product Details tables from Computer Warehouse . accd b. Make sure the data is imported on separate sheets as tables. Name both the tables and
3. sheets Customers and Products. Format numbers accordingly (use currency for prices).
4. Delete any blank worksheets.
5. Insert a new sheet, and move it to the beginning of the workbook. Name the sheet Invoice and
6. complete the following:

• Change the workbook Theme to Facet.
• A1 - Type Computer Warehouse. Merge and Center A1:F1. Apply Accent 2 cell style.
• Change font size to 24.
• A2 - Type 17705 NW Springville Rd. Hillsboro, Oregon 97229. Merge and Center
• A2:F2 and apply the 20% Accent 2 cell style.
• A3 - Type P: 971-555-5432. Right Align.
• A4 - Type F: 971-555-2345. Right Align.
• F3 - Type Email: s..s@c w.com. Right Align.
• F4-Type Website: www.computerwarehouse.com. Right Align.
• Select the range A3:F4 and apply the 40% - Accent 2 cell style. Change font size to 8. AutoFit Column A.
• Enter the following Customer Information. (Bold All)
• A7: Bill To:
• A8: Email:
• A9: Phone:
• A10: Fax:
• E6: Customer #:
• E8: Invoice #:
• E9: Invoice Date:
• Right Align A7:A10 and AutoFit Column E

1. In the range A13:F13 add the following headers: Item#, Description, Qty, Unit Price, Discount, and Total. (Auto Fit All Column Widths, Left Align A13:C13, Center Align D13:F13)
2. Select the range A13:F38 and insert a table. Name the table Order_Details. Apply Table Style Light 3. Turn Off Filters.
3. Outside of the table, in cells E40:E43 enter the following information: Subtotal, Sales Tax, Shipping, and Total. Define these as names in the Name Manager for the adjacent cells in column F. Apply the 20% Accent 2 cell style to E40:F40 and E42:F42. Apply Total cell style to E43:F43.

2 CAS 171 Final Project

Part 2 - Data Validation and Formulas

1. In cell F6 and A14:A38 create data validation rules that allows you to choose the Company # and Item # from a list, using columns from the Customers and Products sheets as the sources. Create an Input Message and Error Alert for both validation rules.
2. Using Structured References, nest the following VLOOKU P functions, within aI FERROR functions:

• In cells B7, B8, B9, and B10 create VLOOKU P functions that will display the required information based on what Customer # is chosen in cell F6. Use an IFERRO R function to display nothing if a customer is not chosen or if there is an error. Test your functions, selecting Customer #2. (Auto fit columns if necessary.)
• In B14, D14, and E14 create VLOOKU P functions that will display the description, unit price, and discount based on what Item# is chosen in A14. Use an IFERROR functions to display nothing if an item is not chosen or if there is an error. (Format numbers as currency.) (Auto fit columns if necessary.) [Test your functions by selecting an Item#.]
• In F14, create a formula to calculate the Total. (=Qty * Unit Price - Discount). Nest the formula in an IFERROR and display nothing if A14 is blank. Format numbers as currency.
• Add a comment in F13: Does not include shipping. Shipping is calculated after

subtotal.

• Company #2 placed an order, as shown in Figure 1. Type the order into the Invoice form.
• In Cell F40 create a formula to calculate the Subtotal.
• In Cell F41 create a formula to calculate a 5% sales tax on all

Figure 1

Item# Qty

orders. Use Named Cells in the formulas. (Format Numbers).

10.In Cell F42, use an IF function to calculate the shipping charge. Shipping is free if the

customer orders 100 items or more. Orders with less than 100 items are charged $5.00 per item. (Hint: Nest SUM of Qty calculations inside the IF function). (Format numbers, and test your function).In F43 create a formula totaling the invoice.

Part 3 - Protect Worksheets

11.Protect the Invoice worksheet so that only Customer#, Item#, and Qty cells can be edited. (No password).

12.Protect the Customers and Products worksheet so that no changes can be made. 13.Save the workbook.

Z4567 5

Z4568 Z4570

10 20

Z4569 15

Z4571 25

3 CAS 171 Final Project

Part 4 - Create Macros and Buttons

You will create two macros to automate some of the steps that need to be done repeatedly. The two macros are ClearInputs and PrintInvoice. Don't forget to SAVE often while working with macros!

14.Save the workbook again, this time as ComputerWarehouse_Macro.xlsm, changing the file type to a Macro Enabled Workbook. Create a macro called ClearInvoice that will complete the following steps for the Invoice sheet: (All macros need to end with the workbook in a protected state).

• Delete the information in cells: F6, A14:A18, and C14:C18
• Select cell F6. Stop recording.
• In Cells H2:I2 draw a button and assign it the ClearInvoice macro. Edit the text of the
• button to an appropriate name. Test your macro.
• 15.Create a new macro called PrintInvoice that will complete the following steps:
• Scale to Fit to 1 page and Set A1:F43 as the Print Area.
• Add a footer with the current date in the middle section and your name in the right
• section.
• Export the file to a PDF. (Save PDF as CW_PDF to your flash drive).
• Clear the Print Area.
• Stop recording.
• 16.Add a button to the Invoice sheet under the ClearInvoice button that will run the PDF macro. Edit the text of the button to an appropriate name.
• 17.Save your work. You should have the following files:
• ComputerWarehouse.xlsx
• ComputerWarehouse_Macro.xlsm CW_PDF.pdf

4 CAS 171 Final Project

Part 5 - Expansion Plan Analysis

Data File: WarehouseAnalysis.xlsx

Computer Warehouse: Computer Warehouse has identified a possible way to expand their business. Some customers would rather pay Computer Warehouse to host their servers for them, rather than buying and maintaining their own. To grow in this area, Computer Warehouse will need to take out a loan to purchase a new warehouse with enough space to host their client's servers. You have been asked to perform a break-even analysis to determine how to make this project profitable.

Computer Warehouse

Expansion Plan

Specificatons

WarehouseArea 1000

Area Required by each Server 20

Maximum Number of Servers 50 Monthly Revenue

Number of Servers 20

Monthly Hosting Fee $ 160.00

Total Revenue $ 3,200.00

Monthly Variable Expenses

Average Hardware Cost per Server $ 50.00

Average Admin Cost per Server $ 40.00

Electricity use by each Server $ 20.00

Total Variable Expenses $ 2,200.00

Monthly Fixed Expenses

Loan Amount $ 200,000.00

Annual Interest Rate 3.52%

Years of Loan 30

Monthly Payment $ 900.00

Insurance $ 500.00

Total Fixed Expenses $ 1,400.00

Summary

Total Revenue $ 3,200.00

Total Expenses $ 3,600.00

Net Income $ (400.00)

Servers

Break-Even Analysis

Revenue Expenses Net Income 20 $ 3,200.00 $ 3,600.00 $ (400.00)

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

3,360.00

3,520.00

3,680.00

3,840.00

4,000.00

4,160.00

4,320.00

4,480.00

4,640.00

4,800.00

4,960.00

5,120.00

5,280.00

5,440.00

5,600.00

5,760.00

5,920.00

6,080.00

6,240.00

6,400.00

6,560.00

6,720.00

6,880.00

7,040.00

7,200.00

7,360.00

7,520.00

7,680.00

7,840.00

$ 8,000.00

3,710.00

3,820.00

3,930.00

4,040.00

4,150.00

4,260.00

4,370.00

4,480.00

4,590.00

4,700.00

4,810.00

4,920.00

5,030.00

5,140.00

5,250.00

5,360.00

5,470.00

5,580.00

5,690.00

5,800.00

5,910.00

6,020.00

6,130.00

6,240.00

6,350.00

6,460.00

6,570.00

6,680.00

6,790.00

$ 6,900.00

(350.00)

(300.00)

(250.00)

(200.00)

(150.00)

(100.00)

(50.00)

(0.00)

50.00

100.00

150.00

200.00

250.00

300.00

350.00

400.00

450.00

500.00

550.00

600.00

650.00

700.00

750.00

800.00

850.00

900.00

950.00

1,000.00

1,050.00

$ 1,100.00

$9,000.00

$8,000.00

$7,000.00

$6,000.00

$5,000.00 $4,000.00

$3,000.00 $2,000.00

$1,000.00

Break-Even Analysis

$-

20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50

SERVERS

Revenue Expenses

18. Examine the WarehouseAnalysis.xlsx workbook to see the details of the warehouse expansion project.

19.In B6, create a formula to solve for the maximum number of servers the new warehouse can hold.

20. A conservative estimate of 20 servers has been listed in B8. Create a formula to solve for the total revenue earned from hosting these servers in B10.

21.In B15, sum the variable expenses of hosting each server, and multiply it by the Number of Servers. (Be sure you are using cell references, and not typing in the number of servers).

22.In B20, create a payment function to determine how much Computer Warehouse will be paying on their loan each month. (Make sure it is shown as a positive number, to match the other expenses). This is a fixed expense. It doesn't change, no matter how many servers are hosted in the space.

5 CAS 171 Final Project

23.Insurance is also a monthly fixed expense. Calculate the Total Fixed Expenses in B22. 24.Use cell references and a formula to complete the Summary cells B24, B25, and B26. 25.Use Create from Selection to create Defined Names for all of the cells containing values in

column B. (Skip all green headings.)

Part 6 - Goal Seek

26.Use Goal Seek to find an Annual Interest Rate that brings the Monthly Payment down to $900. 27.Keep the new interest rate

Part 7 - Data Table

28. In row 4, inside the Break-Even Analysis table, create cell references to Number_of_Servers, Total_Revenue, Total_Expenses, and Net_Income below the corresponding headers.

29.Starting in cell D5, in increments of 1, increase the number of servers from 21 to the maximum number of servers the warehouse can hold.

30.Create a one-variable table to show the revenue, expenses, and net income generated from each of the server numbers shown in Column D. Remember to select B8 as the column input cell for the number of servers.

Part 8 - Break-Even Chart

31. Create a chart of the Revenue and Expenses from the Break-Even Analysis table. 32. Change the Data Source of the Horizontal Axis to D4:D34.

33. Add a Primary Horizontal Axis Title chart element, with the text Servers.

34. Format the chart using Chart Style 3.

35. Copy the chart to a new Word Document, keeping the source formatting.

36. In a brief memo, explain to the CEO of Computer Warehouse how many servers will need to

be hosted by the new warehouse for the company to break-even. 37.Save the Word document as WarehouseMemo.docx

6 CAS 171 Final Project

Part 9 - Scenario Summary

38. Using Scenario Manager, create a new worksheet that shows a summary of the following scenarios:

Scenario Summary

20 $ 160.00

20 35 50 $ 200.00 $ 180.00 $ 120.00

Result Cells:

Number_of_Servers Monthly_Hosting_Fee

$ 3,200.00 $ 4,000.00 $ $ 3,600.00 $ 3,600.00 $ $ (400.00) $ 400.00 $

Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created. Changing cells for each scenario are highlighted in gray.

Part 10 - Pivot Table and Pivot Chart

6,300.00 $ 6,000.00 5,250.00 $ 6,900.00 1,050.00 $ (900.00)

Total_Revenue Total_Expenses Net_Income

Current Values: Plan 1 Plan 2 Plan 3

Changing Cells:

Computer Warehouse needs to know which part of the country would be the best location for their new warehouse. They have decided to build their server warehouse in whichever State has the most customers that have historically purchased the most servers. You will analyze the shipping data contained in the database to determine this.

Description Server

Row Labels Count of Item #

MO 8 OR 3 SD 3 Grand Total 14

39.Insert a new worksheet at the end of your WarehouseAnalysis.xlsx workbook named Shipping Data.

40.Import data from the Items By State query in the ComputerWarehouse.accdb Access database as a table in your Shipping Data worksheet.

41.Use the imported data as the source for a new Pivot Table. 42.Name with Pivot Table worksheet Servers by State.

10 8 6 4 2 0

Total Sever Sales

MO OR SD

T otal

7 CAS 171 Final Project

43.Create a pivot table with the following properties: Rows: State

Values: Count of Item #

Filters: Description (Show servers only)

44.Use the pivot table to create a pivot chart titled Servers by State.

45.Copy the chart into your existing Word memo, and write a brief paragraph identifying the State

where the company should build their warehouse.

Part 11 - Company Expansion

Data File: CW_Financial.xlsx

Computer Warehouse has lined up financial backing to expand the company. The company will need additional capital to fund the expansion. Analyze the conditions for the company's business loan.

Computer Warehouse

Loan Analysis

Annual Interest Rate

3.5%

Financial Value Business Loan Future Value Years Payments per Payments Annual Rate per Quarter Quarterly (PV) (FV) Year (NPER) Rate (RATE) Payments

Quarterly Payment (PMT) $ 325,000 $ - 5

4 20 3.50%

0.88%

(PMT)

$ (17,784)

Future Value (FV) $ 325,000

Payments (NPER) $ 325,000 $ - 6.032145

$ (60,564)

5

4 20 3.50%

4

24.13

3.50%

Business Loan (PV)

$ 274,121

$-5

4 20 3.50%

0.88% $

0.88% $

0.88% $

(15,000)

(15,000)

(15,000)

46.Open up CW_Financial.xlsx. Rename the file as Warehouse_Expansion. 47.Click the Loan worksheet.

48.In cell B4, enter 3.5% as the annual interest rate of the loan.

49.In cell B7, enter $325,000 as the amount of the business loan.

50.In cell C7, enter 0 (zero) for the future value of the loan.

51.In cell D7, enter 5 as the length of the loan in years.

52.In cell E7, enter 4 as the number of quarterly payments per year.

53.In cell F7, calculate the total number of loan payments. In this case, four loan payments per

year, for five years.

54.In cell G7, use a reference formula to display the annual interest rate specified in cell B4. 55.In cell H7, calculate the rate per quarter.

56.In cell I7, using the PMT function, calculate the payment due each quarter.

8 CAS 171 Final Project

The quarterly payments are higher than anticipated. Determine how much of the loan would be unpaid after five years with quarterly payments of $15,000. You can calculate the amount left on the loan using the Future Value.

57.In cell B8, enter $325,000 as the size of the loan.

58.Copy the values and formulas from D7:H7, to D8:H8.

59.In cell I8, enter a negative -$15,000 as the size of the quarterly payments. 60.In cell C8, using the FV function calculate the future value of the loan.

Using the NPER function, calculate the number of payments required to reach the investment goal.

61.Copy the present and future values of the loan in the range B7:C7, to the range B9:C9. 62.In cell E9, enter 4 to specify that payments are made quarterly.

63.Copy G8:I8 to the range G9:I9.

64.In cell F9, using the NPER function calculate the required number of payments.

65.In cell D9, divide the total number of payments by the number of payments per year to determine the number of years needed to repay the loan.

The company does not want to take more than six years to repay a business loan. Calculate the present value of the loan that will be repaid within five years at $15,000 per quarter.

66.Copy the range C7:H7 to C10:H10.

67.In cell I10, enter a negative -$15,000 as the quarterly payment amount.

68.In cell B10, using the PV function calculate how much the loan would be if the quarterly

payment was -$15,000.

9 CAS 171 Final Project

Part 12 - Amortization Schedule

After analyzing the loan information, Computer Warehouse has decided to borrow $274,000 to be repaid at 3.5% interest in quarterly payments over the next five years of operations. Create an Amortization Schedule to determine how much of the quarterly payment is allocated to interest.

Loan Schedule

$274,000 3.50% 4 0.88% 5 20

Amortization Schedule

($14,993)

Loan (PV) Annual Rate Payments per Rate per Period Years Payments Payment Year (RA TE) (NPER) (PMT)

Year Period Remaining Interest Principal Total Principal Payment Payment Payment

1

1

1

1

2

2

2

2

3

3

3

3

4

4

4

4

5

5

5

5

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

$ 274,000

261,404

248,698

235,881

222,951

209,909

196,752

183,480

170,092

156,587

142,964

129,221

115,359

101,375

87,268

73,039

58,684

44,204

29,598

14,863

$ (2,398)

$ (2,287)

(2,176)

(2,064)

(1,951)

(1,837)

(1,722)

(1,605)

(1,488)

(1,370)

(1,251)

(1,131)

(1,009)

(887)

(764)

(639)

(513)

(387)

(259)

(130)

$ (12,596)

$ (12,706)

(12,817)

(12,929)

(13,043)

(13,157)

(13,272)

(13,388)

(13,505)

(13,623)

(13,742)

(13,863)

(13,984)

(14,106)

(14,230)

(14,354)

(14,480)

(14,607)

(14,734)

(14,863)

$ (14,993)

$ (14,993)

(14,993)

(14,993)

(14,993)

(14,993)

(14,993)

(14,993)

(14,993)

(14,993)

(14,993)

(14,993)

(14,993)

(14,993)

(14,993)

(14,993)

(14,993)

(14,993)

(14,993)

(14,993)

69.Go to the Loan Schedule worksheet.

70.In cell A5, enter $274,000. In cell B5, enter 3.5%. In cell C5 enter 4.

71.In cell D5 calculate the period rate per quarter.

72.In E5, enter 5 years. In F5 calculate the total number of payments.

73.In G5, using the PMT formula calculate the quarterly payment.

74.In cell C9, refer to cell A5 to display the loan principal.

75.In cell D9, using the IPMT function, calculate the interest due for the first payment. 76.In cell E9, using the PPMT function, calculate the portion of the payment applied to the

principal in the first period.

77.In cell F9 calculate the total payment for the first period of the loan. (=Interest Payment added

to the Principal Payment)

10 CAS 171 Final Project

78.In cell C10, add the loan amount in C9 to the principal payment in E9.

79.Copy the range D9:F9 to the range D10:F10.

80.Using the fill handle copy the formula in the range C10:F10 to the range C11:F28. Fill Without

Formatting.

81.In cell C29, calculate the final balance of the loan after the final payment. The balance should

be 0.

Part 13 - Cumulative Interest & Principal Payments

For tax purposes the corporate accountant has requested a financial statement to show the amount paid toward interest and principal over the year. Calculate the Cumulative Interest and principal Payments.

Cumulative Interest and Principal Payments per Year

Year 1 Year 2 Year 3 Year 4 Year 5

82.In cell B36, using the CUMIPMT function calculate the cumulative interest payments for the first year.

83.In cell B37, using the CUMPRINC function calculate the principal payments in the first year. 84.Copy the formulas in the range B36:B37 to the range C36:F37.

85.Select the range G36:G37 and choose AutoSum.

86.In cell B38, add the Loan Amount in A5, to the cumulative principal payment in B37. Make A5

an absolute.

87.In cell C38, calculate the remaining principal at the end of Year 2 by adding year 1 principal to

the year 2 principal payment.

88.Copy the formula in cell C38 to the range D38:F38. (At the end of the fifth year, the principal

remaining is zero since the loan is paid off.)

Principal Remaining

Interest

Principal

1

4

($8,925)

($51,049)

$222,951

5

8

($7,115)

($52,859)

$170,092

9

12

($5,240)

($54,734)

$115,359

13

16

($3,299)

($56,674)

$58,684

17

20

($1,289)

($58,684)

$0

Total

($25,868)

($274,000)

11 CAS 171 Final Project

Quarters

Part 14 - Deprecation

Computer Warehouse is projecting $100,000 in tangible assets. The useful life of these assets is estimated at 10 years with a salvage value of $25,000. Calculate the Deprecation of the company's assets.

Computer Warehouse

Depreciation

Long-Term Assets (Cost)

$ 100,000

Salvage Value (Salvage)

$ 25,000

Life of Asset (Life)

10

Year

Straight-Line (SLN)

12345

Yearly Depreciation

Cumulative Depreciation

Depreciated Asset Value

Yearly Depreciation

Cumulative Depreciation

Depreciated Asset Value

)

$

$

7,500 7,500

7,500 15,000

92,500 $ 85,000

12,900 11,236

12,900 24,136

87,100 $ 75,864

$

$

7,500

22,500

77,500

Year

9,786

33,922

66,078

$

$

7,500

30,000

70,000

8,524

42,446

57,554

$

$

7,500

37,500

62,500

7,424

49,871

50,129

Declining Balance (DB

12345

89.Go to the Depreciation worksheet.

90.In cell B4, enter $100,000.

91.In cell, B5 enter $25,000 as the salvage value.

92.In cell B6, enter 10 as the useful life of the asset.

93.In cell B10, using the SLN function calculate the straight-line deprecation in year 1. Copy and

paste the formula to the range C10:F10.

94.In cell B11, refer to the deprecation for the first year in cell B10.

95.In cell C11, add the Year 2 deprecation to the depreciation from Year 1. Copy and paste the

formula to the range D11:F11.

96.In cell B12 calculate the deprecated asset value after the first year. =$B$4-B11. Copy and

paste the formula to range C12:F12.

97.In cell B16, using the DB function, calculate the Declining Balance deprecation. Copy and

paste the formula to the range C16:F16. 98.Copy the formula in B11:F12 to B17:F18. 99.Save and close the file.

12 CAS 171 Final Project

Part 15 - Turn in Your Work

100. Upload the below files to the Final Project folder. Include the following:

ComputerWarehouse.xlsx

ComputerWarehouse_Macro.xlsm CW_PDF.pdf

WarehouseAnalysis.xlsx

WarehouseMemo.docx

Warehouse_Expansion.xlsx

13 CAS 171 Final Project