---

---

The use of spreadsheets with your Atari computer and Epson printer computer system was introduced in Chapter 7. If you haven't read that chapter yet, I suggest you read it before continuing with this chapter.
        When microcomputers were introduced in the late 70s, they were given a lukewarm reception by the business world. They were viewed as hobbyists' machines which served little purpose in the real world of commerce.
        Then came the advent of the electronic spreadsheet.
        At last, the microcomputer could perform a useful function for the financial wizards. It could provide the controller with reports in a few minutes that used to take weeks to create. A great number of businessmen bought microcomputers for the sole purpose of running spreadsheet programs.
        This chapter will cover the use of spreadsheets for inventory control, analysis of advertising campaigns and figuring wages from weekly timecards. These applications may appear simplistic at first, but they are only meant to work as models which can be expanded to fit your personal needs later.


INVENTORY CONTROL

This first application will help you track the production, sale, and existing inventory of your products at the ABC Cookie Company. It is a rather straight-forward spreadsheet application. You'll notice some differences in the notation from Chapter 7. The format and replication commands are embedded within the set of commands. The first three replication commands are used to save time by copying the equations in row 7 throughout each of their respective columns in columns F, H, and I.
        The last replication is used to copy the @SUMming equation in cell D14 to the rest of the cells in that row from columns D through I. Since the equation is not needed in cell G14, it is removed in the last line of commands.

>B1:Inventory
>C1:" Control
>D1:of the AB
>B2:C Cookie
>C2:Company
>A4:" Goods
>C4:Cost per
>D4:" Quant
>E4:" Quant
>F4:" Quant
>G4:" Unit
>H4:" Gross
>I4:" Net
>C5:" Unit
>D5:" Made
>E5:" Sold
>F5:In-Stock
>G5:" Price
>H5:" Income
>I5:" Income
>A7:Chocolate
>B7:" Chip
>C7:/F$ .02
>D7:900
>E7:850
>F7:+D7-E7
>G7:/F$ .05
>H7:/F$ +E7*G7
>I7:/F$ +H7-C7*D7

REPLICATE

/R: F7.F7: F8.F12: R R
/R: H7.H7: HB.H12: R R
/R: I7.I7: I8.112: R R R

>A8:Oatmeal
>CB:/F$ .01
>D8:550
>E8:375
>G8:/F$ .03
>A9:Oatmeal/R
>B9:aisin
>C9:/F$ .02
>D9:500
>E9:425
>G9:/F$ .05
>A10:Peanut Bu
>B10:tter
>C10:/F$ .01
>D10:600
>E10:125
>G10:/F$ .03
>A11:Pecan
>C11:/F$ .03
>D11:300
>E11:275
>G11:/F$ .05
>Al2:Macaroon
>C12:/F$ .05
>D12:300
>E12:300
>Gl2:/F$ .10
>B14:Tota1:
>D14:/F$ @SUM(D7.D12)

REPLICATE

/R: E14.I14: R R

>G14
/B:

I14:/F$

Save it to disk:

---

Inventory Control        ABC Cookie Company
Goods	Cost per Unit	Quant Made	Quant Sold	Quant Instock	Unit Price	Gross Income	Net Income
Chocolate Chip	0.02	900	850	50	0.05	42.50	24.50
Oatmeal	0.01	550	375	175	0.03	11.25	5.75
Oatmeal/Raisin	0.02	500	425	75	0.05	21.25	11.25
Peanut Butter	0.01	600	125	475	0.03	3.75	-2.25
Pecan	0.03	300	275	25	0.05	13.75	4.75
Macaroon	0.05	300	300	0	0.10	30.00	15.00
Total:		3150	2350	800		122.50	59.00

Figure 12.1 Inventory Control

---

        When you have completed entering these commands, you should have a spreadsheet that looks like the one in Figure 12.1. You may only be able to see four columns of your spreadsheet, but you'll discover the rest if you move your cursor around the screen.
        If you aren't the adventurous type, you can print your spreadsheet out on your Epson printer to compare it with the one in Figure 12.1. Set your Epson printer to the NLQ print mode.

        >A1 : /P P & H14:

        Now watch your spreadsheet appear in printed form.


COST ANALYSIS OF ADVERTISING COSTS AND RETURN

Another application you may find helpful is a spreadsheet template which will analyze your advertising costs and your return for the advertising dollar. You simply enter the Circulation, Size of the Ad, Insertion Cost, # of Insertions, and # of Responses. It will calculate your total cost, the cost of the ad in relation to the number of subscribers (cost/circulation ratio), and the cost of each response (cost/response ratio).
        You'll notice a time-saving trick in this spreadsheet. After the format is entered in C10, it is replicated to cells D10 to F10. This prepares those cells for monetary entries. Although this was only applied to a few cells in this situation, it can save quite a bit of work when used on larger spreadsheets.


>B1:" Cost Ana
>C1:lysis of
>B2:XYZ Adver
>C2:tising Ca
>D2:mpaign
>D4:Magazines
>C6:"   Boy's
>D6:"   Kid's
>E6:"   Youth
>F6:" Forever
>C7:"     Day
>D7:"   World
>E7:"    Week
>F7:"   Young
>A8:Circulati
>B8:on
>C8:6000
>D8:8200
>E8:2500
>F8:15000
>A9:Size of A
>B9:d(in.):
>C9:2
>D9:1
>E9:2
>F9:3
>A10:Insertion
>B10: Cost:
>C10: /F$

REPLICATE

/R: D10.F70 "

>C10:400
>D10:320
>E10:275
>F10:750
>A11:"# of Inse
>B11:"rtions
>C11:l
>D11:3
>E11:3
>F11:2
>A12""========= ;


REPLICATE

/R: B12.F12

>A13:Total Cos
>B13:t
>C13:/F$ +C10*C11

REPLICATE

/R: D13.F13: R R

>A14:Cost/Circ
>B14: Ratio:
>C14:/F$ +C10/C8

REPLICATE

/R: D14.F14: R R

>A16:"# of Resp
>B16:onses:
>C16:95
>D16:105
>E16:86
>F16:256
>A17:Cost/Resp
>B17:onse:
>C17:/F$ +C13/C16

REPLICATE

/R: D17.F17: R R


Save it to disk:

---

Cost Analysis of XYZ Advertising Campaign
Magazines
	Boy's Day	Kid's World	Youth Week	Forever Young
Circulation:	6000	8200	2500	15000
Size of Ad(in.)	2	1	2	3
Insertion Cost:	400.00	320.00	275.00	750.00
# of Insertions:	1	3	3	2
===================================================================
Total Cost:	400.00	960.00	825.00	1500.00
Cost/Cir Ratio:	0.07	0.04	0.11	0.05
# of Responses:	95	105	86	256
Cost/Response:	4.21	9.14	9.59	5.86

Figure 12.2 Advertising Cost Analysis

---

        Print your spreadsheet to see if it matches Figure 12.2:

        >A1: / P P & F17:


TIMECARD

One of the most tedious but necessary tasks in running a business is calculating wages from timecards. This template will total an employee's weekly hours, calculate the amount of overtime (if there is any), and then multiply these hours by the appropriate rates to reach the total wages due that employee for the week.
        The number of hours in a full work week is entered in cell B2. All hours worked up to and including this amount, are multiplied by the employee's rate of pay to arrive at a level of compensation. The template then calculates the number of overtime hours worked from Monday through Friday. Since these overtime hours are compensated at a wage of "time and a half," they are multiplied by a factor of 1½ times the employee's rate. The overtime accrued on the weekend is "double time." It is calculated differently at a rate of two times the employee's rate. The sum of all three of these totals is then placed in column Q for the Total Wages due the employee.
        You'll notice two new things in this spreadsheet application. First, cell K6 reads IFJ6>B2THENB2ELSEJ6. This is a conditional statement used to define the value of K6. It states that if the value in J6 is greater than that of B2 then make K6 equal to B2. The ELSE completes the statement by stating that if J6 is not greater than B2 then the value of K6 should be made equal to J6.
        This conditional statement is a simple way of determining the number of hours the employee has worked as regular time. It states that if the total number of hours worked Monday through Friday is at least equal to the total number of hours expected in cell B2 then the total for K6 will equal the number in B2. This eliminates the need for messy calculations.
        The other new spreadsheet tool is the replication procedure used after Q6. It is a process where all of the hours and/or formulas in row 6 are replicated to rows 7 through 15. As you do it, you'll appreciate the amount of time that can be saved using this procedure.

>A1:" Week of
>B1:" August
>C1:"3 - 9
>A2:"  Hours:
>B2:40
>B3:" Date:
>C3:3
>D3:4
>E3:5
>F3:6
>G3:7
>H3:8
>I3:9
>J3:"  Total
>K3:" Regular
>L3:" Regular
>M3:"Overtime
>N3:"  O.T.
>03:"Overtime
>P3:"  O.T.
>Q3:" Total
>A4:Employee
>B4:" Rate
>C4:"    Mon
>D4:"   Tues
>E4:"    Wed
>F4:"  Thurs
>G4:"    Fri
>H4:"    Sat
>14:"    Sun
>J4:" Hours
>K4:" Hours
>L4:" Wages
>M4:"(1 1/2X)
>N4:" Wages
O4:" (2 X)
>P4:" Wages
>Q4:' Wages
>A6:Anderson :

/ G R M    (Recalculate Manually)

>B6:/F$ 5.75
>C6:8
>D6:8
>E6:8
>F6:8
>G6:8
>H6:0
>I6:0
>J6:@SUM(C6.I6)
>K6:IFJ6>B2THENB2ELSEJ6
>L6:/F$ +K6*B6
>M6:@SUM(C6.G6)-K6
>N6:/F$ +M6*(1.5*B6)
>O6:+H6+I6
>P6:/F$ +06*(2*B6)
>Q6:/F$ +L6+N6+P6

REPLICATE

/R [backspace] C6.I6 : C7 to C15 replicates 8 hours
/R [backspace] J6.Q6 :  J7 to J15 <R> R R/R N N R/R R/R R R/R R/R
                                             R/R R/R R R

        You'll notice when all of the replication is through, your columns will contain identical numbers. That's because you turned off the automatic recalculation function. Use /G R A to turn it back on and watch everything change. Remember to use /G R M to turn it back off to allow you rapid number entry.


>A7:Buril
>B7:/F$ 6.95
>D7:9
>F7:9
>H7:9
>I7:8
>AB:Deland
>BB:/F$ 8.56
>C8:7
>G8:6
>A9:Foster
>B9:/F$ 7.25
>H9:4
>A10:Holland
>B10:/F$ 9.76
>Al1:Hug
>Bll:/F$ 9.01
>C11:9
>E11:10
>H11:8
>A12:Jones
>B12:/F$ 5.50
>C12:4
>D12:0
>F12:4
>G12:0
>A13:Kahn
>B13:/F$ 8.57
>A14:Klink
>B14:/F$ 11.54
>A15:Zeitz
>B15:/F$ 12.00
>C15:9
>E15:10
>F15:9
>H15:8
>I15:3

    /G R A (Automatically Recalculates the Equations)

Save it to Disk:

    / S S TIMECARD <R>


        This spreadsheet is quite wide (136 space wide) so you will have to print it in two parts. The first half will include columns A through I. This will display the Employee, Rate, and Hours for the week.
        Remember to set your printer to NLQ before printing.

        >Al: / P P & 115:

Now print the second half of the spreadsheet:

        >J1: / P P & Q15:

---

Week of August 3 - 7
Hours:        40
	Date:	3	4	5	6	7	8	9
Employee	Rate	Mon	Tues	Wed	Thurs	Fri	Sat	Sun
Anderson	5.75	8	8	8	8	8	0	0
Buril	6.95	8	9	8	9	8	9	8
DeLand	8.56	7	8	8	8	6	0	0
Foster	7.25	8	8	8	8	8	4	0
Holland	9.76	8	8	8	8	8	0	0
Hug	9.01	9	8	10	8	8	8	0
Jones	5.50	4	0	8	4	0	0	0
Kahn	8.57	8	8	8	8	8	0	0
Klink	11.54	8	8	8	8	8	0	0
Zeitz	12.00	9	8	10	9	8	8	3

---

---

Total Hours	Regular Hours	Regular Wages	Overtime (1 1/2X)	O.T.  Wages	Overtime    (2 X)	O.T. Wages	Total Wages
40	40	230.00	0	0.00	0	0.00	230.00
59	40	278.00	2	20.85	17	236.30	535.15
37	37	316.72	0	0.00	0	0.00	316.72
44	40	290.00	0	0.00	4	58.00	348.00
40	40	390.40	0	0.00	0	0.00	390.40
51	40	360.40	3	40.55	8	144.16	545.11
16	16	88.00	0	0.00	0	0.00	88.00
40	40	342.80	0	0.00	0	0.00	342.80
40	40	461.60	0	0.00	0	0.00	461.60
55	40	480.00	4	72.00	11	264.00	816.00

Figure 12.3 Timecard

---

        These three spreadsheet applications are just a sampling of the types of timesaving processes which can be created. You no doubt already have a few ideas in mind and there are a great number of spreadsheet application books on the market.
        Another source for spreadsheet applications is other spreadsheet users. Sometimes it's tough to meet others who have the same computer, software or problems as you. The next chapter discusses an emerging form of communication between computerists called "Telecomputing." It will allow you to ask an Atari owner in Bangor, Maine about a spreadsheet problem while you sit in your office in Seattle, Washington.
        Welcome to the Electronic Universe.

---

Return to Table of Contents | Previous Chapter | Next Chapter