Excel lab is the perfect tool for the task

Assignment Help Other Subject
Reference no: EM13276868

Excel Lab

Scenario: You are the owner of a small business who wants to project potential profits for the next four years and know that Excel is the perfect tool for the task. Here is the data you have for this past fiscal year:

Note: Read the entire lab instructions prior to beginning the lab. You may use this template or build one of your own. The Excel Lessons cover all of the steps required to complete this lab.

Income

for this year was from Gross Sales and were $525,000

Business expenses were:

Salaries $205,000

Building costs: $45,250

Insurance cost: $28,000

Supply cost: 11% of gross sales

All other: $67,000

Gross Income

this is Gross Sales less expenses.

Taxes

These are 18% of Gross Income

Post Tax Income

This is Gross Income minus taxes

Profit Sharing

Additionally, you have a profit-sharing agreement with your employees that states you will share $10,000 with them whenever your Post Tax Income exceeds $110,000 and $5,000 if it does not. This should change automatically based upon the value of the Post Tax Income.

Net Profit

This is Post Tax Income minus Profit Sharing

A general description of your task:

1. Create a spreadsheet [Worksheet1] that shows the net profit for this year and the profit projections for the next 4 years given the following assumptions:

sales increase is 5% per year   

salaries increase is 5.5% per year

building costs increase is 2% per year

insurance costs increase is 3% per year

supply costs remain steady at 11%

all other costs increase is 3.5% per year

Show the sum of the total profit you expect over the next 4 years.

2. Using the same spreadsheet [use Worksheet2 for this], project what your profits may be if the sales increase is 6% and salaries increase only 4%

3. Using the initial assumptions of 5% sales and salary increase of 5.5% [Use Worksheet3 for this calculation.], determine what increase in sales would be required to have a cumulative profit over the next 4 years of $450,000

4. Again using the initial assumptions of 5% sales and salary increase of 5.5%, create two charts. The first is a pie chart [Use Worksheet4 as the location for the chart objects] that shows the ratio of expenses for the last year of the 4 projected years. The second chart will be a line chart that plots gross sales and net profit for the current and projected years.  

Specific details and requirements:

Rename Worksheet1 with your name, Worksheet2 to What-If, Worksheet3 to Goal-Seek, Worksheet4 to Charts and color the tabs

Use this current calendar year as your starting year

Format the worksheet with colors, $ sign with no decimal places, % set to two decimal places, merge and center the title, etc. to give it a professional look

Ensure the current year and following 4 years appear as labels

Have the $10,000 bonus cell show with a red background and the $5,000 bonus show a green background. {Note: this shading needs to change automatically if the bonus value changes}

Use appropriate titles and legends for the Line and Pie charts. For the Pie Chart %u2018explode%u2019 the slice for supplies and have the slices show just percentages.

Make sure each worksheet will fit on a single printed page with an appropriate heading that appears on each sheet.

Submission Requirements: Upload the Excel file. Name the file lab5

Reference no: EM13276868

Questions Cloud

Behavioral indicators of possible abuse with children : What are the three signs of possible neglect, and the three signs or behavioral indicators of possible abuse with children? What are some of the agencies and what are the reporting procedures?
According to the nasw code of ethics-ed offer : Janna's social worker, Ed is moving to a new agency and ask her if she would like to continue to see him after the move. According to the NASW Code of Ethics, Ed offer could be considered:
Photodynamic therapy : You are an independent consultant, and you have been approached by a company called Gerusa. Gerusa is a company of former Soviet scientists who have developed a method of killing cancer cells with light in a process called Photodynamic Therapy (PDT).
Applied for insurance with state farm : When Sidney Henry applied for insurance with State Farm, he was asked whether he had ever had recurrent indigestion or a hernia.
Excel lab is the perfect tool for the task : You are the owner of a small business who wants to project potential profits for the next four years and know that Excel is the perfect tool for the task. Here is the data you have for this past fiscal year:
Techniques are common to advocacy : Which of the following techniques are common to advocacy?
Based upon probability theory : Using random sampling (based upon probability theory)
Changed the relationship between art and technology : To what extent have advanced and accessible digital technologies, such as websites, digital photography, and YouTube, changed the relationship between art and technology? Are these technologies reshaping our attitudes toward artists?
How do you communicate with the people you work for : You will interview a programmer to find out how IT professionals view communication. You can find a programmer in any field%u2014it does not need to be a programmer working in the healthcare or public health field.

Reviews

Write a Review

Other Subject Questions & Answers

  Cross-cultural opportunities and conflicts in canada

Short Paper on Cross-cultural Opportunities and Conflicts in Canada.

  Sociology theory questions

Sociology are very fundamental in nature. Role strain and role constraint speak about the duties and responsibilities of the roles of people in society or in a group. A short theory about Darwin and Moths is also answered.

  A book review on unfaithful angels

This review will help the reader understand the social work profession through different concepts giving the glimpse of why the social work profession might have drifted away from its original purpose of serving the poor.

  Disorder paper: schizophrenia

Schizophrenia does not really have just one single cause. It is a possibility that this disorder could be inherited but not all doctors are sure.

  Individual assignment: two models handout and rubric

Individual Assignment : Two Models Handout and Rubric,    This paper will allow you to understand and evaluate two vastly different organizational models and to effectively communicate their differences.

  Developing strategic intent for toyota

The following report includes the description about the organization, its strategies, industry analysis in which it operates and its position in the industry.

  Gasoline powered passenger vehicles

In this study, we examine how gasoline price volatility and income of the consumers impacts consumer's demand for gasoline.

  An aspect of poverty in canada

Economics thesis undergrad 4th year paper to write. it should be about 22 pages in length, literature review, economic analysis and then data or cost benefit analysis.

  Ngn customer satisfaction qos indicator for 3g services

The paper aims to highlight the global trends in countries and regions where 3G has already been introduced and propose an implementation plan to the telecom operators of developing countries.

  Prepare a power point presentation

Prepare the power point presentation for the case: Santa Fe Independent School District

  Information literacy is important in this environment

Information literacy is critically important in this contemporary environment

  Associative property of multiplication

Write a definition for associative property of multiplication.

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd