Question
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
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.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started