90.2K

Verified Solution

Link Copied!

Spreadsheet Project

OVERVIEW:

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:

   	A	B
   1	... INPUT SECTION ...
   2
   3	Company Name	MVP, Inc.
   4
   5	Item	Amount ($)
   6	Sales Adjustments	$325,000
   7	Purchases	$800,000
   8	Cost of Goods Purchased	$900,000
   9	Prior Period Adjustment, net of tax (+/-)	$130,000
   10	Tax on Cumulative Effect Adjustment (+/-)	- $60,000
   11	Retained Earnings, 1/1/19 - adjusted	$470,000
   12	Dividends Declared	$70,000
   13
   14
   15	Item	Amount (%)
   16	Net Sales as a % of Sales	80.0
   17	Gross Profit as a % of Cost of Goods Sold	30.0
   18	Purchases Adjustments as a % of Net Purchases	25.0
   19	Inventory, 12/31/19 as a % of Cost of Goods Available	20.0
   20	Tax Rate for Prior Period Adjustments	35.0
   21	Tax Rate for Cumulative Effect Adjustments	40.0
   22	Net Income as a % of Cost of Goods Sold	15.0
   23	Appropriated Retained Earnings, 12/31/19 as a % of Unappropriated Retained Earnings, 12/31/19	10.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:

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 $$$,$$$,$$$

OUTPUT SECTION. Put "Heading Here Put "Heading Here 4 Retained Earnings, 1/1/19 Prior Period Adjustment Tax Cumulative Effect Adjustment Tax Retained Earnings, 1/1/19 - adjusted Add Inventory, 1/1/19 Purchases Less: S$$,SSS,$SS SSS,S$$.$S$ SSS,SSS,SSS SSS,sss.s$s SSS,SSS,SSS $$$,SSS,$S$ Purchases Adjustments SSS,SSS,$S$ Net Purchases 13 Freight-In COGPurchased COGAvailable Net Income S$$,SSS,S$S 15 15 ess: ess Dividends Declared Inventory, 12/31/19 Retained Earnings, 12/31/19 S$$,SSS,$$S COGS Unappr. RE Appr. RE 20 Put "Heading Put "Heading'" Here 23 Sales 25 26 27 28 29 Less Sales Adjustments Net Sales COGS Gross Profit SS$ S$$ S$S

Answer & Explanation Solved by verified expert

See Answer