Screenshot Tops, Inc. Case Study IN EXCEL (with equations)

Requirements:

1. Think of this as a presentation to a bank or a supervisor at work.

2. The final product that you turn in should be professional:

a. All budgets and schedules should be formatted correctly with proper headings.

b. Each budget should have its own tab in the excel workbook.

c. Schedules and answers should be free from math, spelling, and grammatical errors.

d. Final submission should look good and be very readable (groupings, commas, underlines, tables, etc.)! e. We will follow

3. There are definite solutions to each part of the case study, answers must be correct. To assist you, the following check figures are provided.

Check Figures:

Total sales $ 800,000

Total collections $ 742,400

Total purchases $ 545,000

Total payments $ 464,000

Total labor hours 6,000

Total fixed selling and admin $ 23,280

Total variable overhead $ 46,650

Income before taxes $ 24,670

Ending cash $ 20,380

Total assets $ 137,270

Your calculations/final numbers should match those above. You will lose major points for incorrect amounts, or if you fail to have the supporting formulas (simply keying in the above numbers will not result in awarding of associated points). You should also link your schedules to previous schedules where appropriate.

4. This project must be completed in Excel, with narrative answers (question #1) provided in Word. (You cannot use the Apple apps Pages and Numbers or other software programs. Microsoft Office 365, which includes Word and Excel is available for free to college students see the start here section in D2L.)

5. Question #1 in the narrative will be answered in MS Word. You will have a separate tab for each question numbers 2-12. You will complete questions 13-16 on a single tab with each question and answer clearly labeled and separated.

6. As noted in Item 3 above, you must use formulas where formulas would logically occur, as well as correct formats, underlining, etc. in your worksheets. You should link those portions of the worksheet to the previous sections as support and build supporting calculation formulas as needed. Failure to use formulas and proper linkage will result in the loss of points.

7. You may work on this in a group or on your own. If you work in a group, each group member must fill out a peer evaluation form and your grade will be based on your effort put forth by you as an individual member according to your peers. No single team members grade can exceed the group grade; however, individual grades can be awarded fewer points based on the feedback received. Formatting Guidelines: The dollar sign should appear only in the first entry in each column and not again until the total or subtotal line. Unless the initial number is not in dollars, in which case, when switching from units to dollars the dollar sign should be used. Use commas for numbers over 1,000. Use horizontal lines to separate headings from column entries and to highlight totals and subtotals (see Chapter 8 exhibits). Ensure that cells are formatted to the proper size for text to display fully in the cell. Be consistent about your use of decimal points. Either use them on all numbers in a table or dont use them at all. If you use decimal points, format to two decimal points. All calculations not included in the budget/schedule (formula based) should be separated from the schedule clearly either to the right or below. Calculations (not contained within a formula in a schedule) must be clearly labeled and easy to follow.