PRACTICE 2
1. The Moore Distributor Company, Inc. has just received a franchise to distribute dishwashers. The company started business on January 1, 19A, with the following assets:
Cash $45,000
Inventory 94,000
Warehouse, office, and equipment 800,000
All facilities and equipment have a useful life of 20 years with 0%, 10%, 20% residual value (use option button). Depreciation would be charged quarterly and with exclusive/inclusive (check box).
First-quarter sales are expected to be $360,000 (scroll bar with 10,000 increments) and should be doubled in the second quarter and tripled in Third-quarter over previous quarter.
One percent of sales are considered to be uncollectible.
The gross profit margin should be 30 percent.
Variable selling expenses are budgeted at 12 percent of sales.
Fixed selling expenses at $48,000 per quarter, Variable administrative expenses are expected to be 3 percent of sales, and
Fixed administrative expenses should total $34,200 per quarter, exclusive/inclusive (check box) of depreciation.
Required
Prepare a budgeted income statement for the first, second and third quarter of 19A with the following details:
1 Define layout of Financial Model (10)
2 The output would be Net Income.
3 Number and text formatting is required. (3)
4 Define data validation on INPUTS (2)
5 Insert Graphical Tools where required. (3)
6 Define Data Table with 1 Input and 3 outputs of your choice. Use Scroll Bars. (4)
7 Define Data Table with 2 Inputs and 1 output of your choice Use Scroll Bars. (4)
8 Define 3 Scenario of your choice with Gross Profit and Net Income as output for the summary report. (4)
Cash $45,000
Inventory 94,000
Warehouse, office, and equipment 800,000
All facilities and equipment have a useful life of 20 years with 0%, 10%, 20% residual value (use option button). Depreciation would be charged quarterly and with exclusive/inclusive (check box).
First-quarter sales are expected to be $360,000 (scroll bar with 10,000 increments) and should be doubled in the second quarter and tripled in Third-quarter over previous quarter.
One percent of sales are considered to be uncollectible.
The gross profit margin should be 30 percent.
Variable selling expenses are budgeted at 12 percent of sales.
Fixed selling expenses at $48,000 per quarter, Variable administrative expenses are expected to be 3 percent of sales, and
Fixed administrative expenses should total $34,200 per quarter, exclusive/inclusive (check box) of depreciation.
Required
Prepare a budgeted income statement for the first, second and third quarter of 19A with the following details:
1 Define layout of Financial Model (10)
2 The output would be Net Income.
3 Number and text formatting is required. (3)
4 Define data validation on INPUTS (2)
5 Insert Graphical Tools where required. (3)
6 Define Data Table with 1 Input and 3 outputs of your choice. Use Scroll Bars. (4)
7 Define Data Table with 2 Inputs and 1 output of your choice Use Scroll Bars. (4)
8 Define 3 Scenario of your choice with Gross Profit and Net Income as output for the summary report. (4)