60.1K

Verified Solution

Link Copied!

For this part of the assignment browse through all worksheets before attempting the question.

Using the Microsoft excel workbook assgt_mgmt1000_open_s1_24.xls complete the questions

below to provide solutions for the directors and board members of Rite Tech Corporation,

regarding their financial reporting requirements:

a. Using the 2021 Quarterly Revenue worksheet provide the relevant formulas to

complete the worksheet. Note that values are to be formatted with the thousands

separator, TWO decimal places with $ signs on all totals [6]

b. In the same worksheet in part (a) dynamically apply the following formats for Qtr1

through Qtr4 for all revenue types:

i. Values over $40,000 should be green and bolded [2]

ii. Values under or equals to $15, 000 should be red and italicized [2]c. Create a clustered column chart with all relevant labels, titles and legend while changing the

default colors. The chart is to be inserted in new worksheet called Quarterly Revenue Chart

worksheet. Hint: change the font size on the text areas on the chart to assist in resizing the

chart. [20]

d. Using the Income Statement worksheet, center the financial statement titles

across the data below and bold and italicize the company name. [4]

e. Using the correct method/formula enter the correct values for each revenue item for

2021. [5]

f. Complete the formulas in cells provided for revenues* for 2022. 2022 revenues have

increased by 2.5% over 2021 values. [10]

g. Complete the formulas in cells provided for expenses for 2022. 2022 expenses have decreased

by 1.5% over 2021 values [10]

h. Using the correct function or formula calculate the total expenses and losses in the cells

provided. [4]

i. Insert an exploded pie chart with a 3-D visual effect using all expense items for 2021, in a

new worksheet called, 2021 Expense Comparison. Include all relevant labels, titles and

legends while changing the default colors

m. Using functions, complete the Aggregates worksheet. (Hint: must use Income Statement

worksheet)

[3]

n. Save your MS Excel workbook as Sub-Group#_open_s1_24xl for submission. For example

Sub-Group 1_open_s1_24xl.

j. Using all relevant cells calculate the income tax expenses for each year. [10]

k. Using the correct function or formula calculate the net income. [2]

EmpID	Department	Title	FirstName	LastName	Official Title	Occupation	Phone	Fax
101	Purchasing	Ms	Charlotte	Cooper	Senior Management	Purchasing Manager	(758) 555-2222
102	Sales	Miss	Shelley	Burke	Normal	Order Administrator	(758) 555-4822
104	Sales	Mrs	Regina	Murphy	Normal	Sales Representative	(758) 555-7422
107	Finance & Control	Mr	Antonio	delValle-Saavedra	Senior Management	VP Finance	(758) 555-1002	(758) 455-2005
108	Board of Directors	Dr	Mayumi	Ohno	Board of Directors		(758) 555-5418	(758) 455-2006
110	Distribution	Mr	Ian	Devling	Normal	Marketing Manager	(758) 555-1234	(758) 455-2007
111	Marketing and Advertising	Mr	Peter	Wilson	Normal	Sales Representative	(758) 555-1235	(758) 455-2008
113	Information Technology	Mrs	Lars	Peterson	Senior Management	VP Information Technology	(758) 555-1236	(758) 455-2009
114	Marketing and Advertising	Mr	Carlos	Diaz	Senior Management	VP Finance	(758) 555-1237
116	Operations	Dr	Petra	Winkler	Normal	Sales Manager	(758) 555-1238
117	Marketing and Advertising	Mr	Martin	Bein	Normal	International Marketing Mgr.	(758) 555-1239
119	Board of Directors	Dr	Sven	Petersen	Board of Directors		(758) 555-1240	(758) 455-2013
120	Board of Directors	Mrs	Elio	Rossi	Board of Directors		(758) 555-1241	(758) 455-2015
122	Operations	Mr	Beate	Vileid	Senior Management	VP Operations	(758) 555-1242
123	Human Resourse	Ms	Cheryl	Saylor	Normal	HR Manager	(758) 555-1243
125	Human Resourse	Dr	Michael	Bjrn	Senior Management	President	(758) 555-1244	(758) 455-2017
126	Sales and Distribution	Miss	Guylne	Nodier	Normal	Sales Manager	(758) 555-1245
128	Distribution	Mr	Robb	Merchant	Normal	Wholesale Account Agent	(758) 555-1246	(758) 455-2019
129	Accounts	Mrs	Chandra	Leka	Senior Management	VP Finance	(758) 555-1247	(758) 455-2020
131	Sales	Dr	Thierry	Forde	Senior Management	VP Information Technology	(758) 555-1248
132	Personnel	Mr	Dirk	Luchte	Normal	Personnel Manager	(758) 555-1249
134	Productions	Miss	Anne	Heikkonen	Normal	Product Manager	(758) 555-1250
135	Board of Directors	Mrs	Wendy	Mackenzie	Board of Directors		(758) 555-1251
137	Marketing	Mr	Jean-Guy	Lauzon	Senior Management	Marketing Manager	(758) 555-1252
138	Projects Execution Unit	Mr	Giovanni	Giudici	Board of Directors		(758) 555-1253
140	Green Initiatives	Ms	Marie	Delamare	Normal	Accounting Manager	(758) 555-1254
141	Green Initiatives	Miss	Eliane	Noz	Normal	Accounting Manager	(758) 555-1255
143	Board of Directors	Mrs	Chantal	Goulet	Board of Directors		(758) 555-1256
144	Board of Directors	Ms	Kim	Whow	Board of Directors		(758) 555-1257
146	Board of Directors	Dr	Dan	Giorgon	Board of Directors		(758) 555-1258
147	Board of Directors	Ms	Mia	Manatello-Unix	Board of Directors		(758) 555-1259
149	Sales	Mr	Adrian	Forde	Normal	Sales Agent	(758) 555-1260
150	Treasury	Mrs	Joeann	Grind	Senior Management	Secretary Treasurer	(758) 555-1261
152	Marketing and Advertising	Mrs	Sheena	Brathwaite	Senior Management	VP Marketing	(758) 555-1262
153	Projects Execution Unit	Dr	Manuale	Drugmen	Normal	Order Administrator	(758) 555-1263
155	Information Technology	Mr	Orville	Land	Normal	IT Manager	(758) 555-1264
156	Research & Development	Miss	Xerile	Dackener	Normal	Product Developer	(758) 555-1265
158	Green Initiatives	Mrs	Tiele	Tod	Normal	Accounting Manager	(758) 555-1266	(758) 455-2039
159	Marketing and Advertising	Mr	Trevor	Chrtistie	Normal	Accounting Manager	(758) 555-1267
161	Projects Execution Unit	Dr	Priscilla	Harris	Normal	Order Administrator	(758) 555-1268	(758) 455-2041
162	Research & Development	Ms	Keven	Unical	Senior Management	VP Research & Development	(758) 555-1269
164	Compensation & Benefits	Mr	Don	Juan	Normal	C & B Officer	(758) 555-1270
165	Product Delivery	Mrs	Wazel	Browne	Normal	Accounting Manager	(758) 555-1271	(758) 455-2044
167	Internal Audit	Mr	Quarless	Some	Normal	Senior Auditor	(758) 555-1272
168	Strategic Management	Dr	David	Banner	Senior Management	CEO	(758) 555-1273	(758) 455-2047
170	Sales	Ms	Gregina	Humannie	Normal	Sales Agent	(758) 555-1274
171	Operations	Miss	Kathy	Greene	Normal	Accounting Manager	(758) 555-1275	(758) 455-2048
173	Marketing	Mrs	Margaret	Wilkie	Normal	Marketing Manager	(758) 555-1276	(758) 455-2049
174	Green Initiatives	Mr	Cave	Kellam	Normal	Accounting Manager	(758) 555-1277
176	Product Delivery	Dr	Gena	Kruz	Normal	Product Manager	(758) 555-1278
177	Sales and Distribution	Ms	Roberta	Custelli	Senior Management	VP Sales	(758) 555-1279	(758) 455-2051
179	Health & Safety	Dr	Butch	Daz	Senior Management	HR Manager	(758) 555-1280
180	Procurement	Mrs	Nicole	Callender	Normal	Order Administrator	(758) 555-1281
182	Sales	Miss	Zeena	kelly	Normal	Sales & Marketing	(758) 555-1282
183	Board of Directors	Dr	Nello	Benskin	Board of Directors		(758) 555-1283	(758) 455-2056
185	Sales and Distribution	Ms	Canny	Cuun	Normal	Export Administrator	(758) 555-1284
186	Marketing and Advertising	Miss	Michael	Beene	Normal	Marketing Representative	(758) 555-1285
188	Board of Directors	Mr	Bill	Tudor	Board of Directors		(758) 555-1286	(758) 455-2058
189	Board of Directors	Mr	Alvin	Chippman	Board of Directors		(758) 555-1287	(758) 455-2054
191	Sales and Distribution	Dr	Siven	Trotty	Normal	Sales Agent	(758) 555-1288
192	Purchasing	Ms	Monie	Young	Normal	Purchasing Manager	(758) 555-1289
194	Green Intiatives	Miss	Kriss	Cross	Senior Management	VP Greening	(758) 555-1290
195	Sales and Distribution	Mr	Al B	Surez	Normal	Sales & Marketing	(758) 555-1291
197	Internal Audit	Mrs	Janice	Deed	Senior Management	Director of Internal Audit	(758) 555-1292	(758) 455-2065
198	Board of Directors	Ms	Ayana	Sheralin	Board of Directors		(758) 555-1293
199	Board of Directors	Ms	Keisha	Sharifa	Board of Directors		(758) 555-1294
200	Marketing and Advertising	Ms	Joann	Janet	Senior Management	Marketing Executive	(758) 555-1295
201	Green Initiatives	Mr	Kim	Morris	Normal	Accounting Manager	(758) 555-1296
202	Green Intiatives	Mr	Yasmin	Padmore	Normal	Accounting Manager	(758) 555-1297
203	Green Intiatives	Mr	Dylan	Joseph	Normal	Accounting Manager	(758) 555-1298
205	Green Intiatives	Mrs	Kim	Cumberbatch	Normal	Sales & Marketing	(758) 555-1299
207	Board of Directors	Mr	Bike	Chip	Board of Directors		(758) 555-1287	(758) 455-2054
209	Sales and Distribution	Dr	Laff	Trottoy	Normal	Sales Agent	(758) 555-1288
212	Purchasing	Ms	Kathill	Young	Normal	Purchasing Manager	(758) 555-1289
214	Green Intiatives	Miss	Call	Cross	Senior Management	VP Greening	(758) 555-1290
216	Sales and Distribution	Mr	Al BE	Sures	Normal	Sales & Marketing	(758) 555-1291

l. Format the comparative income statement use dollars signs where necessary. All

values are to have the thousand separator format with ZERO decimal places.

Answer & Explanation Solved by verified expert

See Answer