8/13/2019 Project in Excel - Set A.doc

http://slidepdf.com/reader/full/project-in-excel-set-adoc 1/2

BRAC UNIVERSITY

BRAC BUSINESS SCHOOL

Course Name: Managerial Accounting Control

Course Co!e: ACT "#$% Summer $#&&

Assignment'(ro)ect in E*celSet A

Time Allo+e!: $ ,ours

Name o- Stu!ent:

 ............................... 

I/ 0

Mynor Company, an office supplies specialty store, prepares its master budget on aquarterly basis. The following data have been assembled to assist in preparing the master

 budget for the first quarter:

As of December the end of the prior quarter!, the company"s balance sheet showed thefollowing account balances:

a.Cash 50,000$ Accounts recceivables 222,000 Inventory 60,000 Buildings and equipment net! "#0,000  Accounts payables ",000$Capital stoc% 500,000 &etained earnings '0,000 

#02,000$ #02,000$

 b. Actual sales for December and budgeted sales for the ne#t four months are asfollows:

(ecember actual! 2)0,000$*anuary +50,000$ebruary #+0,000$

-arch "00,000$

 April "00,000$

c. $ales are %&' for cash and (&' on credit. All payments on credit sales are collectedin the month following sale. The accounts receivable at December )* are a result ofDecember credit sales.

d. The company"s gross margin is +&' of sales. n other words, cost of goods sold is

-&' of sales!.e. Monthly e#penses are budgeted as follows: salaries and wages, %/,&&& per month0

advertising, /&,&&& per month0 shipping 1' of sales0 other e#penses, )' of sales.Depreciation including depreciation on new assets acquired during the quarter will

 be +%,&&& for the quarter.f. 2ach months ending inventory should equal %1' of the following months cost of

goods sold.g. 3ne4half of a month"s inventory purchases are paid for in the month of purchase0 the

other half is paid in the following month.h. During 5ebruary, the company will purchase a new copy machine for *,/&& cash.

During March, other equipment will be purchased for cash at a cost of (+,1&&.i. During 6anuary, the company will declare and pay +1,&&& in cash dividends.

7ro8ect in 2#cel 9 $et A 7age * of %

8/13/2019 Project in Excel - Set A.doc

http://slidepdf.com/reader/full/project-in-excel-set-adoc 2/2

 8. The company must maintain a minimum cash balance of )&,&&&. An open line ofcredit is available at a local ban for any borrowing that may be needed during thequarter. All borrowing is done at the beginning of a month and all repayments aremade at the end of a month. ;orrowings and repayments of principal must be inmultiples of *,&&&. nterest is paid only at the time of payment of principle. Theannual interest rate is *%' 5igure interest on whole months, e.g. *<*%, %<*%, etc!.

Re1uire!:

=sing the data above, prepare the following statements and schedules for the first quarter:*. $chedule of e#pected cash collections%. Merchandise purchases budget). $chedule of e#pected cash disbursements for merchandise purchases+. $chedule of cash disbursement for operating e#penses1. Cash budget-. ncome statement

/. ;alance sheet as of March )*

7ro8ect in 2#cel 9 $et A 7age % of %