This computer project involves the calculation of various

This computer project involves the calculation of various items associated with the Income and Retained Earnings Statements. A spreadsheet is to be designed to accept input in one section, and then produce output in another section. However, the output section should be constructed such that any change to the input will result in the output being recomputed.


INSTRUCTIONS:

  1. Prepare a data input section in the exact order as given below. The naming of the input 'sheet' should be "Input". Do NOT cell protect the numeric data of this section, as you must allow for the possibility of change to this information. Use the amounts given as a test of your output. Enter Pct as the %, not decimal (80% entered as 80, not 0.80). Your input should be placed in the following rows/columns (the heading "INPUT SECTION" should be centered over all columns to which it relates) and in the color scheme shown:

     

     AB
    1... INPUT SECTION ...
    2
    3Company NameMVP, Inc.
    4
    5ItemAmount ($)
    6Sales Adjustments$325,000
    7Purchases$800,000
    8Cost of Goods Purchased$900,000
    9Prior Period Adjustment, net of tax (+/-)$130,000
    10Tax on Cumulative Effect Adjustment (+/-)- $60,000
    11Retained Earnings, 1/1/19 - adjusted$470,000
    12Dividends Declared$70,000
    13
    14
    15ItemAmount (%)
    16Net Sales as a % of Sales80.0
    17Gross Profit as a % of Cost of Goods Sold30.0
    18Purchases Adjustments as a % of Net Purchases25.0
    19Inventory, 12/31/19 as a % of Cost of Goods Available20.0
    20Tax Rate for Prior Period Adjustments35.0
    21Tax Rate for Cumulative Effect Adjustments40.0
    22Net Income as a % of Cost of Goods Sold15.0
    23Appropriated Retained Earnings, 12/31/19 as a % of Unappropriated Retained Earnings, 12/31/1910.0

 

2. Prepare an output section that produces the following items: 1) "Retained Earnings Statement", 2) "COGS Schedule", and 3) "Gross Profit Computation". The reporting period is for the calendar year of 2019. The output items should be placed on a separate 'sheet' (the heading "OUTPUT SECTION" should be centered over all columns to which it relates: [A1..G1]). The naming of the output 'sheet' should be "Output". No number (dollar amount or percentage) or company name should be typed (hardcoded) directly into any cell in the output section, as this would prevent your output from being correct when the input is changed. Instead, the output section must reference the appropriate amounts (dollars and percents) and name from the input and/or output sections. Your output should be placed in the following spreadsheet rows/columns, using the color scheme as shown, where $$$ represent dollar amounts:

 

OUTPUT SECTION. Put Heading Here Put Heading Here 4Retained Earnings, 1/1/19 Prior Period Adjustment Tax Cumulative EffectPut Heading Here 23 Sales 25 26 27 28 29 Less Sales Adjustments Net Sales COGS Gross Profit SS$ S$$ S$S


The "HEADING" for each output item should be centered over all columns in the output for which it relates, and shown in BOLD, ITALICS, and in RED; both the Names and amounts ($$$) that are taken from the input section should be shown in BOLD, ITALICS, and in BLUE. The captions "Add:" and "Less:" should be shown in BOLD, ITALICS, and in RED, with the amounts associated with the caption "Less:" shown as positive numbers, and then subtracted. (see above)Do NOT cell protect any portion of the output sections. Use good form with respect to the creation of the output. This includes heading rules; appearance; alignment; proper use of commas, rulings, $; etc. All dollar amounts should be ROUNDED to the nearest dollar and FORMATTED to show whole dollars only (no cents). COGS should be computed one time, in cell [G29], and then transferred to cell [G18]. The amounts shown in cells [C9] and [C11] represent the net of tax amounts for the prior period and cumulative effect adjustments, respectively. The prior period and cumulative effect adjustments, and their associated tax amounts, as well as the net income amount,  should be shown as either positive or negative numbers as appropriate.

All labels in columns "A" and column "E" are to be left-aligned, except for the descriptions shown "indented". The amounts shown in columns "B", "C", "F", and "G" are to be right-aligned. The "Headings" [A3..D5], [E3..G5], [E21..G23] and the LABELS and AMOUNTS for the unappropriated and appropriated portion of retained earnings ([B19], [D19], [B20], [D20]), should all be centered.

No item should be shown on the spreadsheet in a "box" (no grid lines), except for the unappropriated and appropriated portion of retained earnings labels and amounts. For these items, place a single "box" around cells [B19] and [B20], and then a separate single "box" around cells [D19] and [D20] as follows:

 

Unappr. RE
$$$,$$$,$$$
 
Appr. RE
$$$,$$$,$$$