Abc Assignment Help

Income Statements Using Spreadsheet Program: Healthcare Product Mart

Assessment item 1—Developing Office Skills —Term 3 2018 COIS11011 Foundations of Business Computing

Objective

This assessment item relates to the unit learning outcome 6 as stated in the ‘Unit Profile’. The purpose of this assessment item is to give you an opportunity to demonstrate the use of selected features in a word processor and a spreadsheet program. It allows you to apply acquired practical knowledge to a specific business scenario.

Details

General advice

Read the case notes carefully and fully. Read them through several times, make notes, diagrams etc. as you go, to assist with understanding the situation. Ensure you understand the problem as completely as possible, and what you have to do, before starting work. Once you understand the assignment and what it entails, read the ‘Report requirements’ section for how the report needs to be presented. Then, review the marking guide noting the criteria listed so that you understand how it will be assessed.

Do not wait until you have ‘mastered’ word processing or spreadsheets before starting. You will not have time to master the applications and in any case, completing the assignments only needs a comparatively small number of core features or tools in each application.

A suggested way is to learn-as-you-go. Open the applications and one by one, learn how to use the features that are actually required in the assignment. This knowledge becomes your core skill set that you can expand on as needed in the future.

Key Spreadsheet skills to learn

IF function, Cell Formatting, Chart Wizard, Absolute cell referencing, Formulas, SUM function, Worksheet Formatting & layout, and any other as may be required to complete the assignment.

Key Word processing skills to learn

Setting font & point sizes, auto-generation of Table of Contents, Breaks (page breaks and section breaks), using Headers & Footers, spelling & grammar checking tool, document formatting and layout, any other as may be required to complete the assignment.

YouTube videos are available in the Microsoft Office Learning block of the Moodle unit website to help you learn and execute these skills in your assignment.

Now go through the details below, get started, and good luck!

Best Healthcare Product Mart (Case is adapted from MIS Cases 3rd Edition by Lisa Miller, 2007, Pearson Prentice Hall, New Jersey)

Case Background

Best Healthcare Product Mart is a family-owned

Answer

Foundations of Business Computing

 Developing Office Skills

Executive Summary

The purpose of this report is to demonstrate the use of various features of the word processor and the excel spreadsheet program. It encourages students to apply their acquired practical knowledge over the course to a specific business scenario, i.e. budgeting and forecasting.

This report includes the statement of operations for the base period on the basis of which the sales, expenses and profits for the future periods is forecasted. 

 The report has been made keeping in mind that the owners or the management is planning on expansion of the business through renovation. As it is a major business decision involving heavy expenditure, it is important to analyze the requirements and impacts of such activity on the business in long run. 

As per the requirement, the income statements are prepared as a worksheet with input and information sections, using basic formulas and formatted properly in a way that it can be used to analyze the plan and future projections by the business partners and employees. The various alternatives, mentioned in the question, has also been taken into consideration and separate worksheets have been prepared for each alternative which can be used by the business partners to perform ‘what if’ analysis. 

Through this report, we have tried to present and review the income statement of the company in the present and the future period using the budgeting system of the company, analyze the impacts of the possible expansion plan and recommend steps of improvement, if required. 

Table of Content

  1. Introduction                                                                                                          Pg. 4
  2. Discussion of Worksheet A                                                                                 Pg. 5
  3. Discussion of Worksheet B                                                                                 Pg. 5-6
  4. Discussion of Worksheet C                                                                                 Pg. 6
  5. Discussion of Worksheet D                                                                                 Pg. 6-7
  6. Discussion of Worksheet E                                                                                  Pg. 7-8
  7. Analysis of the pie chart                                                                                      Pg. 8
  8. “What if” Analysis                                                                                                 Pg. 9
  9. Absolute Referencing                                                                                           Pg. 9
  10. Summary and Recommendation                                                                       Pg. 10 
  11. Reference                                                                                                               Pg. 10

                                                                                                             

Introduction

Brian Brown is the owner of Best Healthcare Product Mart. It is a family-owned chemist warehouse which is experiencing a growth in customer numbers and sales. Brian wants to expand the business by renovating the mart building. 

As renovation of the building is a capital expenditure and it will have an impact over the operations of the business in not only the current year but also in future periods. Hence, to properly analyze the impact of such renovation on the costs and profits of the business, Brian wants to prepare and review the Statement of Operations of the current period, i.e. 2017 and the budgeted statements of the future periods- 2018, 2019 and 2020. 

All the projections are made using the 2017 sales figures as a base point, to calculate the budgeted sales, cost of goods sold, expenses, taxes, and net income for 2018, 2019 and 2020. 

Separate Sections are created for every item of the income statement, i.e. tax, wages, salary, common costs, etc. By doing this, it is very easy to not only understand the calculation and the data used to arrive at the final figures, but also, if necessary, the worksheet can be easily altered as per the changing rate and requirements, thus facilitating the decision-making activities.

In order to fulfill the purpose of the report, the income statements have been prepared using the spreadsheet program, with data provided in the problem statement. A pie chart has also been added at the end of the assignment to present a comparative analysis of sales figure.

Discussion of Worksheet A

The starting point of creating the worksheet is the Gross Sales figure which is an input in B43. Keeping B43 as base for the calculation, every other figure is calculates as per the information provided by Brian. 

The number of employees in this case is 16. The wage rate is $12 per hour. Number of working hours per week is 40 hours and number of weeks are 50. 

The growth rate projected by the management in lieu of the renovation activity is as follows-

2018 GrowthRate - 6.50%

2019 GrowthRate - 7.50%

2020 GrowthRate - 8.00% 

As per the prescribed growth rate, there has been a slight decrease in net income in the year 2018 but it has increased afterwards in the year 2019 and 2020.

Discussion of Worksheet B 

The starting point in this worksheet is also the same, i.e. 2017 sales figure. However, the expected sales growth rate has been changed a bit by the management as an alternative financial planning. The changes rates are as follows-

2018 GrowthRate – 9.00%

2019 GrowthRate - 9.50%

2020 GrowthRate - 10.00% 

 The number of employees in this case is 16. The wage rate is $12 per hour. Number of working hours per week is 40 hours and number of weeks are 50. 

As per the prescribed growth rate, there has been a slight decrease in net income in the year 2018 but it has increased afterwards in the year 2019 and 2020.

                                                                                

Discussion of Worksheet C

The starting point in this worksheet is also the same, i.e. 2017 sales figure. However, the expected sales growth rate has been changed a bit by the management as an alternative financial planning. The changes rates are as follows-

2018 GrowthRate - 4.00%

2019 GrowthRate - 5.00%

2020 GrowthRate - 5.50% 

 The number of employees in this case is 16. The wage rate is $12 per hour. Number of working hours per week is 40 hours and number of weeks are 50. 

As per the prescribed growth rate, there has been a slight decrease in net income in the year 2018 but it has increased afterwards in the year 2019 and 2020.

                                                                                

Discussion of Worksheet D

The starting point in this worksheet is the same, i.e. 2017 sales figure.  

The sales growth rate is same as 2017, which is as follows-

2018 GrowthRate - 6.50%

2019 GrowthRate - 7.50%

2020 GrowthRate - 8.00% 

However, the items that changed in this case are Brian’s salary and employee’s hourly wage rate. Brian’s salary went down from 15% to 8% of gross profit of that respective year, from 2017 to 2020. The employee’s wage rate increased from $12 to $14 per hour.

The number of employees in this case is still the same, i.e. 16. Number of working hours per week is 40 hours and number of weeks are 50. 

After changing all the required parameters, there has been a slight decrease in net income in the year 2018 but it has increased afterwards in the year 2019 and 2020.

Discussion of Worksheet E

The starting point in this worksheet is the same, i.e. 2017 sales figure.  

The sales growth rate is same as 2017, which is as follows-

2018 GrowthRate - 6.50%

2019 GrowthRate - 7.50%

2020 GrowthRate - 8.00% 

Brian’s salary is same as Worksheet A, i.e. 15%, and employee’s hourly wage rate is same as worksheet D, i.e. $14. However, the item that changed in this case is the number of employees. The number of full-time employees increased from 16 to 18 employees, from 2017 to 2020. 

Number of working hours per week is 40 hours and number of weeks are 50. 

After changing all the required parameters, there has been a slight decrease in net income in the year 2018 but it has increased afterwards in the year 2019 and 2020.

Analysis of the Pie Chart

As per the instructions, the below diagram represents the sales figure pertaining to the year 2017, as per worksheet A.

We can conclude that the maximum portion of sales is from Krill & Fish Oils and the least contribution is from the sales of Dental products.

“What if” Analysis

In all the worksheets, row 39 to row 61 are the input provided in the problem statement of the basis of which all the calculations are done and figures has been recorded in the Statement of Operations. The main area of the worksheet which comprises of the final Statement of Operations, from row 1 to row 36, is all based on the below mentioned inputs. Almost every cell has an applied formula, which is linked to the inputs from row 39 to row 61. 

Hence, if any of the parameters or rates changes like tax rates, or if the management wants consider different alternatives by changing a few rates and figures for decision making, the only thing that needs to changes are the input factors and the management will be able to get a new statement on the basis of the changed input figures.

Absolute Referencing

An absolute cell reference is a cell reference in a spreadsheet application that remains constant even if the shape or size of the spreadsheet is changed, or the reference is copied or moved to another cell or sheet. Absolute cell references are important when referring to constant values in a spreadsheet. [Techopedia (2016)]

Absolute referencing is making a formula absolute by placing a $ symbol in front of the column letter and row number in the formula of cell.

In the worksheet, it has been used wherever the figure is based on the gross sales amount, e.g. all the sales and COGS figures from row 6 to row 25, common cost figure in row 32, etc.

Summary and Recommendations

It is to be noted that even with all the changed parameters, the gross profit of year 2018 tends to decrease in each case from Worksheet A to Worksheet E. The management should pay attention as to how they can avoid a dip in profits by taking necessary steps. 

It can be concluded that an excel spreadsheet is a very efficient and accurate technique to prepare actual as well as forecasted financial statements. It is very easy, time and cost efficient to maintain the same spreadsheet over the years and change the input parameters as per requirements and arrive at the required financial statement. I recommend that the management should continue to use this technique.

Customer Testimonials