I You have an online bookstore that offers books on its website. Last week your company...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
I You have an online bookstore that offers books on its website. Last week your company had received 50 orders from 20 customers (readers). Each order includes a single book, purchased either in one or multiple copies. According to the data being described, you are now preparing a weekly sales report in Excel. You will create a table for weekly sales This table will have 7 columns: First column: Day of week (Number, between1 and 7) => Day 1 means Monday, 2 meansTuesday, 3 for Wednesday, and so on. Example: On Friday, a customer with the identity number-62341 (John) has purchased two books with code 17 (Lord of the rings) with a unit price of 3.50$. The following row represents this purchase: 5 62341 17 2 3.50 Lord of the rings John Follow the following steps (af) one by one to prepare the report as described above. Then, solve the remaining questions. e. Second column: An identity number for customer (Number, 5-digits, no decimals) Third column: Product code for book (Number, no decimals) => Each book has a unique number (integer) Fourth column: Units sold (Number, no decimals) Fifth column: Unit price of the book (Number, with two decimals) => all prices are in dollars Sixth column: Book title Seventh column: Customer name a. Before designing the table above, design a table for "books". This table should have 3 columns: Code, Description, and Price. Enter data for 80 books. You are free to pick those from your mind. b. Apply conditional formatting on the price column of "books" table. More expensive items should be automatically colored in green, while cheap items should be colored in white. c. Design a "customers" table with two columns: Customer-Identity-Number, and Customer-Name. Enter data for 20 customers. Give those customers unique codes. Give them names as you wish. d. Design the table for sales records as described above. Make a header row. Then enter data for 50 orders. Fill in data only the first four columns (Don't copy the same data to entire table). Keep the last three columns empty. Go to the "books" table. Insert a column to its right. In this column, calculate total units sold from the books being listed (Hint: use SUMIF). Then, prepare an appropriate chart that compares total units sold from each book. Formulate the last three columns with VLOOKUP. Taking a customer's identity-number, your formula should locate that customer on customers' table, and complete the name on last column. Additionally, formulate book descriptions and prices. (Changing the book code on 3rd column should immediately change 5th and 6th columns.) Create a pivot table on another worksheet that shows the daily order counts. f. Attention: Explain your ideas in Excel. Add comments to describe how you developed your solution. I You have an online bookstore that offers books on its website. Last week your company had received 50 orders from 20 customers (readers). Each order includes a single book, purchased either in one or multiple copies. According to the data being described, you are now preparing a weekly sales report in Excel. You will create a table for weekly sales This table will have 7 columns: First column: Day of week (Number, between1 and 7) => Day 1 means Monday, 2 meansTuesday, 3 for Wednesday, and so on. Example: On Friday, a customer with the identity number-62341 (John) has purchased two books with code 17 (Lord of the rings) with a unit price of 3.50$. The following row represents this purchase: 5 62341 17 2 3.50 Lord of the rings John Follow the following steps (af) one by one to prepare the report as described above. Then, solve the remaining questions. e. Second column: An identity number for customer (Number, 5-digits, no decimals) Third column: Product code for book (Number, no decimals) => Each book has a unique number (integer) Fourth column: Units sold (Number, no decimals) Fifth column: Unit price of the book (Number, with two decimals) => all prices are in dollars Sixth column: Book title Seventh column: Customer name a. Before designing the table above, design a table for "books". This table should have 3 columns: Code, Description, and Price. Enter data for 80 books. You are free to pick those from your mind. b. Apply conditional formatting on the price column of "books" table. More expensive items should be automatically colored in green, while cheap items should be colored in white. c. Design a "customers" table with two columns: Customer-Identity-Number, and Customer-Name. Enter data for 20 customers. Give those customers unique codes. Give them names as you wish. d. Design the table for sales records as described above. Make a header row. Then enter data for 50 orders. Fill in data only the first four columns (Don't copy the same data to entire table). Keep the last three columns empty. Go to the "books" table. Insert a column to its right. In this column, calculate total units sold from the books being listed (Hint: use SUMIF). Then, prepare an appropriate chart that compares total units sold from each book. Formulate the last three columns with VLOOKUP. Taking a customer's identity-number, your formula should locate that customer on customers' table, and complete the name on last column. Additionally, formulate book descriptions and prices. (Changing the book code on 3rd column should immediately change 5th and 6th columns.) Create a pivot table on another worksheet that shows the daily order counts. f. Attention: Explain your ideas in Excel. Add comments to describe how you developed your solution.
Expert Answer:
Related Book For
Advanced Financial Accounting
ISBN: 978-0137030385
6th edition
Authors: Thomas Beechy, Umashanker Trivedi, Kenneth MacAulay
Posted Date:
Students also viewed these accounting questions
-
(a) [10 pts, page 7] State the Universal Approximate theorem of feed forward neural networks (b) [20 pts, page 7] Draw a sparse auto-encoder of 1 hidden layer with 2 hidden nodes where input and...
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
The Crazy Eddie fraud may appear smaller and gentler than the massive billion-dollar frauds exposed in recent times, such as Bernie Madoffs Ponzi scheme, frauds in the subprime mortgage market, the...
-
The Carter Caterer Company must have the following number of clean napkins available at the beginning of each of the next four days: day 1, 1500; day 2, 1200; day 3, 1800; day 4, 600. After being...
-
The wall shear stress w for flow in a narrow annular gap between a fixed and a rotating cylinder is a function of density , viscosity , angular velocity , outer radius R, and gap width r. Using (, ,...
-
Fulkerson Manufacturing wishes to maintain a sustainable growth rate of 8 percent a year, a debt-equity ratio of .85, and a dividend payout ratio of 30 percent. The ratio of total assets to sales is...
-
Define a simple machine.
-
The Shell Oil Company, which began about 1912, had been for decades a household name as a quality oil company in the United States. However, by the late 1970s much of its prestige as a premiere...
-
. A 80-kg astronaut (including spacesuit and equipment) is floating at rest a distance of 11 m from the spaceship. . when she runs out of oxygen and fuel to power her back to the spaceship. . She...
-
Parker, Inc., acquires 70 percent of Sawyer Company for $420,000. The remaining 30 percent of Sawyer's outstanding shares continue to trade at a collective value of $174,000. On the acquisition date,...
-
Find out the various benefits derived from efficient industrial management in the context of Bangladesh Find out the various benefits derived from efficient industrial management in the context of...
-
What does AIDA stand for? Why is AIDA important? What is the significance of AIDA? What is the benefit of AIDA?
-
What are 2 main competitors of Uniqlo? Name them and give two reasons for each that explain why you see them as Uniqlo's main competitor What makes the companies similar, does one have a specific...
-
A company plans to launch two mutually exclusive projects and requires rate of return of 12 %. The cost and expected cash flows from both projects are as follow. YEAR 0 1 2 3 Tesola model X (50,000)...
-
As a financial analyst at Citibank, you want to help your clients to determine how to the income and consumption over this year and next. Refer to the above graph. Point A is $5000 and Point B is...
-
You set out to design an automatic shower with a requirement for control of both the flow of water and the temperature. The system specifications are as follows: Data: Delivered flowrate F = 15 L/min...
-
. Use the Divergence theorem to calculate the flux of F= (x-4y, y-72,2-5x) out of the unit sphere.
-
The Home Depot is the leading retailer in the home improvement industry and one of the 10largest retailers in the United States. The company included the following on its January 29, 2012, balance...
-
Muskoka Furniture Inc. (Muskoka) can also import custom pine furniture but it must be ordered several months in advance as it is only manufactured after a specific request is made. Assume that on...
-
On January 1, 20X4, Parent Ltd. purchased 90% of the shares of Sub Ltd. for $ 975,000. At that time Sub Ltd. had the following SFP: Sub Ltd. Statement of Financial Position January 1, 20X4 The bonds...
-
South Ltd. acquired 100% of the voting shares of North Ltd. In exchange, South Ltd. issued 50,000 common shares, with a market value of $ 10 per share, to the common shareholders of North Ltd. Both...
-
The following is the trial balance of Sanjay Industries Ltd. as on 31st March 2006. Further information 1.Outstanding rent amounted to 7,200 while outstanding salaries 8,100 at the end of the year....
-
Refer to the case of Monik Traders given in the exercises of the last chapter. Monik Varma now wants to know as to where his firm stands after one month of running of the business. Help him. Towards...
-
The accountant of Pushpa Engineering Company Ltd. has prepared the following trial balance of the company as on 31st March, 2006. Further information 1. Authorised equity share capital of the company...
Study smarter with the SolutionInn App