Project For Management and Accounting

QUESTION 1 (40 marks)

You have been approached to invest in an online store that rents DVD and delivers them

to customers. You have researched the business and want to forecast profits for the next

four years.

The online store offers the following plans for the customers.

1. 10.95$ per month. (1 DVD out at a time) – Plan A

2. 18.95$ per month. (2 DVD out at a time) – Plan B

3. 24.95$ per month. (3 DVD out at a time) – Plan C

4. 36.95$ per month. (6 DVD out at a time) – Plan D

The cost of having a customer for each monthly plan is expected to be as follows:

Plan Customers

A 2.45$

B 4.90$

C 7.00$

D 12.00$

In the first year, you expect to be able to attract the following number of customers to

each plan:

Plan Customers

A 2000

B 1500

C 1000

D 500

In each of the following 3 years, you expect the number of customers to increase

according to the following table.

Plan Year 2 Year 3 Year 4

A 20% 15% 15%

B 20% 15% 20%

C 15% 10% 10%

D 5% 5% 3%

The average cost for all the plans per customer will decrease each year by 5%.

Your office rent in the first year is $30,000. However, since you have to expand to

support the growth of business your rent will increase at least 5% per year.

Your servers’ administrator is paid $45,000 per year. In addition, you have to spend at

least 4000$ per year to upgrade your servers so you can handle the extra traffic of your

website. Your clerical costs will be $10,000 in the first year and increase 5% in each of

the subsequent years. Office supplies will cost you about 5,000$ per year.

You plan to invest heavily in advertising in the first year, spending an amount equal to

25% of sales. Thereafter, you will spend $4,000 plus 12% of sales. All other costs will

amount to $16,000 plus 3% of sales in each year.

Income tax is payable only if your annual profit exceeds $75,000. If it does, the tax

payable is 55% of the amount of profit exceeding $75,000.


· Include your first and last names as part of the company name in the title of the


· Immediately after the title, specify the number of customers of each plan in the

first year and the annual growth rate for the next 3 years as assumptions whose

values are referenced in the remainder of the worksheet. Therefore, when you

change either or both of these values, all subsequent references to them will also

be changed.

· When calculating the number of accounts per plan sold in years subsequent to the

first, round the result to the nearest integer.

· Note that you are being marked on the appearance, as well as the accuracy, of

your worksheet. Make sure that it has a meaningful title, meaningful row and

column labels, suitably formatted cells and printed gridlines.

1b. A revised worksheet that shows the results of the number of accounts in the first year

and growth according to the following tables. This scenario reflects the optimistic

forecast for the business.

Plan Customers

A 2500

B 2000

C 1000

D 900

Plan Year 2 Year 3 Year 4

A 25% 18% 18%

B 25% 18% 22%

C 20% 14% 12%

D 10% 8% 5%

1c. A revised worksheet that shows the results of the number of accounts in the first year

and growth according to the following tables. This scenario reflects the pessimistic

forecast for the business.

Plan Customers

A 1500

B 1000

C 500

D 200

Plan Year 2 Year 3 Year 4

A 10% 10% 12%

B 20% 10% 15%

C 5% 8% 6%

D 2% 3% 1%

1d. A worksheet resulting from the use of Goal Seek to determine how many accounts

must be sold in the first year of the most likely forecast to yield an after tax profit of

$500,000. To reduce the number of possibilities and uncertainty about number of

accounts sold per plan always consider that number accounts sold per plans relative to

plan D are:

A = 4D

B = 3D

C = 2D

QUESTION 2 (10 marks)

Mr Jones who is a software analysist at IBM has N number of shares of Apple stock. The

apple stock is currently worth $M. The share cost might increase/decrease Y% during a


Write an algorithm that gets N, M and Y as input and calculates Mr. Jones’ total worth of

Apple shares and his benefit (or loss).


Number of Shares

Current price

Growth Rate



QUESTION 3 (10 marks)

Translate the following algorithm into Visual Basic.

Name: myCompanyProfit

Givens: Name, Employee1_Salary, Employee2_Salary, Employee3_Salary,

Administration_Cost, Office_Cost, Revenue

Intermediate: Total

Results: Profit_Loss

Definition: Profit_Loss = myCompanyProfit(Name,Employee1_Salary,

Employee2_Salary, Employee3_Salary, Administration_Cost, OfficeCost, Revenue)


Get Name

Get Employee1_Salary

Get Employee2_Salary

Get Employee3_Salary

Get Administration_Cost

Get OfficeCost

Get Revenue

Profit_Loss = Revenue – (Employee1_Salary + Employee1_Salary + Employee1_Salary

+ Administration_Cost + OfficeCost)

If Profit_Loss > 0

If Profit_Loss > 70% of Revenue

Give Name “, your company is awesome!!”


Give Name “, your profit is modest”.


Give Name “, you are not making any profit!!”

Deliverable List

1 Excel Work Book (7 Worksheets, 2 of them are Module Sheets)

Title Page with Signature file from Assignment 0

Question 1a

Question 1b

Question 1c

Question 1d

Question 2 algorithm (module sheet)

Question 3 in VB (module sheet)

Skills: Accounting, Excel, Management, Visual Basic

See more: you and ibm, write your name in 3d online, write for about and get paid, write a business plan online, write a book and get paid, worksheet in accounting, work seek, work online accounting, work for seek, who to use as references, well referenced, website of accounting, visual basic price, visual basic for website, visual basic cost, visual basic book online, visual basic algorithm, visual basic 6.0 online, visual basic 5 online, vb algorithm, translate work online, translate rate, translate online get paid, translate get paid, translate get by

About the Employer:
( 0 reviews ) Amritsar, India

Project ID: #528301

5 freelancers are bidding on average $111 for this job


Hi! I can help you regarding Question No. 1 only. The price given is only for Question 1. Please contact me and get it done.

$50 USD in 2 days
(16 Reviews)

Hi Please see PM

$125 USD in 3 days
(9 Reviews)

i am keen to do this project for you ..

$30 USD in 3 days
(0 Reviews)

Experience wise I am used to do this kind of work since 1998. If you give me a chance to do this job I am very much ready.

$250 USD in 5 days
(0 Reviews)

Hi Vijay, I am an online tutor for these subjects and can manage it quite easily. If interested, please contact me for further details. Thanks, Shikha.

$100 USD in 4 days
(0 Reviews)